Loading...

Table Value Parameter in SQL Server 2008

View: 857    Dowload: 0   Comment: 0   Post by: hanhga   Category: SQL Server   Fields: Computers - Technology

Facts about Table Value Parameters

Here are a few facts that you must know before starting to use the Table Value Parameter:

  1. Helps address the need to pass an “array” of elements to a Stored Procedure / function.
  2. Table-valued parameters are used to send multiple rows of data to a Transact-SQL statement or a routine, such as a Stored Procedure or function, without creating a temporary table or many parameters.
  3. Overcomes both security and performance issues in a dynamic SQL approach. Internally treated like a table variable.
  4. Scope is batch.
  5. Not affected by rollback (beyond the atomic statement scope).
  6. No histograms/distribution statistics.
  7. When parameter value not provided, defaults to empty table.

Example

CREATE TYPE dbo.OrderIDs AS TABLE 
( 
  pos INT NOT NULL PRIMARY KEY,
  orderid INT NOT NULL UNIQUE
)

DECLARE @T AS dbo.OrderIDs;

INSERT INTO @T(pos, orderid) VALUES(1, 10248)
INSERT INTO @T(pos, orderid) VALUES(2, 10250)
INSERT INTO @T(pos, orderid) VALUES(3, 10249);

SELECT * FROM @T;

In the above code snippet, we create a Table User Defined Type. In SQL Server 2008, we can define Table UDTs (this is a new feature in SQL Server 2008). Then, create a variable using the newly created UDT and insert a few records into the variable and query the same.

CREATE PROC dbo.sp_GetOrders
(@T AS dbo.tbl_Order READONLY)
AS
SELECT O.OrderID, O.OrderDate, O.CustomerID
FROM dbo.tbl_Order AS O JOIN @T AS T ON 
    O.OrderID = T. OrderID
ORDER BY T. RecordID;
GO

DECLARE @MyOrderIDs AS dbo.tbl_Order;
INSERT INTO @MyOrderIDs(pos, orderid) VALUES(1, 10248)
INSERT INTO @MyOrderIDs(pos, orderid) VALUES(2, 10250)
INSERT INTO @MyOrderIDs(pos, orderid) VALUES(3, 10249);

EXEC dbo.sp_GetOrders @T = @MyOrderIDs;

The above code snippet shows how to use the Table UDT as a parameter in SQL Stored Procedure.

Conclusion

So now, with the Table UDT and the ability to pass the table value parameter to Stored Procedures and functions, we need lesser code and also get performance benefits.

Hope you enjoyed this article. Happy programming!!!

Table Value Parameter in SQL Server 2008

One of the fantastic new features of SQL Server 2008 is the Table value parameter. In previous versions of SQL Server, there wasn’t a native way to pass a table to a Stored Procedure or functions. The usual workaround was to pass a large varchar or the XML type and parse through it. Now, in SQL Server 2008, Table parameters are available. Many a times, we need to utilize a single Stored Procedure to update multiple database tables with one-to-many relationships. In such occasions, we end up concatenating large strings, and inside the procedure, end up parsing the string to get records. With SQL Server 2008, it is very simple, and we no more need to write tons of lines of code to implement such requirements.

Posted on 09-04-2014 

Comment:

To comment you must be logged in members.

Files with category

  • Data Encryption in SQL Server using T-SQL Functions (ENCRYPTBYPASSPHRASE, DECRYPTBYPASSPHRASE & HASHBYTES)

    Data Encryption in SQL Server using T-SQL Functions (ENCRYPTBYPASSPHRASE, DECRYPTBYPASSPHRASE & HASHBYTES)

    View: 507    Download: 0   Comment: 0

    Category: SQL Server     Fields: Other

    Decade ago data was just an entity which helped business to operate smoothly. By then data was considered as some sort of business related information just stored in a database, which can be retrieved based on the demand/requirement as per the...

  • Building a Multi-Cloud Strategy

    Building a Multi-Cloud Strategy

    View: 305    Download: 0   Comment: 0

    Category: SQL Server     Fields: Other

    Today, few companies are in a position to leverage a single cloud for all their needs. Even as service providers add multiple services like email and collaboration to try to keep people in the ecosystem, companies need a multi-cloud strategy.

  • Insufficient access rights error when doing backup in SQL Server

    Insufficient access rights error when doing backup in SQL Server

    View: 683    Download: 0   Comment: 0

    Category: SQL Server     Fields: none

    All of sudden SQL Server Management studio gives me the following error message when I'm trying to browse for the backup file location.

  • Migrate MySQL to Microsoft SQL Server

    Migrate MySQL to Microsoft SQL Server

    View: 2800    Download: 0   Comment: 0

    Category: SQL Server     Fields: none

    This article describes a few simple steps in order to migrate MySQL into Microsoft SQL Server 2005. The technique is very easy, but useful if you plan to move your data from MySQL and upgrade it finally to a Microsoft SQL Server environment.

  • COUNT of DISTINCT Rows in SQL Server

    COUNT of DISTINCT Rows in SQL Server

    View: 1602    Download: 0   Comment: 0

    Category: SQL Server     Fields: Other

    SQL Server does not support COUNT(DISTINCT *). For example, the below query fails.

  • Finding SQL Servers on the Network

    Finding SQL Servers on the Network

    View: 942    Download: 0   Comment: 0

    Category: SQL Server     Fields: Other

    I am a TSQL fanatic. The programs I code are highly dependent upon MS SQL stored procedures. I pay for this love of TSQL when a major overhaul of the system is necessary. Sometimes my code needs to be updated heavily in two places; client and...

  • SQL Server and Database Enumerator

    SQL Server and Database Enumerator

    View: 958    Download: 0   Comment: 0

    Category: SQL Server     Fields: Other

    This sample helps you to enumerate the list of SQL Servers and the databases and languages that a particular SQL server has. The class CSQLInfoEnumerator encapsulates this functionality into it.

  • Dynamic Management Views [DMV] – A SQL Server 2005 Feature

    Dynamic Management Views [DMV] – A SQL Server 2005 Feature

    View: 926    Download: 0   Comment: 0

    Category: SQL Server     Fields: Other

    The DMVs; newly introduced in SQL Server 2005 gives the database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance....

 
File suggestion for you
Loading...
File top downloads
Loading...
Loading...
Codetitle - library source code to share, download the file to the community
Copyright © 2018. All rights reserved. codetitle Develope by Vinagon .Ltd