Archive for the ‘System Stored Procedures’ Category

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.


    [ @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
DECLARE @ParameterDefinition NVARCHAR(500);
DECLARE @BusinessEntityID INT;

SET @SQL =N'SELECT @BusinessID=[BusinessEntityID] from [HumanResources].[vEmployee]
Where [FirstName]=@FName';

EXECUTE sp_executesql @SQL

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
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
@BusinessID =@BusinessEntityID OUTPUT;

SELECT @BusinessEntityID as [BusinessEntityID]

(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)'


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 »

sp_sequence_get_range System stored procedure is shipped with SQL SERVER 2012 and it is one of the handy system stored procedures when it comes to fetch range of IDs from SQL Server.
Note : Sequence is a prerequisite for this article.

Syntax :

sp_sequence_get_range [ @sequence_name = ] N''
     , [ @range_size = ] range_size
     , [ @range_first_value = ] range_first_value OUTPUT
    [, [ @range_last_value = ] range_last_value OUTPUT ]
    [, [ @range_cycle_count = ] range_cycle_count OUTPUT ]
    [, [ @sequence_increment = ] sequence_increment OUTPUT ]
    [, [ @sequence_min_value = ] sequence_min_value OUTPUT ]
    [, [ @sequence_max_value = ] sequence_max_value OUTPUT ]
    [ ; ]

Explanation :
Lets proceed step by step :

Step 1:

Lets create a sequence.

CREATE SEQUENCE [dbo].[Invoice]
 AS [int]

Step 2:

Lets pick up a new ID from this sequence.

SELECT NEXT VALUE FOR dbo.[Invoice] as [New ID]

New ID

(1 row(s) affected)

Step 3:

Lets fetch the new IDs via sp_sequence_get_range and see the difference.


DECLARE @range_first_value sql_variant
, @range_first_value_output sql_variant
, @range_last_value_output sql_variant

EXEC sp_sequence_get_range
@sequence_name = N'dbo.Invoice'
, @range_size = 4
, @range_first_value = @range_first_value_output OUTPUT
, @range_last_value = @range_last_value_output OUTPUT

SELECT @range_first_value_output AS [First ID]
,@range_last_value_output as [Last ID]

First ID       Last ID
2                5

(1 row(s) affected)

In the above example, you can see that this stored procedure returns a range from 2-5 IDs (First Value – Last Value),  so can use all four IDs (2, 3,4, 5) in your application.

If you execute the same script, 2nd time it will give you the range from 6-9 IDs and it will carry on till it reaches its maximum ID.

sp_sequence_get_range is not limited to return First & Last ID only. It will return other info related to sequence as well.

Step 4:

In Step 2 & Step 3, you can see both techniques are used to get new ID (s), but the difference between both of them is the requirement.

Whenever you need only one new ID you need to use NEXT VALUE & and if you need to get a range of new IDs from sequence you need to use sp_sequence_get_range.

Step 5:
Lets create another example to return all possible result sets from sp_sequence_get_range.


DECLARE @First_Value sql_variant
, @Last_Value sql_variant
, @Cycle_Count int
, @Sequence_Increament sql_variant
, @Sequence_Min_Value sql_variant
, @Sequence_Max_Value sql_variant ;

EXEC sys.sp_sequence_get_range
@sequence_name = N'dbo.Invoice'
, @range_size = 5
, @range_first_value = @First_value OUTPUT
, @range_last_value = @Last_Value OUTPUT
, @range_cycle_count = @Cycle_Count OUTPUT
, @sequence_increment = @Sequence_Increament OUTPUT
, @sequence_min_value = @Sequence_Min_Value OUTPUT
, @sequence_max_value = @Sequence_Max_Value OUTPUT ;

  @First_value AS FirstVal
, @Last_Value AS LastVal
, @Cycle_Count AS CycleCount
, @Sequence_Increament AS SeqIncrement
, @Sequence_Min_Value AS MinSeq
, @Sequence_Max_Value AS MaxSeq ;

Real world example :

I recently developed a billing system for one of my clients and the major requirement was to open 4 different invoices before saving the previous invoices.

And the problem was that I could not get a new ID without saving the previous invoice. So, I used this technique and it was successful.

Reference : MSDN

Read Full Post »

In SQL Server 2012 , Microsoft has provided a very helpful new system stored procedure namely “sp_describe_first_result_set”. Like name, like Work.. As the name goes, so is the performance. It gives the detailed meta data(Schema) information for the first possible result set. Note that it works only in SQL Server 2012. If you need to get the meta data info in SQL Server 2005/2008, you need to use “SP_help” and with very limited info availability.

Syntax :

sp_describe_first_result_set [ @tsql = ] N'Transact-SQL_batch'
    [ , [ @params = ] N'parameters' ]
    [ , [ @browse_information_mode = ]  ] ]

Lets make some simple examples to check its functionality  :

EXEC sp_describe_first_result_set @tsql =N'SELECT * FROM HumanResources.Department'

As I discussed, you can view the  output as a detailed meta data.

Now, let me explain why “first possible result set,” name is given to this procedure, with the help of a simple example.
Given below are two queries and it will return two sets of result as well.

SELECT * FROM [HumanResources].[Department];
SELECT * FROM [HumanResources].[Employee];

But, if we pass both the queries to “sp_describe_first_result_set” to provide meta data, it will provide the meta data of the first query only and according to this functionality this name (sp_describe_first_result_set) is given to this stored procedure. Lets execute this scenario.

EXEC sp_describe_first_result_set @tsql =N'SELECT * FROM [HumanResources].[Department];
SELECT * FROM [HumanResources].[Employee];'

Now, lets look at the different option of @browse_information_mode in sp_describe_first_result_set

Lets create a view to explain the different option of @browse_information_mode.

Use AdventureWorks2012
Create View [HumanResources].[vDepartment]
SELECT [DepartmentID] as [ID]
,[Name] as [Department Name]
FROM [HumanResources].[Department]

Now, lets execute sp_describe_first_result_set with the different option of @browse_information_mode.


Use AdventureWorks2012
EXEC sp_describe_first_result_set
 @tsql =N'SELECT * FROM [HumanResources].[vDepartment];'
-- When @browse_information_mode=0, it will give you the meta data but no source data available in this option.


Use AdventureWorks2012
EXEC sp_describe_first_result_set
@tsql =N'SELECT * FROM [HumanResources].[vDepartment];'
-- When @browse_information_mode=1, it will give you the meta data along with the source info but the source details will be based on this view's table.


Use AdventureWorks2012
EXEC sp_describe_first_result_set
@tsql =N'SELECT * FROM [HumanResources].[vDepartment];'
-- When @browse_information_mode=2, it will give you the meta data along with the source info but the source details will be based on this view.

Reference : MSDN

Read Full Post »