Table Valued Parameters : Making life easier
CREATE TABLE dbo.People( PersonID int NOT NULL, Name varchar(100) NOT NULL, FavouritePizza varchar(100) NOT NULL) |
We now need to define a table variable type ( in other words , a user defined table type ). We do it this way so that the stored procedure we create will use this as a parameter, and will know what type of table variable to expect. Let us define a user defined table type to hold the information we need.
— Create Type CREATE TYPE PeopleTableType AS TABLE (PersonID int, Name nvarchar(100), FavouritePizza nvarchar(100)); GO |
Now for what is essentially the new part – creating a stored procedure that can accept this type as a parameter.Note that the parameter is READONLY.
CREATE PROCEDURE NewPerson (@PersonDetails PeopleTableType READONLY) As BEGIN INSERT INTO dbo.People SELECT * FROM @PersonDetails END |
We are now ready to call the stored procedure and pass a table variable to it . In this example , we declare a table variable of the type we created above, populate the table variable with some records, and then send it to the stored procedure, which inserts it into the final table.
DECLARE @NewPeople PeopleTableType
INSERT INTO @NewPeople VALUES (1,’John McLean’,’Quadro Staggioni’) EXEC NewPerson @NewPeople |
And that’s your bulk load with a stored procedure without using any temp tables !