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,
month_date datetime,
consultant_id int,
consultant_name varchar(50),
billing_code int,
billing_code_name varchar(50),
entry_date datetime,
total_hours money,
billing_rate money,
total_amount money
)

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]
@month_date datetime,
@timesheet_entries ManagedServiceMonthlyTimesheetEntries READONLY
AS
BEGIN

END

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
(

     timesheet_id,
month_date,
consultant_id,
consultant_name,
billing_code,
billing_code_name,
[entry_date],
[total_hours],
[billing_rate],
total_amount
)
EXEC sGetTimesheetEntries @month_date

// pass in type

EXEC sCalculateMonthlyInvoiceAmount @month_date, @timesheet_entries

Advertisements