With an example how can we create a Table Valued Parameter in Sql Server 2008?

 Posted by Rajnilari2015 on 9/30/2015 | Category: Sql Server Interview questions | Views: 501 | Points: 40
Answer:

Assuming we have a table by the name tblTest with the below columns
ID  INT,

Name VARCHAR(50)


Step 1: Declare a new table User Defined Type
CREATE TYPE tblTestType AS TABLE

(
ID INT,
Name VARCHAR(50)
)


Step 2: Create a STORED PROCEDURE that has tblTestType as parameter
CREATE PROCEDURE countcv

(
@tblName tblTestType readonly
)

AS
INSERT INTO tblTest (ID, Name)
SELECT ID, Name
FROM
@tblName;


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response