There is one new feature in SQL Server 2008 that I am pretty excited about, that is the ability to pass table valued parameters onto stored procedures or functions. One scenario where this could be handy is when we have to insert multiple rows of data. For example, let say you have an application where you allow users to input multiple new employee information at once and you need to insert all those information into the employee table. Prior to SQL Server 2008, we probably would loop through each row (employee information) and call the stored procedure that insert the information to the employee table one row at the time. Alternatively we could parse those employees information into XML and pass the XML into a stored procedure where it will then process the XML and insert the information to the employee table. But now with SQL Server 2008, we could pass the new employees information as a table valued parameters into a stored procedure, which can make things simpler.
You might ask why not use bulk insert or bcp. According to Microsoft, in many cases, if the number of rows is less than 1,000, using table valued parameters actually might be better performance wise.
How to Use Table Valued Parameters
It is pretty simple actually, You just need to do the following:
- Create a user defined table type. This actually defined the structure for the table valued parameters.
- Create the stored procedure or user defined function that would utilized the user defined table type (created on Step 1) as its parameter.
- You can now pass table valued parameters to the stored procedure or user defined functions created on Step 2.
Example
Let say that I have an table called EmpInfo. I created the table using the following SQL Script:
CREATE TABLE dbo.EmpInfo (
EmpID INT IDENTITY(1,1) PRIMARY KEY,
EmpFirstName VARCHAR(25),
EmpLastName VARCHAR(25),
EmpTitle VARCHAR(25),
CreatedDate DATETIME);
Then I created a user defined table type called EmpInfoTableType using the following SQL Script:
CREATE TYPE dbo.EmpInfoTableType AS TABLE (
EmpFirstName VARCHAR(25),
EmpLastName VARCHAR(25),
EmpTitle VARCHAR(25));
I now can create a stored procedure that utilized EmpInfoTableType user defined table type as one of its input parameter by using the following SQL Script:
CREATE PROCEDURE dbo.InsertEmpInfoBatch (@EmpInfoBatch EmpInfoTableType READONLY)
AS
BEGIN
INSERT INTO EmpInfo (EmpFirstName, EmpLastName, EmpTitle, CreatedDate)
SELECT EmpFirstName, EmpLastName, EmpTitle, CURRENT_TIMESTAMP
FROM @EmpInfoBatch
END
GO
If you notice there is READONLY keyword when declaring the EmpInfoBatch parameter (table valued parameter). This is required for table valued parameters (or more of a restriction actually), as we can not do any data modification to the table valued parameters within the stored procedure and user defined function.
Now the fun part, we test it:
DECLARE @EmpInfoTable AS EmpInfoTableType;
INSERT INTO @EmpInfoTable
VALUES ('John', 'Doe', 'CEO'),
('Jane', 'Smith', 'CFO'),
('Mike', 'Fox', 'CTO')
EXEC dbo.InsertEmpInfoBatch @EmpInfoTable;
If we issue the following SQL Script:
SELECT EmpID, EmpFirstName, EmpLastName, EmpTitle, CreatedDate
FROM dbo.EmpInfo
We should get the following result:
Pretty simple, eh?
