Saturday, April 18, 2009

Able to use sp_executesql without declaring variables!

From Denis blog I came to know recently that we are able to use sp_executesql without declaring the variables!

It really sounded strange to me. I thought it to be one another bug :)

So thought would dig deep into this and see what best I can conclude.

--Dummy table schema
Create table tblQuestion1
(
sno int identity,
fname varchar(50)
)
Go

--Lets add some dummy records into the table
Insert into tblQuestion1 values ('Alpha')
Insert into tblQuestion1 values ('Beta')
Insert into tblQuestion1 values ('Gamma')

--So-called strange script
Declare @strFetchData nvarchar(100)
Select @strFetchData = 'Select * from tblQuestion1'
Exec sp_executesql @alpha = @strFetchData

If you have noticed we haven't declared @alpha in the script but SQL hasn't complained about it!

I thought the easiest way to understand would be to go through sp_executesql procedure.

Sp_helptext sp_executesql
go

Result - "(server internal)" … so its not a stored proc.

I tried adding another dummy parameter!

Declare @strFetchData nvarchar(100)
Select @strFetchData = 'Select * from sys.sysobjects'
Exec sp_executesql @alpha = @strFetchData, @beta = N''

Just the @ alone is also taken without a variable name:

Declare @strFetchData nvarchar(100)
Select @strFetchData = 'Select * from sys.sysobjects'
Exec sp_executesql @ = @strFetchData, @beta = N''

The code still works.

I tried adding the third parameter! hurray it fails.

Declare @strFetchData nvarchar(100)
Select @strFetchData = 'Select * from sys.sysobjects'
Exec sp_executesql @alpha = @strFetchData, @beta = N'', @gamma = N''

So I guess during compilation SQL engine is ignoring undeclared variables for the first 2 parameters but not from the third parameter onwards.

That said, syntax of sp_executesql on a higher level is:

1. First parameter - SQL Stmt
2. Second parameter - Parameter Definition
3. Third parameter and going fwd -- Value for the parameters are assigned here. (Parameter mapping)

So once parameter mapping comes into play it fails. The first two parameters is being taken as a dummy placeholders!!!

Few more examples:

--This works
Declare @IntVariable int;
Declare @SQLString nvarchar(500);
Declare @ParmDefinition nvarchar(500);

Set @SQLString =
N'Select EmployeeID, NationalIDNumber, Title, ManagerID
From AdventureWorks.HumanResources.Employee
Where ManagerID = @ManagerID';
Set @ParmDefinition = N'@ManagerID tinyint';

Set @IntVariable = 197;
Exec sp_executesql @SQLString, @ParmDefinition,
@ManagerID = @IntVariable;

--This would fail
Declare @IntVariable int;
Declare @SQLString nvarchar(500);
Declare @ParmDefinition nvarchar(500);

Set @SQLString =
N'Select EmployeeID, NationalIDNumber, Title, ManagerID
From AdventureWorks.HumanResources.Employee
Where ManagerID = @ManagerID';
Set @ParmDefinition = N'@ManagerID tinyint';

Set @IntVariable = 197;
Exec sp_executesql @alpha = @SQLString, @ParmDefinition,
@ManagerID = @IntVariable;

No comments: