2 replies to this topic

[Luthfi]

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.

1. Define custom data type matches table structure that you want to pass to a stored procedure or a function.
2. 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.
3. 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.

 

Live Action

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  usr01@email.com
Usr02    Usr02 Fullname  usr02@email.com
Usr03    Usr03 Fullname  usr03@email.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[123]')
BEGIN
	DELETE FROM dbo.MemberEmailAddresses
	WHERE [Member] in (SELECT [ID] FROM dbo.Members WHERE UsrName LIKE 'Usr0[123]');
	DELETE FROM dbo.Members WHERE UsrName LIKE 'Usr0[123]';
	DELETE FROM dbo.EmailAddresses WHERE [Address] LIKE 'Usr0[123]@%';
END;

-- Table variable declaration
DECLARE @NewMembers NewMemberTable;

-- insert some test records
INSERT INTO @NewMembers VALUES ('Usr01', 'Usr01 Fullname', 'usr01@email.com');
INSERT INTO @NewMembers VALUES ('Usr02', 'Usr02 Fullname', 'usr02@email.com');
INSERT INTO @NewMembers VALUES ('Usr03', 'Usr03 Fullname', 'usr03@email.com');

-- 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[123]';

When executing in SQL Server Management Studio, you might get something like this.

 

 

 

Summary

 

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.

[viveq]

usefull one

[Luthfi]

Thanks, man!