Welcome to EMC Consulting Blogs Sign in | Join | Help

Claypole's World - The SQL Server Side

Concerning development of bespoke applications and database administration.

Denali : Using EXECUTE WITH RESULT SETS (example using SSIS Source Adaptors)

One of my pet peeves about stored procedures is the lack of clarity they provide a user on the data types of returned columns.  This is especially applicable when the column returned has been calculated. Consider a statement such as this SELECT 10.0/5. It’s not obvious what that data type is. Now consider the fact that as the SQL developer I might decide to change the statement to be SELECT 10/5.

As I am sure you know the first returns a decimal and the second an int.  The interface therefore is inherently weak. There is nothing in the stored procedure programming paradigm that hardens that interface. Given that the result set of a stored procedure is actually a public interface or external contract that could potentially be consumed by any number of data consumers I’ve often thought of this as a bit of problem.  The only way to work around this lack of hardening is to place an explicit cast on each column to guarantee that regardless of the change to the column the data type remains consistent i.e. SELECT CAST(10/5 as decimal(8,6)). Note though that this isn’t a complete solution – it doesn’t enable us to say anything about nullability.

This has now changed in Denali.  Denali offers an extension to the EXECUTE statement called with RESULT SETS.

I won’t bore you with a re-iteration of books online however that article is available here.

A modified example (Books Online version returns an error) from the CTP1 version of Books Online is below.

USE AdventureWorks2008R2;
GO

EXEC uspGetEmployeeManagers 16
WITH RESULT SETS
( 
   ([Reporting Level] int NOT NULL,
    [ID of Employee] int NOT NULL,
    [Employee First Name] nvarchar(75) NOT NULL,
    [Employee Last Name] nvarchar(150) NOT NULL,
    [Employee ID of Manager] nvarchar(max)  NOT NULL,
    [Manager First Name] nvarchar(75) NOT NULL,
    [Manager Last Name] nvarchar(150) NOT NULL )
);
N.B. I have modified the data types for illustration purposes from the actual values.
What can we see from this?
  1. The column names have been changed
  2. The data types have been set
  3. Nullability can be enforced

A good step forward I think you’ll agree. This is especially handy in SSIS as I can use this much friendlier syntax when creating queries against data sources.

All you need to do is drop in the query above:

image_thumb2

and either press preview or check out columns page (below) to see column changes

image_thumb1

The advanced editor shows us the data type changes

image_thumb3

That’s very cool I think. However this hasn’t resolved the stored procedure problem. After all I can still call this:

EXEC uspGetEmployeeManagers 16

and get the original data types.

This is still useful but does mean one would need to carry round and include the interface definition wherever the stored procedure was used.

Not a great single version of the truth story there then.

However, I can also augment the stored procedure to create that interface like so:

ALTER PROCEDURE [dbo].[uspGetEmployeeManagers]
    @BusinessEntityID [int]
AS

SET NOCOUNT ON;

