CODEWITHSUNDEEP
sqlsql-server-2008
Sam
Sam

Reputation: 3177

Assign variable the result of a select statement

How would you assign a variable the result of a SELECT in Sql Server?

Here is my attempt:

CREATE PROCEDURE x
AS 
BEGIN

DECLARE @MyVar integer
DECLARE @sql nvarchar(150), @v nvarchar(50)
Set @v = 'mytable' 
--SET @sql = 'SELECT INTO ' + @MyVar + 'count(*) FROM ' + @v 
SET @sql = 'Set ' + @MyVar + '= SELECT count(*) FROM ' + @v 
EXEC sp_executesql @sql
print @MyVar
END

Conversion failed when converting the varchar value 'Set ' to data type int.

Any recommendation on learning the syntax would be useful too. Thank you!

Upvotes: 0

Views: 625

Answers (1)

Martin Smith
Martin Smith

Reputation: 453807

You can use an OUTPUT parameter to sp_executesql as below.

DECLARE @MyVar INTEGER
DECLARE @sql NVARCHAR(MAX),
        @v   SYSNAME

SET @v = N'mytable'
SET @sql = N'SELECT @MyVar = count(*) FROM ' + QUOTENAME(@v)

EXEC sp_executesql
  @sql,
  N'@MyVar INT OUTPUT',
  @MyVar = @MyVar OUTPUT

PRINT @MyVar

RE: "Recommendation on learning the syntax" I don't really have one other than books online but you should read The Curse and Blessings of Dynamic SQL to see why QUOTENAME is important.

Upvotes: 3

Related Questions