SQL SERVER – sp_describe_first_result_set New System Stored Procedure in SQL Server 2012

I might have said this earlier many times but I will say it again – SQL Server never stops to amaze me. Here is the example of it sp_describe_first_result_set. I stumbled upon it when I was looking for something else on BOL. This new system stored procedure did attract me to experiment with it. This SP does exactly what its names suggests – describes the first result set. Let us see very simple example of the same. Please note that this will work on only SQL Server 2012.

EXEC sp_describe_first_result_set
N'SELECT * FROM AdventureWorks.Sales.SalesOrderDetail', NULL, 1
GO

Here is the partial resultset.

describefirst SQL SERVER   sp describe first result set New System Stored Procedure in SQL Server 2012

Now let us take this simple example to next level and learn one more interesting detail about this function.

First I will be creating a view and then we will use the same procedure over the view.

USE AdventureWorks
GO
CREATE VIEW dbo.MyView
AS
SELECT
[SalesOrderID] soi_v
,[SalesOrderDetailID] sodi_v
,[CarrierTrackingNumber] stn_v
FROM [Sales].[SalesOrderDetail]
GO

Now let us execute above stored procedure with various options. You can notice I am changing the very last parameter which I am passing to the stored procedure.This option is known as for browse_information_mode.

EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView'
, NULL, 0;
GO
EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView'
, NULL, 1;
GO
EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView'
, NULL, 2;
GO

Here is result of all the three queries together in single image for easier understanding regarding their difference.

describefirst1 SQL SERVER   sp describe first result set New System Stored Procedure in SQL Server 2012

You can see that when BrowseMode is set to 1 the resultset describes the details of the original source database, schema as well source table. When BrowseMode is set to 2 the resulset describes the details of the view as the source database.

I found it really really interesting that there exists system stored procedure which now describes the resultset of the output.

Reference: Pinal Dave (http://blog.sqlauthority.com)

8 thoughts on “SQL SERVER – sp_describe_first_result_set New System Stored Procedure in SQL Server 2012

  1. Pingback: SQL SERVER – DMV sys.dm_exec_describe_first_result_set_for_object – Describes the First Result Metadata for the Module « SQL Server Journey with SQL Authority

  2. Hi Sir,

    Please help me, I want to make a stored procedure and I want to use @reportdate variable, can you tell me how to use whole month date in below SP when i m pass start date and end date with stored procedure

    Like

  3. Hello Sir,
    I am a newbie and have being following your blog for quite a some time. I did search on internet for the doubt i am going to ask but was not able to find the solution because my doubt might be very basic. Well i am saying that u had executed the stored procedure as “N’Name_of_the_procedure” . i had tried out without “N ” and i works out to be fine then why do we need “N'” to execute a stored procedure

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #023 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s