exec(@sqlstr) How do I get the return value? 3

[shaunacg]

Hi,

I have the following string:

set @sqlstr = 'Select ordering from ' + @table + ' where [id] = ' + cast(@id as nvarchar(10))

that I want to execute and get the return value of, putting it into a variable. How can I do this? I execute the string like this exec(@sqlstr) but get not return value. And as far as I know I have to do it this way because a normal select statement won't work since I need to put two variable values into it.

Does anyone know how to do this? Thanks.

 

[dbomrrsm]

declare @sqlstr varchar (150),@table varchar (20),@id int
set @table = 'tablename'
set @id = 15
set @sqlstr = 'Select ordering from ' + @table + ' where [id] = ' + cast(@id as nvarchar(10))
EXEC @sqlstr

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[dbomrrsm]

or this might be what you want to do:

DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

/* Build the SQL string once.*/
SET @SQLString =
     N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint'
/* Execute the string with the first parameter value. */
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @level = @IntVariable
/* Execute the same string with the second parameter value. */
SET @IntVariable = 32
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @level = @IntVariable

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[JamesLean]

DECLARE @id int,
	@table sysname,
	@sql nvarchar(200),
	@params nvarchar(200),
	@ordering varchar(20)

--input variables
SET @id = 123
SET @table = 'mytable'

--set up SQL statement
SET @sql = 'SELECT @out = ordering FROM ' + @table + ' WHERE id = @p1'

--set up parameter list to pass to SQL statement
SET @params = '@p1 int, @out varchar(20) OUTPUT'

--execute SQL, passing in parameter for id value and specifying the output parameter for the result
EXEC sp_executesql @sql, @params, @id, @ordering OUT

--this is the result!
PRINT @ordering

--James

 

[sunaj]

Hi James,

Thanks for your post, exactly what I was looking for - a star for you.
Now, why has MS not written that in the help for sp_execute!

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'