Assigning results of stored procedure to a variable

Question

Assigning results of stored procedure to a variable

Stuart Davies

SSCoach

Points: 19505
More actions
September 22, 2009 at 4:16 am

#214680

I have a stored procedure (usp_GetEmailsOnCall) that selects as varchar(250) a set of email address in a line (i.e. in the format fred.smith@domain.com;joe.bloggs@domain.com) for where the oncall column = 1.
In the under lying table they are in the format
Email OnCall
fred.smith@domain.com 1
joe.bloggs@domain.com 1
mary.jones@domain.com 0
I now want to assign this to a variable. In the past I’ve used
Exec @Var = usp_GetEmailsOnCall
To get this type of info but in this instance all I’m getting is zero.
After a couple of hours googling this I’m still none the wiser / nearer my answer.
Does anyone have the answer please
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

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

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

Stuart Davies SSCoach Points: 19505 More actions · Answer 1

I have now got a work around by putting the results of the sp into a temp table then, as the data is always only one row, assigning the variable to being the select top 1 from the table.

This seems a bit clumsy, so if there is a more elegant solution I would still like to hear.

Many thanks

Stuart

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 2

Hi Stuart,

Try this:

CREATE PROCEDURE usp_GetEmailsOnCall

@EmailsOnCall varchar(max) OUTPUT

AS

BEGIN

SET @EmailsOnCall = 'fred.smith@domain.com;joe.bloggs@domain.com' -- Replace this with actual code to get email adresses from database table

END

GO

DECLARE @EmailsOnCall varchar (max)

EXEC usp_GetEmailsOnCall @EmailsOnCall OUTPUT

SELECT @EmailsOnCall

Note that both the declaration of the parameter in the procedure as well as the actual parameter passed when calling the SP must be flagged as OUTPUT.

Regards,

Jan

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Stuart Davies SSCoach Points: 19505 More actions · Answer 3

Thanks for that Jan, I'll give that a go

I can now see where in one of my attempts in doing it I went wrong. I didn't pass a parameter to the stored proc as output and I was expecting to jyst output the data - it makes sense now.

You live and learn (hopefully)

Stuart

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx