set a variable with a return value of a query

Question

Hi I have something like this in my store procedure,

SET @SQl = 'Some query' --this query return only a cell the type int
SET @VAR = exec(@SQL) --the varaible @var it's local and the type int

How can i get the return value of the query and set to the variable, it's possible?? or what I'm doing it's wrong???

Thanks for your help

Answer 1

If the query returns a scalar result set you need to do

DECLARE @VAR INT

DECLARE @Result TABLE
(
C INT
)

DECLARE @SQl NVARCHAR(MAX)
SET @SQl = 'SELECT 1'

INSERT INTO @Result
EXEC(@SQl)

SELECT @VAR = C FROM @Result

Much better to use sp_executesql and OUTPUT parameters

DECLARE @VAR INT

DECLARE @SQl NVARCHAR(MAX)
SET @SQl = 'SELECT @out = 1'

EXEC sp_executesql @SQl, N'@out int output', @out = @VAR OUTPUT

SELECT @VAR