EXECUTE sp_executesql 
    N'-- Use recursive query to list out all Employees required for a particular Manager
    WITH [EMP_cte]
        ([BusinessEntityID]
        ,[OrganizationNode]
        ,[FirstName]
        ,[LastName]
        ,[JobTitle]
        ,[RecursionLevel]
        ) -- CTE name and columns
    AS (
        SELECT     e.[BusinessEntityID]
                ,e.[OrganizationNode]
                ,p.[FirstName]
                ,p.[LastName]
                ,e.[JobTitle]
                ,0 -- Get the initial Employee
        FROM    [HumanResources].[Employee] e 
        JOIN    [Person].[Person] as p        
        ON        p.[BusinessEntityID] = e.[BusinessEntityID]
        WHERE    e.[BusinessEntityID] = @BusinessEntityID
        UNION ALL
        SELECT     e.[BusinessEntityID]
                ,e.[OrganizationNode]
                ,p.[FirstName]
                ,p.[LastName]
                ,e.[JobTitle]
                ,[RecursionLevel] + 1 -- Join recursive member to anchor
        FROM    [HumanResources].[Employee] e 
        JOIN    [EMP_cte]                
        ON        e.[OrganizationNode] = [EMP_cte].[OrganizationNode].GetAncestor(1)
        JOIN    [Person].[Person] p        
        ON        p.[BusinessEntityID] = e.[BusinessEntityID]
    )
    -- Join back to Employee to return the manager name 
    SELECT     [EMP_cte].[RecursionLevel]
            ,[EMP_cte].[BusinessEntityID]
            ,[EMP_cte].[FirstName]
            ,[EMP_cte].[LastName]
            ,[EMP_cte].[OrganizationNode].ToString() AS [OrganizationNode]
            ,p.[FirstName] AS ''ManagerFirstName''
            ,p.[LastName] AS ''ManagerLastName''  -- Outer select from the CTE
    FROM    [EMP_cte] 
    JOIN    [HumanResources].[Employee] e 
    ON        [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
    JOIN    [Person].[Person] p 
    ON        p.[BusinessEntityID] = e.[BusinessEntityID]
    ORDER BY [RecursionLevel]
            ,[EMP_cte].[OrganizationNode].ToString()
    OPTION (MAXRECURSION 25)'
    ,N'@BusinessEntityID [int]'
    ,@BusinessEntityID = @BusinessEntityID
WITH RESULT SETS
( 
   ([Reporting Level] int NOT NULL,
    [ID of Employee] int NOT NULL,
    [Employee First Name] nvarchar(75) NOT NULL,
    [Employee Last Name] nvarchar(150) NOT NULL,
    [Employee ID of Manager] nvarchar(max)  NOT NULL,
    [Manager First Name] nvarchar(75) NOT NULL,
    [Manager Last Name] nvarchar(150) NOT NULL )
);

Instinctively I like this. I like the separation. I feel like an interface developer and a contract has been included with this proc.


The casts are not cluttering up the rest of the syntax and neither are the aliases.

I am not limited to a single result set either. 
I can even say that no result will be returned by using the WITH RESULTS NONE option.

However, WITH RESULT SETS will not allow you to return a different number of columns to the actual procedure. Every column must be handled.

So a request such as this :

EXEC uspGetEmployeeManagers 16
WITH RESULT SETS
( 
   ([Reporting Level] int NOT NULL,
    --[ID of Employee] int NOT NULL,
    [Employee First Name] nvarchar(75) NOT NULL,
    [Employee Last Name] nvarchar(150) NOT NULL,
    [Employee ID of Manager] nvarchar(max)  NOT NULL,
    [Manager First Name] nvarchar(75) NOT NULL,
    [Manager Last Name] nvarchar(150) NOT NULL )
);

Returns an error like this:

Msg 11537, Level 16, State 1, Procedure uspGetEmployeeManagers, Line 9
EXECUTE statement failed because its WITH RESULT SETS clause 
specified 6 column(s) for result set number 1
, but the statement sent 7 column(s) at run time.

I haven’t yet tested this for performance but a quick check against the query plans shows no obvious differences between the two query plans.

This does suggest that the WITH RESULT SETS rules are applied after the fact and that there might be some cost in that translation.

However, I will save that particular investigation for later.

Cheers, James
Published Tuesday, November 16, 2010 3:36 PM by James.Rowland-Jones

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jamie.thomson said:

Not a bad new feature. Its a halfway-house to fixing my single most hated "feature" of T-SQL (i.e. No contract for sprocs). Why people continue to use them for situations where multi-statement TVFs work equally as well is beyond me!

Obvious question is why isn't this included as part of CREATE PROCEDURE?

Can't wait for the day when I can write "SELECT * FROM storedproc" - it'll come I'm sure.

-JT

November 16, 2010 3:46 PM
 

jamie.thomson said:

Oh P.S. I would have preferred Output table-values-params to this!

November 16, 2010 3:48 PM
 

Andy said:

Yes, I can see how this could be useful as a wrapper for legacy code.

November 16, 2010 3:58 PM
 

James Rowland-Jones said:

I don't know why they chose to implement it in this way. I will ask but I get the feeling it is implemented at a higher level than even a create procedure statement. I guess it also means that you get some additional flexibility as you can use it for any SQL Statement.

TVF's return a table variable and that also equals danger. No Stats. When people get creative - joins etc on an object with no stats = unhappiness in a number of scenarios.

http://blogs.msdn.com/b/psssql/archive/2010/10/28/query-performance-and-multi-statement-table-valued-functions.aspx

November 16, 2010 5:34 PM
 

Douglas Laudenschlager (Microsoft) said:

Sorry about the error that you found in the Books Online example! It was a simple data conversion error on the hierarchyid column, which we fixed today. Thanks for catching it.   -Doug

November 16, 2010 7:34 PM
 

James Rowland-Jones said:

That's no problem Douglas. I was going to drop you a note. It is a CTP build after all. I know you guys have it tough. I doubt people realise just how far ahead of the product development curve you guys have to be in order to make the deadlines, get the images done and the translations into the minimum number of languages supported for launch.

Having co-authored a book now I can say that I have had a taster of the effort involved and my hat does go off to you and the rest of the team.

November 17, 2010 7:47 AM
 

Richard said:

I'd much rather see the WITH RESULT SETS as part of the CREATE/ALTER PROCEDURE statement than have to wrap the body of every stored procedure with a call to sp_executesql. Something like:

CREATE/ALTER PROCEDURE {name}

(

   {parameters}

)

WITH RESULT SETS

(

   {result sets}

)

As

   {body}

However, I can see that this would be difficult for procedures which returned different result sets for different parameter values.

On a vaguely related note, I'd love to see an UPDATE PROCEDURE/FUNCTION/VIEW statement which would either create the object if it didn't exist or update it if it did. This would make it much easier to deploy new versions of procedures, functions and views without having to wrap everything in an "IF NOT EXISTS(SELECT * FROM sys.objects WHERE ..." block.

November 23, 2010 1:14 PM
 

Hennie de Nooijer said:

I understand the purpose of the WITH RESULT SETS although i would prefer more possibilities with this feature. Why not leaving some field out of the result sets or may be adding some fields for concatenating fields, etc.

What is the purpose of WITH RESULTS SETS NONE. A stored procedure without a result set won't give you a result set and there fore specifying that again at the EXEC seems a bit awkward to me.

Hennie

December 2, 2010 10:18 AM
 

Rafi Asraf said:

I agree that it would have made more sense to make this a part of the stored proc definition. But would on earth do you (Jamie) want to have a "select * from myProc"

For me there is a difference between a procedure and a function. One is made to return a value (function) the other to do something, to implement a procedure, in a procedural manner.  (from Wiki: "A procedure is a set of actions or operations which have to be executed in the same manner in order to always obtain the same result under the same circumstances (for example, emergency procedures)").

The design patterns of building a SP for each table, that implicit a select on the relevant columns from the table is a bad design IMHO. Giving the ability to select from a procedure, will only make this trend used more often. Build a view instead, if you must have code and parameters, I cannot see why, nut if you must, then use a table function.

We must not forget the relational theory, and the set based approach. use views, joins, CTE etc.

April 20, 2012 12:31 PM
 

clem said:

Hello,

The same style of code doesn't work at all in my SQLServer 2008 Management Studio.

SQLServer show me an error message telling me that the syntax is not correct around the keyword 'result'.

There's any way to make this code work, but i absolutely need to get the result of sp_executesql in an underquery (I wanna use it like the datasource in a merge instruction).

Is there any parameter to set before trying to make this work ?

August 9, 2013 12:13 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit

About James.Rowland-Jones

James is an Advisory Practice Consultant with EMC Consulting. He works primarily with SQL Server technologies in architecture, development and administration capacities. He also co-ordinates our Internal SQL Server Community.

View James Rowland-Jones's profile on LinkedIn

Powered by Community Server (Personal Edition), by Telligent Systems