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?

​I have a stored procedure that has the following format: If (@x = 1)BEGIN    RETURN SCOPE_IDENTITY()ENDELSEBEGIN   RETURN COLUMN1END 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?  Read More

​