Tuesday, October 11, 2005
Deferred named resolution - I
Deferred named resolution
Yesterday I have noticed an interesting behavior of SQL Server 2000 when writing a procedure. I came across a situation to define a variable data type based on a condition. Say, if the condition meets true then the variable needs to initiate as char otherwise as varchar. I have written the scriptlet similar to the following.
create proc test1 @type char(1)
as
if @type = 'c'
declare @var1 as char(10)
else
declare @var1 as varchar(10)
When I execute (even parse the query by pressing Ctrl+F5) the query, it thrown an error
Msg 134, Level 15, State 1, Procedure test1, Line 6
The variable name '@var1' has already been declared. Variable names must be unique within a query batch or stored procedure
If we look at the query it is practically not possible to satisfy both the status (true and false), hence the error thrown is unexpected. After referring Khen’s excellent book “Guru’s guide to SQL Server stored procedures, HTML and XML”, I found that this strange behavior is cause of SQL Server 2000’s name resolution. SQL Server won’t resolve the object names when we create procedures. The object name get resolve at run-time not at design time. But this will not apply for variables and temporary tables. So, we can not declare a variable more than one place in a single stored procedure even you declare them mutually exclusive or logically independent blocks.
Yesterday I have noticed an interesting behavior of SQL Server 2000 when writing a procedure. I came across a situation to define a variable data type based on a condition. Say, if the condition meets true then the variable needs to initiate as char otherwise as varchar. I have written the scriptlet similar to the following.
create proc test1 @type char(1)
as
if @type = 'c'
declare @var1 as char(10)
else
declare @var1 as varchar(10)
When I execute (even parse the query by pressing Ctrl+F5) the query, it thrown an error
Msg 134, Level 15, State 1, Procedure test1, Line 6
The variable name '@var1' has already been declared. Variable names must be unique within a query batch or stored procedure
If we look at the query it is practically not possible to satisfy both the status (true and false), hence the error thrown is unexpected. After referring Khen’s excellent book “Guru’s guide to SQL Server stored procedures, HTML and XML”, I found that this strange behavior is cause of SQL Server 2000’s name resolution. SQL Server won’t resolve the object names when we create procedures. The object name get resolve at run-time not at design time. But this will not apply for variables and temporary tables. So, we can not declare a variable more than one place in a single stored procedure even you declare them mutually exclusive or logically independent blocks.
# posted by Satheesh Chandra @ 6:17 PM 
