Skip to main content

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
-- ================================
USE AdventureWorks
GO

-- Create the data type
CREATE TYPE dbo.MY_TVP AS TABLE
(
       c1 int NOT NULL,
       c2 char(10) NULL,
       c3 datetime NULL,
    PRIMARY KEY (c1)
)
GO

--the table we want the values inserted into note the identity column which we need after the insert is done.
CREATE TABLE [dbo].[tbltest](
       [ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
       [c1] [int] NULL,
       [c2] [char](10)  NULL,
       [c3] [datetime]  NULL
      

Once the above is done define your proc and utilize the above TVP as a type as below:

-- =============================================
-- Define a proc with the TVP value
-- =============================================
create PROCEDURE [dbo].[spSave]
       -- Add the parameters for the stored procedure here
       @Mytable MY_TVP Readonly

AS
BEGIN
-- Use this table to hold the inserted Identity values of table 1

Declare @IDList Table(ID int);

       -- this is a general insert, just work with the table variable as per normal table.
             Insert into tbltest (c1,C2,C3)
Output INSERTED.ID Into @IDList(ID)
                    select  * from
                    @Mytable
                     
             insert into relatedTable
                    select ID,@SomeID from @IDList  
      

END

Comments

Popular posts from this blog

Using SSRS web services to render a report as a PDF

I have been looking around the net for some decent code which would explain how I could render a report, using SSRS 2008 web services as a PDF.   The need was to extract reports sitting on a SSRS 2008 server sitting on a NT domain on a trusted network, whereas my web server was sitting in a DMZ. Where the only communication allowed by the network admin was port 80. To do this you will need to use the SSRS2008   ReportExecution2005.asmx web service. This could be accesses using the following URL assuming your SSRS server was installed using the default settings. http://YourServerIP/reportserver/reportexecution2005.asmx?wsdl 1.        Create a user on your AD domain with the least amount of privileges (say ReportUser) 2.        Give this account browse access on the reporting server for the desired reports. 3.        To get this working in visual studio 2010 (I am using the Premium edition)   Right click on your project and add a service reference to the above web serv

Integrating MIGS eGate Payment gateway using C# for Oz businesses (Server-Hosted Payment method)

If you ever wanted to include the eGate payment gateway in your ASP.Net site and want to save yourself the time in creating the class, here’s how to do it. First things first you will need a merchant account with ANZ who will give you the following ·          Merchant Account ·          User name ·          password The details could be downloaded from the ANZ web site http://www.anz.com.au/australia/business/merchant/DownloadDevKit.asp I am doing this for an Aus base client thus the process might be different to your own country. Please check with the issuing bank for details. Have a read of the Merchant Admin guide. Login to the merchant account and create an operator ID, Login as the operator and obtain the access code. I will not going to the details as this is all documented in the admin guide. What you will need for the implementation would be vpc_AccessCode vpc_Merchan MIGS gateway URL:https://migs.mastercard.com.au/vpcdps The site has numerous

Read any Json data into a table structure using Dynamic SQL and save time write long queries.

Let's say you have a MYSQL database that stores form data as a JSON string. To read the data and manipulate it as SQL, you can use SQL link services. Here's how it works: First, you create a temporary table to store the data from MYSQL. You can use the following SQL script to do this: sql /****** Object: Table [dbo].[tbljson] Script Date: 29/03/2023 10:37:48 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[#tbljson]( [ID] [ int ] IDENTITY ( 1 , 1 ) NOT NULL , [form_id] [ int ] NOT NULL , [params] [nvarchar](max) NULL , CONSTRAINT [PK_tbljson] PRIMARY KEY CLUSTERED ( [ID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] ) ON [ PRIMARY ] TEXTIMAGE_ON [ PRIMARY ] GO Once you've created the temporary table, you can insert the values from the MYSQL database into it using the following SQL script: insert into #tbljson s