Creating a table-valued parameter (TVP) in a stored procedure in SQL Server involves several steps. Here, I'll guide you through the process step-by-step.
Step 1: Create a User-Defined Table Type
First, you need to define a user-defined table type. This will serve as the structure for your table-valued parameter.
CREATE TYPE MyTableType AS TABLE
(
ID INT,
Name NVARCHAR(100)
);
Step 2: Create the Stored Procedure
Next, create a stored procedure that uses the user-defined table type as a parameter.
CREATE PROCEDURE MyStoredProcedure
@MyTableParam MyTableType READONLY
AS
BEGIN
-- Your business logic goes here
-- For example, inserting data from the TVP into another table
INSERT INTO SomeOtherTable (ID, Name)
SELECT ID, Name
FROM @MyTableParam;
-- Additional logic can follow...
END
Step 3: Execute the Stored Procedure with a TVP
Now you can call the stored procedure and pass a table-valued parameter. First, you'll need to declare a variable of the type you created, then insert data into it.
DECLARE @MyTableVar MyTableType;
-- Insert data into the table variable
INSERT INTO @MyTableVar (ID, Name)
VALUES (1, 'John Doe'), (2, 'Jane Smith');
-- Execute the stored procedure and pass the table-valued parameter
EXEC MyStoredProcedure @MyTableVar;
Summary
1. Define a User-Defined Table Type - This is the structure for the TVP.
2. Create the Stored Procedure - Use the TVP as a parameter marked as READONLY.
3. Pass the TVP When Calling the Procedure - Create a variable of the defined type and populate it with data before executing the procedure.
This will allow you to pass complex sets of related data into your stored procedures efficiently.