Issues with building SQL strings

Question

Why won't this piece of code work for me? It gives the error Must declare the scalar variable "@y".

DECLARE @y int
DECLARE @dbname VARCHAR(50)

SET @y = -1
SET @dbname = 'SomeDb.dbo.'

SET @sql = 'SELECT @y=1 from ' + @dbname + 'Respondent'

exec(@sql)

Answer 1

The exec function creates a new execution scope. Your @y variable doesn't exist there.

Use sp_executesql instead. Note that you'll still have to do some extra work to pass your variable across the function boundary.

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(Title) 
   FROM AdventureWorks.HumanResources.Employee
   WHERE ManagerID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;