Forum Discussion

HSBHSB's avatar
HSBHSB
Copper Contributor
Aug 06, 2024

ADF Lookup Stored Procedure Error

I have a stored procedure that has the following format:

 

If (@x = 1)

BEGIN

    RETURN SCOPE_IDENTITY()

END
ELSE
BEGIN

   RETURN COLUMN1

END

 

I'm using a Lookup in ADF and it gives me an error: The specified Stored Procedure is not valid. It could be caused by that the stored procedure doesn't return any data.

 

How can I get the value from this stored procedure in ADF and save it to a variable without changing the stored procedure to say SELECT ID = @Scope_Identity() and keeping the RETURN xxxxx?

azure data factory

1 Reply

  • HSBHSB's avatar
    HSBHSB
    Copper Contributor
    Aug 07, 2024
    I created a new procedure that has an OUTPUT parameter.

    SET @ReturnID = SCOPE_IDENTITY()

    I'm still getting the same error on the lookup.
    The specified Stored Procedure is not valid. It could be caused by that the stored procedure doesn't return any data.

Resources