SQL SERVER – Significance of Table Input Parameter to Stored Procedure

http://blog.sqlauthority.com/2013/01/31/sql-server-significance-of-table-input-parameter-to-stored-procedure-guest-post-by-vikas-munjal-koenig-solutions/

SQL Server has introduced a functionality to pass a table data form into stored procedures and functions. This feature greatly simplifies the process of developing. The reason being,  we need not worry about forming and parsing XML data. With the help of the table Input parameter to Stored Procedure we can save many round trips. Any SQL training will vouch for the fact that SQL is capable of accepting large, complex data in the form of parameters in a stored procedure.

Situation

Consider two tables

1) Sales Table

2)  SalesDetails Table

dbo.Sales

dbo.SalesDetails

In the Sales Table various products are there for a specific Sales Id. The SalesDetails Table also displays costs of these products along with them.

SaleID=1, for instance, has PurchaseOrderNumber=’BigOrder’. It has three products- Product1,Product2 and Product3.

We should note that SalesId in Sales Table functions as the primary key and function of secondary key is performed by SalesId in SalesDetails Table.

Whenever there is a new sale, we want to impart a unique and distinctive number to the sale in the sales table. Also, we should provide the SalesDetails Table with the sold products in the product column.

Explanation

We have to generate two stored procedures to provide a solution to this situation. We use the first stored procedure for inserting the data in the dbo.Sales table. With the second stored procedure we store the data in  dbo.SalesDetails table.

In case, we consider the situation displayed in the table above, we should use the first stored procedure only once for adding the data in the dbo.Sales table. To add the data in the dbo.SalesDetails table, we have to use the second stored procedure thrice. A total of four round trips occurs between the SQL server and application. The number of round trips can be reduced to one, if we use table Input parameter to Stored Procedure.

Step 1 – Open a new query window to the tempdb database

USE tempdb;
GO

Step 2 – Create a Sales and SalesDetails table

CREATE TABLE dbo.Sales
( SaleID INT IDENTITY PRIMARY KEY,
CustomerID INT,
PurchaseOrderNumber VARCHAR(20)

);
CREATE TABLE dbo.SalesDetails( SalesDetailID INT IDENTITY,SaleID INT REFERENCES dbo.Sales(SaleID),Description VARCHAR(50),Price DECIMAL(18,2)
);GO

Step 3 – Create traditional insert stored procedures for both tables

CREATE PROCEDURE dbo.SalesInsert
@CustomerID INT,
@PurchaseOrderNumber VARCHAR(20),
@SaleID INT OUTPUT
AS BEGIN
INSERT INTO dbo.Sales (CustomerID,PurchaseOrderNumber)
VALUES(@CustomerID,@PurchaseOrderNumber);
SELECT @SaleID = SCOPE_IDENTITY();
END;
GO
CREATE PROCEDURE dbo.SalesDetailInsert
@SaleID INT,
@Description VARCHAR(50),
@Price DECIMAL(18,2),
@SalesDetailID INT OUTPUT
AS BEGIN
INSERT INTO dbo.SalesDetails (SaleID,Description,Price)
VALUES(@SaleID,@Description,@Price);
SELECT @SalesDetailID = SCOPE_IDENTITY();
END;
GO

Step 4 – Show how we would have previously inserted an order

Here four round trips will occur in which we call dbo.SalesInsert stored procedure once to insert the data into dbo.Sales and dbo.SalesDetailInsert stored procedure thrice to insert all the products for a particular  sales id

DECLARE @SaleID INT;
DECLARE @SalesDetailID INT;
BEGIN TRAN;
EXEC dbo.SalesInsert 12,'BigOrder',@SaleID OUTPUT;
EXEC dbo.SalesDetailInsert @SaleID,'Product 1',12.3,@SalesDetailID OUTPUT
EXEC dbo.SalesDetailInsert @SaleID,'Product 2',14.6,@SalesDetailID OUTPUT
EXEC dbo.SalesDetailInsert @SaleID,'Product 3',122.35,@SalesDetailID OUTPUT
COMMIT;
GO
SELECT * FROM dbo.Sales;
SELECT * FROM dbo.SalesDetails;
GO

Now we create a table data type

Step 5 – Create a table data type to hold the sales details

