I am trying to make a simple function that reads a table from an ORACLE database and returns a sequence number. I would either like to return it directly or store the value inside of @cwpSeq and return that to the calling program.
Right now I am getting error:
RETURN statements in scalar valued functions must include an argument.
Can anyone assist me.
create function dbo.get_cwpSeq_from_oracle(@COIL nvarchar(100) )
returns int as
begin
DECLARE @cwpSeq int, @SQL nvarchar(1000);
set @SQL = N'select * from openquery(DEV, 'select cwp_seq from apps.custom_wip_pieces where lot_number = ''' + @COIL + '')';
return execute sp_executesql @SQL;
end;
As already mentioned, in this case you should use a procedure with an output parameter instead of a function. If you want to fully execute the query on the Oracle linked server side and return some value after that, I would suggest using dynamic as follows:
Create Or Alter Procedure dbo.get_cwpSeq
@COIL nvarchar(100),
@cwp_seq Int Output
As
Declare @QueryText nVarChar(max)
Select @QueryText = 'Select @cwp_seq=cwp_seq
From Openquery(DEV,
''Select cwp_seq
From apps.custom_wip_pieces
Where lot_number= ''''' + @COIL + ''''''') As Ora';
Execute sp_executesql @QueryText, N'@COIL nvarchar(100), @cwp_seq Int Output', @COIL = @COIL, @cwp_seq = @cwp_seq Output
As far as I understand in your case: Linked server is "DEV", Owner of the table is "apps".