July 28, 2003 at 10:07 pm
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
July 29, 2003 at 4:09 am
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.
July 30, 2003 at 10:09 am
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.
August 4, 2003 at 2:10 pm
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
August 4, 2003 at 2:14 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply