T-SQL Enhancements in SQL Server 2008 phase-4.2

Hello,

In my previous post, we have seen table value parameters, how to define it in sql server and asp.net, How to use Table Valued Parameters, Advantages of Table Valued Parameters, Limitations to passing Table Parameters etc.

Today we will see the example of Table Valued Parameters

I have used two tables Orders and OrderDetails. You can see the User Defined Table Type with the same structure.

I have created procedure “InsertOrders”. You need to pass the Table Type Variable as a parameter from asp.net

Example:

CREATE TABLE [Orders]
(
	OrderId UNIQUEIDENTIFIER NOT NULL,
	CustomerId INT NOT NULL,
	OrderedDate DATE NOT NULL,
	CreatedDate DATETIME2(0) NOT NULL
)
GO

CREATE TABLE [OrderDetails]
(
	OrderId UNIQUEIDENTIFIER NOT NULL,
	ProductId INT NOT NULL,
	Quantity INT NOT NULL,
	Price MONEY NOT NULL,
	CreatedDate DATETIME2(0) NOT NULL
)
GO

CREATE TYPE OrderUdt AS TABLE
(
	OrderId UNIQUEIDENTIFIER,
	CustomerId INT,
	OrderedDate DATE
)
GO

CREATE TYPE OrderDetailUdt AS TABLE
(
	OrderId UNIQUEIDENTIFIER,
	ProductId INT,
	Quantity INT,
	Price MONEY
)
GO

CREATE PROCEDURE InsertOrders
(
	@OrderHeaders AS OrderUdt READONLY,
	@OrderDetails AS OrderDetailUdt READONLY
)
AS
BEGIN

 -- Bulk insert order header rows from TVP
 INSERT INTO [Orders]
 SELECT *, SYSDATETIME() FROM @OrderHeaders

 -- Bulk insert order detail rows from TVP
 INSERT INTO [OrderDetails]
 SELECT *, SYSDATETIME() FROM @OrderDetails

 END
GO

SELECT name, system_type_id, user_type_id  
FROM sys.types
WHERE is_table_type = 1

/*DROP TYPE OrderDetailUdt
DROP TYPE OrderUdt*/

SELECT * FROM Orders
SELECT * FROM OrderDetails

T-SQL Enhancements in SQL Server 2008 phase-4.1

Hello,

we have seen some features of sql server 2008 in my previous article like Grouping Sets, cube and rollup, grouping_ID, merge statement, compund assignment operators, declare and initialize variables, Row constructors etc. Now today i am going to describe Table-Valued Parameters.

Table-Valued Parameters

  • Helps address the need to pass “array” of elements to stored procedure / function
  • Solutions pre-2008
    1. Dynamic SQL : Suffers from security and performance problems:
    a. Security: SQL Injection
    b. Performance: inefficient reuse of execution plans
    2. UDF that splits array to individual elements
    a. Overcomes security and performance issues
    b. Complicated

Advantages of Table Valued Parameters

  • It is easier to maintain and provides better performance than the use of temporary tables
  • Reduces multiple round trips between client and the server
  • It is much easier to program and use Table Valued Parameters
  • It doesn’t cause the statements to recompile
  • It enables you to include complex business logic in a single routine
  • They have a well defined scope at the end of which they are automatically cleared

How to use Table Valued Parameters

  • Create a User Defined Table Type with the same structure as that of the table, which can be passed as a Table Valued Parameter to a SP or a Function
  • Create a stored procedure which accepts table type as a parameter
  • Declare a variable as Table Type and refer the Table Type which you have created earlier
  • Populate the Table Type Variable by using an insert statement
  • Once the Table Type Variable is created and populated then the next step will be to pass the Table Type Variable as a parameter to the stored procedure or a function

Limitations to passing Table Parameters

  • You must use the READONLY clause when passing in the table valued variable into the procedure.
  • Data in the table variable cannot be modified. you can use the data in the table for any other operation.
  • Also, you cannot use table variables as OUTPUT parameters. you can only use table variables as input parameters.

That’s it. I will explain example in my next article.