August 1, 2003 at 3:52 pm
I posted this in the general forum, but didn't really get an answer, so I will try again:
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
August 2, 2003 at 1:55 am
Tom,
I'm not absolutely sure of what you want, but try this....
declare @var sql_variant
exec sp_executesql N'SET @var = (SELECT StaffID FROM Staff WHERE (lastname = ''Smith''))', N'@var SQL_Variant OUTPUT', @Var OUTPUT
select @var
Cheers,
- Mark
Cheers,
- Mark
August 2, 2003 at 1:19 pm
Thanks very much for your reply.
What I'm trying to do is pass a sql string from an app to a stored procedure, and get a return value. I'm doing this now with a cursor.
The applicaton sends, e.g. '(SELECT StaffID FROM Staff WHERE (lastname = 'Smith') to the sp, and is represented by the variable.
Thanks to your help, and an MS KB article, I was able to get this to work. Apparently returning a value from sp_executesql is not documented in BOL.
Here's the test sp that finally worked:
CREATE PROCEDURE [dbo].[spTESTGetValBySql]
--gets a single value from the passed in sql string
@strsql nvarchar(1000),
@rtnVal sql_variant = NULL OUTPUT
AS
SET NOCOUNT ON
--pass in sql string to get the value
DECLARE @testsql nvarchar(1000)
DECLARE @val sql_variant
DECLARE @param nvarchar(50)
SET @param = '@val sql_variant OUTPUT'
SET @testsql = 'SELECT @val = ' + @strsql
exec sp_executesql @testsql, @param, @val OUTPUT
SELECT @val
SELECT @rtnVal = @val
GO
Thanks again for your help, this has been bugging me for quite some time....
August 2, 2003 at 4:36 pm
The other, messier way is to write the value out somewhere, either to a global temp table or a permanent table, then retrieve it from the calling session.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply