SQL Server Variable Scope in a Stored Procedure

SQL Server variable scope in a stored procedure

From books online:

The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.

However. Nothing keeps you from doing this:

create procedure Foo as begin

declare @bob int

if exists (x)
begin
    set @bob = 1
end
else
begin
    set @bob = 2
end

end

Variables scope which are defined within a while block in stored procedures - SQl Server

The variable scope is the whole batch in this case a stored procedure.

It isn't re-declared every loop

So this is exactly as expected

Edit:

There is a recent blog article which is quite similar. The author was quickly corrected :-)

T-SQL: Variable Scope

You dont need a dynamic query to achieve what you want, below query will give the same result as yours.

  declare @x varchar(max)
  declare @tableName varchar(100), @ColumnName varchar(50)

  set @tableName = 'Employee'
  set @ColumnName = 'ID'

  select @x = DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
  where
     Table_Name = @tableName
     and column_name = @ColumnName

  select @x

Output scoped variable values within execution of batch/stored procedure TSQL

You are using local variable, which is not possible to acess it out of it's scope. As TechNet also mentioned it:

The scope of a variable is the range of Transact-SQL statements that
can reference the variable. The scope of a variable lasts from the
point it is declared until the end of the batch or stored procedure in
which it is declared. Variables have local scope and are only visible
within the batch or procedure where they are defined.

But you can use SQLCMD, to declare variable, which is accessible out of it's defined batch too(limited to the session!)

Here is the Code:

--deifne a variable using SQLCMD mode
:setvar myvar 10 
PRINT $(myvar)--accessible

GO

PRINT $(myvar)--Also accessible

In order to activate the SQLCMD mode, you need to select SQLCMD mode from Query menu.

Set variable with Scope Identity after executing different stored proc

scope_identity() does what it says on the tin - it gives you the last identity value generated in the current scope. A stored procedure defines a scope. So when the stored procedure that causes the identity value to be generated exits, you're no longer in the scope where the value was generated, so scope_identity() can't tell you anything.

What you can do is capture the scope_identity() value into a variable inside the stored procedure, and return it as an output parameter:

create table t(i int identity(1,1), j int);
go
create proc insert_and_get_scope @scopeid int = null output as
begin
   insert t(j) values (1);
   set @scopeid = scope_identity();
end
go

declare @scopeid int;
exec insert_and_get_scope @scopeid output;
select @scopeid;

Scope of table variable within SQL cursor

Yes, it does - the scope isn't defined by the begin / end statements, but by the end of a stored procedure, or a go

The scope of a variable is the range of Transact-SQL statements that
can reference the variable. The scope of a variable lasts from the
point it is declared until the end of the batch or stored procedure in
which it is declared.

http://msdn.microsoft.com/en-us/library/ms187953(v=sql.105).aspx

Table Variable scoped to a single request, in SQL Server

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-go

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

IOW, including GO in your statement will at best be pointless, and at worst will throw an error. In the code you provided, your table variable's scope will terminate when your request is completed.

In this case, the scope of the table variable is the batch that you're submitting to SQL Server within the raw() function and everything within it will go out of scope when the query(ies) complete. From http://technet.microsoft.com/en-us/library/ms187953(v=sql.105).aspx

The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.

---

Related Topics