sp_describe_undeclared_parameters is one of the featured system stored procedures introduced in SQL Server 2012. In this article we will discuss this stored procedure syntax, purpose & examples.
Syntax:
sp_describe_undeclared_parameters
[ @tsql = ] 'Transact-SQL_batch'
[ , [ @params = ] N'parameters' data type ] [, ...n]
Purpose :
This function gives us details about all undeclared parameters of any t-sql query. Lets say if I give you a dynamic sql and tell you to execute it. You will definitely ask me two main questions :
- How many parameters are required to execute this dynamic sql ?
- What are the data types of these parameters ?
Without having answers to above questions you cannot execute a dynamic t-sql query ? The beauty of sp_describe_undeclared_parameters stored procedure is, it will give you the answers to above questions and once you have the parameters & its data type, you can easily execute any dynamic sql.
Lets do it step by step.
Step 1 :
In the given below example, we have a t-sql but we don’t know how many parameters are required and its data type, nevertheless we will try to execute it.
Use AdventureWorks2012
GO
DECLARE @SQL NVARCHAR(500);
DECLARE @ParameterDefinition NVARCHAR(500);
DECLARE @BusinessEntityID INT;
SET @SQL =N'SELECT @BusinessID=[BusinessEntityID] from [HumanResources].[vEmployee]
Where [FirstName]=@FName';
EXECUTE sp_executesql @SQL
--OUTPUT
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable “@BusinessID
Oops ! error because we did not pass the parameters and we don’t know about it either.
Step 2 :
Lets pass the same t-sql to sp_describe_undeclared_parameters to get parameters details including segregation of input and output parameters.
sp_describe_undeclared_parameters @tSQL=N'SELECT @BusinessID=[BusinessEntityID]
from [HumanResources].[vEmployee] Where [FirstName]=@FName';
Step 3 :
Now, you have the parameters details including which one is input and output parameter. Lets pass it and view the results.
Example-1 :
use AdventureWorks2012
GO
DECLARE @SQL NVARCHAR(500);
DECLARE @ParameterDefinition NVARCHAR(500);
DECLARE @BusinessEntityID INT;
DECLARE @FirstName as NVARCHAR(50)
SET @FirstName='Ken'
SET @SQL =N'SELECT @BusinessID=[BusinessEntityID] from [HumanResources].[vEmployee]
Where [FirstName]=@FName';
SET @ParameterDefinition = N'@FName nvarchar(50), @BusinessID INT OUTPUT';
EXECUTE sp_executesql @SQL
,@ParameterDefinition,
@FName=@FirstName,
@BusinessID =@BusinessEntityID OUTPUT;
SELECT @BusinessEntityID as [BusinessEntityID]
GO
--OUTPUT
BusinessEntityID
———————
1
(1 row(s) affected)
Example-2 :
In this example we know only one parameter and the rest of the parameters we don’t. Simply we need to pass t-sql and the known parameter to sp_describe_undeclared_parameters, and it will give you the unknown / undeclared parameter information.
sp_describe_undeclared_parameters @tSQL=N'SELECT @BusinessID=[BusinessEntityID]
from [HumanResources].[vEmployee] Where [FirstName]=@FName'
,@params =N'@FName nvarchar(50)'
--OUTPUT
Example-3 :
Sometimes, we know all the parameters but to make sure none of the parameters is missing, simply we need to pass t-sql and the all parameters to sp_describe_undeclared_parameters, it will give you the missing parameters (if any), otherwise Nil result set.
sp_describe_undeclared_parameters @tSQL=
N'SELECT @BusinessID=[BusinessEntityID]
from [HumanResources].[vEmployee] Where [FirstName]=@FName'
,@params =N'@FName nvarchar(50), @BusinessID INT OUTPUT'
Will post some real world examples in the upcoming posts.
Read Full Post »
SQL Server Portal 