1

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;
Dale K
  • 25,246
  • 15
  • 42
  • 71
esjones
  • 13
  • 3
  • selct * never can return an integer, so please explain what exactly you want to returm – nbk Nov 08 '21 at 21:25
  • 3
    You can't use dynamicSql in a function, you should consider using a procedure with an output parameter. – Stu Nov 08 '21 at 21:26
  • Hope this can help you: https://stackoverflow.com/questions/48993147/return-statements-in-scalar-valued-functions-must-include-an-argument/48993148 or maybe this: https://www.exacthelp.com/2012/03/return-statements-in-scalar-valued.html – Mohamad TAGHLOBI Nov 08 '21 at 21:31

1 Answers1

0

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".

Anton Grig
  • 1,640
  • 7
  • 11
  • Anton thank you for the reply. I just have a quick clarifying question. So "DEV" is our generic name for our ORACLE DB that we want to connect to. For example if I run this command in sql developer I get returned 'A' select * from openquery(DEV, 'select ''A'' from dual '); We use a schema called WSCTS so would the call still be "...DEV.WSCTS.dbo.sp_executesql..." – esjones Nov 09 '21 at 21:55
  • @esjones I'm sorry, it's my fault. I posted an edited version of the procedure for Oracle Linked Server. – Anton Grig Nov 10 '21 at 07:51
  • that did the trick! Thank you!! – esjones Nov 11 '21 at 21:05
  • @esjones You are welcome! – Anton Grig Nov 11 '21 at 22:11