Returning Result Set to Variable

Question

Returning Result Set to Variable

gotdablues

SSC Veteran

Points: 272
More actions
February 2, 2005 at 7:02 am

#163719

I need to return the result from a stored procedure to variable. Below is a simple procedure that returns a result set. I would like the result returned to @ResultOut.
Any thoughts?
-- Declare of stored procedure. It returns a result set.
Create Procedure spReturnResultSet
as
begin
select log_date, filename from log_history
end
-- execute the stored procedure. Would like to store the results in @ResultOut
Declare @ResultOut varchar(1000)
exec spReturnResultSet

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

I am not completely clear as to where you want these results. You are choosing two fields from a table as a recordset, and want one @Variable to hold them? You can declare @ResultOut as OUTPUT and that will allow you hold and pass the results. You may want to look up output on BOL to see your other options for passing recordsets.

I wasn't born stupid - I had to study.

gotdablues SSC Veteran Points: 272 More actions · Answer 2

Ultimately, I want to take the result from the stored procedure email the results to myself.

For instance, I want to take @ResultOut and include it in an email message body.

Declare @ResultOut varchar(1000)

exec spReturnResultSet

exec xp_smtp_sendmail @To = 'myemail@abc.com', @message = @ResultOut

HTH.

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

You may need a more simple remedy. Try using executesql. Here is a simple little script, (which loops; something you may not need) that uses executesql and may help you to see if that is what you want to use. Good luck. (Please read the following article before using this kind of SQL to make sure you are not opening yourself up to malicious hacking! http://www.sommarskog.se/dynamic_sql.html 'The Curse and Blessings of Dynamic SQL'

CREATE TABLE #Test( Field1 int, Field2 int, Field3 int, Field4 int, Field5 int,

Field6 int, Field7 int, Field8 int, Field9 int, Field10 int)

INSERT INTO #Test SELECT 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

DECLARE @Counter int,

@SQL nvarchar(2000),

@NumberOutPut int

SELECT @Counter = 1

WHILE @Counter <= 10

BEGIN

SET @sql = N'SELECT @OutPut = Field' + CONVERT( varchar, @Counter) + ' FROM #Test'

EXEC sp_executesql @sql, N'@OutPut int OUTPUT', @OutPut = @NumberOutPut OUTPUT

SELECT @NumberOutput AS '@NumberOutPut'

SELECT @Counter = @Counter + 1

END

DROP TABLE #Test

I wasn't born stupid - I had to study.