I am attempting to run a stored procedure with a single output parameter and then map it to a variable. Seems simple in theory, but I keep getting the error
"There is an invalid number of result bindings returned for the ResultSetType"
After looking at all the answers i can find, none seem to answer my problem.
Create Procedure ReturnsessionVariables(@sessionid INT,@groupshare varchar(2000),@returnvalue nvarchar(max) output)
DECLARE @guid NVARCHAR(8)
SELECT @guid = guid from THANDLERSESSION where sessionid = @sessionid
select @returnvalue = substring((SELECT ','+keyname+'='+case when CHARINDEX(@guid,keyvalue) != 0 and keyvalue != @guid then @groupshare + right(keyvalue,len(keyvalue)-CHARINDEX(@guid,keyvalue)) else keyvalue end
from THANDLERPARAMS HP
where sessionid = @sessionid
FOR XML PATH ('')),2,2500)
Basically i am reading all the parameters from a table and creating a concatenated string that can then be passed to the next step in my package.
Output Example :-
this proc works fine and returns the data when I run it in SQL.
In SSIS i am attempting to run the proc and return the value to a variable.
As far as i can see I am doing everything ok, but i cannot work out what I must be doing wrong. This is a single return value proc so no issue with more than 1 row, object assignment etc. So what i am doing wrong?