CODEWITHSUNDEEP
sqlsql-serverdatabase
sourabhgkr
sourabhgkr

Reputation: 89

sql server save result of exec(@query) in string

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)

Upvotes: 2

Views: 7346

Answers (2)

Deepshikha
Deepshikha

Reputation: 10274

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

Upvotes: 3

Rahul
Rahul

Reputation: 77896

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

Upvotes: 1

Related Questions