SQLTeam.com | Weblogs | Forums

Storing the results from a stored procedure into a variable


I know I have seen how to do this before but I cannot find it now. I think I had to create a variable or type object. So if run a stored proc in a SQL task it then would move the results to a variable. I would then need to know how I would be able to view these results from the variable.
Thank you


Here is an example of how to do this:

-- a test stored proc
CREATE PROC dbo.TestProc1
	@someVariable FLOAT OUTPUT
	SET @someVariable = 24.3;

-- getting the value from the stored proc into a local variable.
EXEC dbo.TestProc1 @myVar OUTPUT;
SELECT @myVar;


Hi I am sorry I forgot to put it in here but I am referring how to do it in SSIS.
I mean I can run the proc in a SQL Task and then I am looking too send the result to the SSIS variable.
then I looking for away to loop through the results and display the results.


To display output in SSIS (during debug), you can either set a watch on the variable or write a script task to MessageBox.Show the contents of the variable. Once deployed to a server, there is no way to display the results other than sending them to a file or raising an informational message or warning.