CREATE TYPE dbo.SalesDetails AS TABLE
( Description VARCHAR(50),
Price DECIMAL(18,2)
);
GO

Step 6 – Modify the insert procedure to take detail lines as well

ALTER PROCEDURE dbo.SalesInsert
@CustomerID INT,
@PurchaseOrderNumber VARCHAR(20),
@SalesDetails dbo.SalesDetails READONLY,
@SaleID INT OUTPUT
AS BEGIN
BEGIN TRAN;
INSERT INTO dbo.Sales (CustomerID,PurchaseOrderNumber)
VALUES(@CustomerID,@PurchaseOrderNumber);
SELECT @SaleID = SCOPE_IDENTITY();
INSERT INTO dbo.SalesDetails (SaleID,Description,Price)
SELECT @SaleID, Description,Price
FROM @SalesDetails;
COMMIT;
END;
GO

Step 7 – Perform an insert with a single round-trip

With the help of table data type to a stored procedure only one round trip is needed

DECLARE @SaleID INT;
DECLARE @SalesDetails dbo.SalesDetails;
INSERT INTO @SalesDetails VALUES('Product 1',12.3),('Product 2',14.66),('Product 3',122.35);
EXEC dbo.SalesInsert 12,'BigOrder',@SalesDetails,@SaleID OUTPUT;
GO
SELECT * FROM dbo.Sales;
SELECT * FROM dbo.SalesDetails;
GO

Table input parameter in SQL is a massive march ahead where development and potential performance are concerned. It can lessen server round trips, utilize table constraints and widen the functionality of programming on the database engine.

http://itdeveloperzone.blogspot.in/2012/01/passing-datatable-to-stored-procedure.html

           

passing datatable to stored procedure

11:30 AM  SQL Server 2008  No comments

In this post, I will show you, how DataTable from C# is passed to a stored procedure in Sql Server. 

1.        Create a TableType with the structure as same of the DataTable in Sql Server 

2.        Make this TableType as input parameter of stored procedure. 

3.        Finally, pass the DataTable to the TableType parameter of the stored procedure

Note : This is supported on Sql Server 2008 onwards.

Step 1: First Create a Table Employee

CREATE TABLE Employee(

    EmpID        VARCHAR(10)

    , EmpName    VARCHAR(50)

    , Gender     CHAR(1)

    , DOJ        DATETIME 

)

Step 2 : Create a Table Type

CREATE TYPE EmpTableType AS TABLE (

    EmpID        VARCHAR(10)

    , EmpName    VARCHAR(50)

    , Gender     CHAR(1)

    , DOJ        DATETIME 

)

Step 3 : Create Stored Procedure that will take Table Type that we created in Step 2 as parameter 

CREATE PROCEDURE usp_GetEmpDetils(

    @EmpDet EmpTableType READONLY 

) AS 

BEGIN    

    INSERT INTO Employee

    SELECT * FROM @EmpDet

    SELECT * FROM Employee

END

Step 4 : ADO.Net Code passing DataTable to the TableType Parameter in Stored Procedure

DataTable EmpTable = new DataTable();

EmpTable.Columns.Add("EmpID");

EmpTable.Columns.Add("EmpName");

EmpTable.Columns.Add("Gender");

EmpTable.Columns.Add("DOJ");

DataRow EmpRow = EmpTable.NewRow();

EmpRow["EmpID"] = "EMP0001";

EmpRow["EmpName"] = "Sandeep Mittal";

EmpRow["Gender"] = "M";

EmpRow["DOJ"] = "01/01/2010";        

EmpTable.Rows.Add(EmpRow);

EmpTable.AcceptChanges();

SqlConnection connection = new SqlConnection("data source=ServerName;database=DBName;uid=UserID;pwd=Password");

SqlCommand selectCommand = new SqlCommand("usp_GetEmpDetils", connection);

selectCommand.CommandType = CommandType.StoredProcedure;

SqlParameter tvpParam = selectCommand.Parameters.AddWithValue("@EmpDet", EmpTable);

tvpParam.SqlDbType = SqlDbType.Structured;

connection.Open();

grid.DataSource = selectCommand.ExecuteReader();

grid.DataBind();

connection.Close();