It's declared outside of the dynamic SQL string. If you want the variable accessible inside the dynamic SQL, you either have to declare it within the dynamic SQL string, or use sp_executesql and declare it as a parameter
This will throw an error - variable not declared
DECLARE @sql varchar(max), @SomeVar int
SET @SomeVar = 0
SET @sql = 'SELECT @SomeVar'
EXEC (@sql) -- error @somevar not declared
This will work fine.
DECLARE @sql varchar(max)
SET @SQL = 'DECLARE @SomeVar int'
SET @SQL = @SQL + ' SET @SomeVar = 0 '
SET @SQL = @SQL + ' SELECT @SomeVar'
EXEC (@sql)
This will also work fine
DECLARE @sql nvarchar(max), @SomeVar int
SET @SomeVar = 0
SET @sql = 'SELECT @SomeOtherVar'
exec sp_executesql @sql, N'@SomeOtherVar int', @SomeOtherVar = @SomeVar
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability