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.

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.

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)

About these ads

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

  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

  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