I have a query which returns a single result.
@query='select name from studtable where id=1'
How should I write query so that result is saved in string and @result contains result.
@result=exec(@query)
-
1you got the answer?? I've noticed you don't accept any answers. Why so?? It's not a good practice..Deepshikha– Deepshikha2014-06-05 12:23:45 +00:00Commented Jun 5, 2014 at 12:23
Add a comment
|
2 Answers
To execute a string, we recommend that you use the sp_executesql stored procedure
instead of the EXECUTE statement. Because this stored procedure supports parameter
substitution, sp_executesql is more versatile than EXECUTE; and because
sp_executesql generates execution plans that are more likely to be reused by SQL
Server, sp_executesql is more efficient than EXECUTE.
Read more here:http://technet.microsoft.com/en-us/library/ms175170(v=sql.105).aspx
So you can write as"
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @IntVariable INT
DECLARE @name varchar(30)
SET @SQLString = N'SELECT @nameOUT = name
from studtable where id=@id'
SET @ParmDefinition = N'@id tinyint,
@nameOUT varchar(30) OUTPUT'
SET @IntVariable = 1
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@id = @IntVariable,
@nameOUT=@name OUTPUT
SELECT @name
Comments
You can do something like below to store the result using sp_executesql with output parameter. Observed from here Assign result of dynamic sql to variable
declare @ret int
set @ret = 0
set @query='select name from studtable where id=1'
exec sp_executesql @query, N'@var int out', @ret out
select @ret
