Showing posts from November, 2014

How to pass a data table into a stored procedure as a Table Value Parameter (TVP) in SQL server 2008 and above.

In some instances there might be a need to manipulate an entire data set rather than passing individual values as variables into a database. To do this I explain how to start by creating the proc with the parameters then obtaining the inserted identity values from the insert statement to update a related table. Note the special table inserted which is an SQL table which stores the values of an insert just before this hits your table. Refer MSDN on inserted and deleted Tables 1.To start with define the table value parameter under your database ->programmability->Types-> User-Defined Table Types 2.Right click on the User Defined Table Types Create a data table definition of the set needed to be passed. Clicking on CTRL-Shift-M would bring up the Dialog to create them visually. The script should look like the following -- ================================ -- Create User-defined Table Type -- ================================ USEAdventureWorks GO
-- Create the data type CREATETYPEdbo.MY_TV…