• 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass