New tsql commands in Sql Server 2012 (Part 2)

The new tsql feature i am writing about today is the with result set feature. Until now if you wanted to change the column name or datatype of the result set of an stored procedure you needed a temptable.

This is the procedure we use to return a dataset.

create procedure testProcedure
(
    @maxid int
)
as
    select 
            id,
            data 
    from 
            testData 
    where 
            id < @maxid
    

Here is the pre sql server 2012 tsql code to rename or change the datatype of the result set of the procedure.

DECLARE @tempTable TABLE (MyIdbigint, MyText nvarchar(MAX))
 
INSERT INTO @tempTable
EXEC testProcedure @maxid = 5
 
SELECT * FROM @tempTable

If you execute this tsql code you get the following result:

In sql server 2012 you could use the with result set command, to rename or change the datatype of the result set of an stored procedure.

EXEC testProcedure @maxid = 5
WITH RESULT SETS
(
    (
        MyId bigint,
        MyText nvarchar(max)
    )
)

If you execute this tsql code you get the following result:

Advertisements


If you have a note or a question please write a comment.

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