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;
Comments