Scope of variables in Dynamic SQL - SQL Server Vs MySQL‏

I have already posted about how Dynamic SQL works in SQL Server and MySQL at http://www.sqlservercurry.com/2012/08/dynamic-sql-sql-server-vs-mysql.html

There is a significant difference between SQL Server and MySQL as far as the scope of variables is concerned. The variable declared and accessed in Dynamic SQL can be accessed out of Dynamic SQL in MySQL, whereas this is not possible in SQL Server

Consider the following set of data

create table testing(id int, names varchar(100))
insert into testing(id,names)
select 1,'test1' union all
select 2,'test2' union all
select 3,'test3'

MySQL

The purpose is to assign a value to a variable in dynamic sql and access the same variable out of dynamic sql

set @sql:='set @count:=(select count(*) from testing);';
prepare stmt from @sql;
execute stmt ;
select @count;

In the above code, the variable @count is declared and assigned in dynamic sql. But after dynamic sql is executed using the prepare statement, the variable is still accessible. The statement select @count returns the value 3

SQL Server

Create the same table testing in SQL Server. Now execute the following code

declare @sql varchar(8000)
set @sql='
    declare @count int
    set @count=(select count(*) from testing)
    '
execute (@sql)
select @count

You will get the following error

Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@count".

The code before select @count will get executed correctly. But the variable declared in dynamic sql cannot be accessed out of it. If you access it in same dynamic scope like below, it will work

declare @sql varchar(8000)
set @sql='
    declare @count int
    set @count=(select count(*) from testing)
    select @count
'
execute (@sql)
 
So you need to be aware of this behavior when using Dynamic SQL.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan