I was attempting to create a modularized approach to a set of calculations in SQL Server. The result was the dreaded “Nested stored procedure” error. I had to use stored procedures instead of functions because I was using a cursor.
I figured that I could resolve this issue pretty easily if I could only pass in a TABLE variable to one of my stored procedures. At first glance, this did not appear to be a possibility until I discovered TYPES.
TYPES are one of those features that is not readily apparent in SQL Server, because I cannot see them in the SQL Server Manager. For my purposes, a TYPE is like a named table variable in SQL Server. And because it is a named variable, you can pass it into a stored procedure just like any other variable.
Here is an example of creating a TYPE in SQL:
CREATE TYPE MonthlyTimesheetEntries AS TABLE
timesheet_id int identity,
If you need to add or modify any of the fields defined in the type, you can simply drop the TYPE and recreate it, assuming you don’t have any dependencies.
DROP TYPE MonthlyTimesheetEntries
Please Note: Once you pass the TYPE into a stored procedure, you can no longer DROP it. To work around this, I backup my stored procedure, delete it. Drop and recreate the type, and then recreate my stored procedure.
Once you have created the TYPE, you can use it as a stored procedure parameter:
// you must pass it in as read only
CREATE PROCEDURE [dbo].[sCalculateMonthlyInvoiceAmount]
@timesheet_entries ManagedServiceMonthlyTimesheetEntries READONLY
And you can simply populate and pass in the TYPE like this:
// populate type
DECLARE @timesheet_entries MonthlyTimesheetEntries
DECLARE @month_date datetime
SET @month_date = ‘8/1/2012’
INSERT INTO @timesheet_entries
EXEC sGetTimesheetEntries @month_date
// pass in type
EXEC sCalculateMonthlyInvoiceAmount @month_date, @timesheet_entries