Prior SQL Server 2008, in SQL Server 200 to be exact (I never have serious project with SQL Server 2005), I always wondering why we could not pass a table variable to a stored procedure. I often needed this to pas information in the form of records. At the time I solved the problem using temporary table. The solution is fine, I just think it would be better (for maintaining and simply more intuitive) if we can just pass populated local table variable.
SQL Server 2008 finally opens up this option. With SQL Server 2008 (and above), you can pass table variables to stored procedures and user defined functions and have them handle those tables without problem. No need for temporary table! Well, not really. We still need temporary table (or global cursor) if we want to get table-like information from the stored procedure. Because stored procedure still can't use table-valued parameter as output. In fact you need to specifically mark the table-valued parameter as READ ONLY.
Now we know the limitations, let's get down to business.
- Define custom data type matches table structure that you want to pass to a stored procedure or a function.
- Create that stored procedure or function and for the parameter where you want to pass the table, define it with type that you declared in step #1, and specifically add READONLY option.
- When using the stored procedure or function, just declare a local variable with type that you defined in step #1. Populate that table and then pass it when you execute the stored procedure or function.
Suppose we want to use a stored procedure to record new members. New members information will be passed in a table like this.
-------------------------------------- UsrName Fullname EmaillAddy ====================================== Usr01 Usr01 Fullname email@example.com Usr02 Usr02 Fullname firstname.lastname@example.org Usr03 Usr03 Fullname email@example.com
1. Define Custom Data Type Matches the table structure
Let's name this custom data type NewMemberTable. We declare it like this.
CREATE TYPE NewMemberTable AS TABLE ( UsrName nvarchar(50) PRIMARY KEY , FullName nvarchar(50) NOT NULL , EmailAddy nvarchar(254) NOT NULL );
Execute that command and you will get a new data type named NewMemberTable.
2. Create Stored Procedure Accepting Table-valued Parameter
CREATE PROCEDURE [dbo].[ImportMembers] @NewMembers NewMemberTable READONLY AS BEGIN DECLARE @UsrName nvarchar(50); DECLARE @FullName nvarchar(50); DECLARE @EmailAddy nvarchar(50); DECLARE @MemberID int; DECLARE @EmailID int; DECLARE NewMembersCursor CURSOR FAST_FORWARD FOR SELECT UsrName, FullName, EmailAddy FROM @NewMembers; OPEN NewMembersCursor; FETCH NEXT FROM NewMembersCursor INTO @UsrName, @FullName, @EmailAddy WHILE @@FETCH_STATUS=0 BEGIN INSERT INTO dbo.Members(UsrName, FullName) VALUES(@UsrName, @FullName); SELECT @MemberID=SCOPE_IDENTITY(); SET @EmailID=(SELECT [ID] FROM dbo.EmailAddresses WHERE [Address]=@EmailAddy); IF @EmailID IS NULL BEGIN INSERT INTO dbo.EmailAddresses([Address], RefCount) VALUES(@EmailAddy, 0); SELECT @EmailID=SCOPE_IDENTITY(); END; UPDATE dbo.EmailAddresses SET RefCount=RefCount+1 WHERE [ID]=@EmailID; INSERT INTO dbo.MemberEmailAddresses(Member, EmailAddy, Active) VALUES(@MemberID, @EmailID, 1); FETCH NEXT FROM NewMembersCursor INTO @UsrName, @FullName, @EmailAddy END; CLOSE NewMembersCursor; DEALLOCATE NewMembersCursor; END;
3. Declare and Populate Local Table Variable, then Feed to Stored Procedure
In your session, you can declare a local table variable of the custom type, in our example it's NewMemberTable.
-- Check if the same test records already exist in the database. -- If they exist, continue with removals. IF EXISTS(SELECT * FROM dbo.Members WHERE UsrName LIKE 'Usr0') BEGIN DELETE FROM dbo.MemberEmailAddresses WHERE [Member] in (SELECT [ID] FROM dbo.Members WHERE UsrName LIKE 'Usr0'); DELETE FROM dbo.Members WHERE UsrName LIKE 'Usr0'; DELETE FROM dbo.EmailAddresses WHERE [Address] LIKE 'Usr0@%'; END; -- Table variable declaration DECLARE @NewMembers NewMemberTable; -- insert some test records INSERT INTO @NewMembers VALUES ('Usr01', 'Usr01 Fullname', 'firstname.lastname@example.org'); INSERT INTO @NewMembers VALUES ('Usr02', 'Usr02 Fullname', 'email@example.com'); INSERT INTO @NewMembers VALUES ('Usr03', 'Usr03 Fullname', 'firstname.lastname@example.org'); -- show the table variable content SELECT * FROM @NewMembers; -- feed the table variable to ImportMembers stored procedure EXEC dbo.ImportMembers @NewMembers; -- show the vMemberEmailAddresses view to see the newly imported member info SELECT * FROM dbo.vMemberEmailAddresses WHERE UsrName LIKE 'Usr0';
When executing in SQL Server Management Studio, you might get something like this.
With SQL Server 2008 (and above), you can use table variable to pass information to stored procedure. Compared to using temporary variable or global cursor, this option frees you from the need to check (and recheck, and recheck) for possible problem caused by name clash or existing data overwriting.