Trigger Stored Procedures with OUT parametrs

%3CLINGO-SUB%20id%3D%22lingo-sub-1763440%22%20slang%3D%22en-US%22%3ETrigger%20Stored%20Procedures%20with%20OUT%20parametrs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1763440%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20trigger%20a%20stored%20procedure%20with%20OUT%20parameters%20through%20'lookup'%20activity.%20Getting%20the%20below%20error%2C%20Can%20some%20one%20Suggest%20how%20to%20trigger%20Stored%20Procedures%20with%20'Lookup'%20activity.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22kishoremakke_0-1602240760166.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F225374i36E10A67B06ADDF6%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22kishoremakke_0-1602240760166.png%22%20alt%3D%22kishoremakke_0-1602240760166.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EBelow%20is%20my%20settings%20in%20'Lookup'%20activity.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22kishoremakke_1-1602240981671.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F225377i1878D0C082302CE8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22kishoremakke_1-1602240981671.png%22%20alt%3D%22kishoremakke_1-1602240981671.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20Advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1773273%22%20slang%3D%22en-US%22%3ERe%3A%20Trigger%20Stored%20Procedures%20with%20OUT%20parametrs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1773273%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F701708%22%20target%3D%22_blank%22%3E%40kishoremakke%3C%2FA%3E%26nbsp%3BAs%20best%20I%20can%20tell%2C%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-factory%2Fcontrol-flow-lookup-activity%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ELookup%20Activity%3C%2FA%3E%20only%20returns%20%3CEM%3E%3CSTRONG%3Erecords%3C%2FSTRONG%3E%20%3C%2FEM%3Efrom%20a%20stored%20procedure.%26nbsp%3B%20Do%20you%20have%20the%20option%20to%20change%20the%20way%20the%20stored%20procedure%20returns%20the%20data%20currently%20defined%20by%20your%20OUT%20parameters%20as%20the%20result%20of%20a%20custom%20SELECT%20statement%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDECLARE%20%40myVariable%20NVARCHAR(50)%3CBR%20%2F%3ESET%20%40myVariable%20%3D%20'Some%20value'%3C%2FP%3E%3CP%3ESELECT%20%40myVariable%20as%20%5BMyVariable%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20are%20stuck%20with%20the%20way%20the%20current%20stored%20procedures%20are%20designed%2C%20you%20may%20try%20creating%20a%20wrapper%20stored%20procedure%20that%20converts%20the%20OUT%20parameters%20to%20a%20recordset.%26nbsp%3B%20Alternatively%2C%20you%20could%20create%20an%20Azure%20Function%20that%20could%20execute%20the%20stored%20procedure%20and%20return%20the%20necessary%20results.%26nbsp%3B%20Not%20ideal%2C%20but%20there%20may%20be%20a%20few%20options.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

 

Hi,

 

I am trying to trigger a stored procedure with OUT parameters through 'lookup' activity. Getting the below error, Can some one Suggest how to trigger Stored Procedures with 'Lookup' activity.

kishoremakke_0-1602240760166.png

Below is my settings in 'Lookup' activity.

kishoremakke_1-1602240981671.png

 

Thanks in Advance.

 

 

1 Reply

@kishoremakke As best I can tell, the Lookup Activity only returns records from a stored procedure.  Do you have the option to change the way the stored procedure returns the data currently defined by your OUT parameters as the result of a custom SELECT statement?  

 

DECLARE @myVariable NVARCHAR(50)
SET @myVariable = 'Some value'

SELECT @myVariable as [MyVariable]

 

If you are stuck with the way the current stored procedures are designed, you may try creating a wrapper stored procedure that converts the OUT parameters to a recordset.  Alternatively, you could create an Azure Function that could execute the stored procedure and return the necessary results.  Not ideal, but there may be a few options.