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.

SQL SERVER - sp_describe_first_result_set New System Stored Procedure in SQL Server 2012 describefirst

Solarwinds

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.

SQL SERVER - sp_describe_first_result_set New System Stored Procedure in SQL Server 2012 describefirst1

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 (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Online Index Rebuilding Index Improvement in SQL Server 2012
Next Post
SQL SERVER – A Puzzle – Illusion – Confusion – April Fools’ Day

Related Posts

7 Comments. Leave new

  • Thanks for sharing and revealing one more feature of SQL SERVER 2012 :)

    Reply
  • abhinav srivastav
    May 3, 2012 1:30 am

    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

    Reply
  • 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

    Reply
  • hello worldl, i am new herer

    Reply
  • Is there any way to get metadata from a SP which uses Temporary Tables having multiple result sets?

    Reply

Leave a Reply

Menu