September 21, 2016 at 5:45 am
I have a query that delivers me a list of employeeIDs. For each EmployeeID I want to run a stored procedure and print its output. So the SP has an input and an output parameter. For looping I use a curser this is what I have done yet:
DECLARE @MyCursor as CURSOR
DECLARE @InputPar as int
DECLARE @OutputPar as varchar(30)
SET @MyCursor = CURSOR FOR <query>
Open @MyCursor;
FETCH NEXT FROM @MyCursor INTO @InputPar
BEGIN
WHILE @@FETCH_STATUS = 0
Exec dbo.mySP @InputPar, @OutputPar Output
Print @OutputPar
FETCH NEXT FROM @MyCursor INTO @InputPar
END
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
Running this code I get the Error "Must declare the scalar variable "@QutputPar". During my research I saw that this might be a scope problem since as soon as Exec triggers I lose the previous scope and so the variable is not available anymore (I am not sure if this is right, but this might be the problem). I checked different solutions but cannot apply them to my code. It would be nice, if anyone could help me get my code running.
Thx in advance!
September 21, 2016 at 5:50 am
neophilius (9/21/2016)
I have a query that delivers me a list of employeeIDs. For each EmployeeID I want to run a stored procedure and print its output. So the SP has an input and an output parameter. For looping I use a curser this is what I have done yet:
DECLARE @MyCursor as CURSOR
DECLARE @InputPar as int
DECLARE @OutputPar as varchar(30)
SET @MyCursor = CURSOR FOR <query>
Open @MyCursor;
FETCH NEXT FROM @MyCursor INTO @InputPar
BEGIN
WHILE @@FETCH_STATUS = 0
Exec dbo.mySP @InputPar, @OutputPar Output
Print @OutputPar
FETCH NEXT FROM @MyCursor INTO @InputPar
END
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
Running this code I get the Error "Must declare the scalar variable "@QutputPar". During my research I saw that this might be a scope problem since as soon as Exec triggers I lose the previous scope and so the variable is not available anymore (I am not sure if this is right, but this might be the problem). I checked different solutions but cannot apply them to my code. It would be nice, if anyone could help me get my code running.
Thx in advance!
Assuming your output parameter as defined in the sp is called @OutputPar,
Exec dbo.mySP @InputPar, @OutputPar = @OutputPar Output ?
Then there would be the follow on question as to whether a Cursor is actually the best mechanism for doing whatever it is you're doing ...
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 21, 2016 at 6:12 am
Thanks I do not get the error anymore.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply