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?
- The column names have been changed
- The data types have been set
- 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:
and either press preview or check out columns page (below) to see column changes
The advanced editor shows us the data type changes
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