Returning value from stored procedure

Question

Returning value from stored procedure

TomT

SSC Eights!

Points: 887
More actions
July 28, 2003 at 10:07 pm

#82037

I assume there must be a way to do this, and I am just missing it:
Right now, I get values from sql strings passed to a stored procedure by using a cursor to write the value to a variable. I'd like to avoid using a cursor, but can't seem to find a way around it.
E.g. this works with a set sql string:
declare @var sql_variant
select @var = (SELECT StaffID FROM Staff WHERE (lastname = 'Smith'))
I get a value for @var
but if I want to pass in a variable containing the sql string e.g.
@strsql varchar(500),
@val sql_variant = NULL OUTPUT
--what would go here? how do I pass in the @sqlstr variable?
select @var =
This quite likely is a dumb question, but I've spent too much time already trying to make this work....
thanks for your input

Viewing 5 posts - 1 through 5 (of 5 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

If you are saying you want to pass the string dynamically and return the value to @var then look at sp_executesql in BOL.

But be carefull here as that increases you chance for injection attacks if you don't have other ways to control the data being submitted.

TomT SSC Eights! Points: 887 More actions · Answer 2

Thanks for your response.

I have already read the section you refer to, however I am still missing the part about assigning the result of a select statement to a variable.

E.g. SELECT @var = exec sp_executesql @strsql

where @strsql holds a valid sql string, does not work

Would you be kind enough to provide an example?

The string passed is always controlled by the front end, btw.

bobsterboy SSCommitted Points: 1569 More actions · Answer 3

DECLARE @nvch500SQLStr as nvarchar(500),

@nvch500ParmStr as nvarchar(500),

@nvch20SegValLocal as nvarchar(20)

--This is the select Statement and allows you to use Dynamic SQL AND also return a value from the statement

SET @nvch500SQLStr = N'SELECT @nvch20SegValOUT = CategoryName FROM Northwind.dbo.categories WHERE CategoryID = 1'

--This is where you declare your variables so you can pass data OUT from the Dynamic SQL Script

SET @nvch500ParmStr = N'@nvch20SegValOut as nvarchar(20) OUTPUT'

-- To run the SQLStr, you pass the SQLString and then the parameters and then map the SQLString

-- parameters to your local parameters - you will want to tag your local parameters with Local

-- to make it less confusing, but you could use any name for the mapping, just make sure the

-- data types are the same.

EXECUTE sp_executesql@nvch500SQLStr,

@nvch500ParmStr,

@nvch20SegValout = @nvch20SegValLocal OUTPUT

print @nvch20segvalLocal

TomT SSC Eights! Points: 887 More actions · Answer 4

Thanks very much, this approach does the trick. As far as I can see, that aspect of returning output to a variable from sp_executesql is not documented in BOL...

I found the KB article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;262499

which goes into this as well.

Thanks again for your help.