Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: SQL Server 2008: Table Valued Parameters

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2008-07-24 : 09:29:03

In SQL Server 2005 and earlier, it is not possible to pass a table variable as a parameter to a stored procedure. When multiple rows of data to SQL Server need to send multiple rows of data to SQL Server, developers either had to send one row at a time or come up with other workarounds to meet requirements. While a VB.Net developer recently informed me that there is a SQLBulkCopy object available in .Net to send multiple rows of data to SQL Server at once, the data still can not be passed to a stored proc.

Possibly the most anticipated T-SQL feature of SQL Server 2008 is the new Table-Valued Parameters. This is the ability to easily pass a table to a stored procedure from T-SQL code or from an application as a parameter.

Read SQL Server 2008: Table Valued Parameters

Starting Member

1 Post

Posted - 2008-08-11 : 16:02:49
In SQL 2005 you could achieve this by passing an XML Data Type containing the table structure you want and then convert this XML to the table in the Procedure working on (SP/Functions).

Dont you think the new method is more procedural , I would have expected it to be an extension of using XML Data Type rather than creating a UDT

Thoughts ?

Thanks and Regards
Go to Top of Page

Starting Member

2 Posts

Posted - 2008-12-05 : 14:28:12
I would think that with the XML Data Type you would have to do a lot of shredding work to get the XML into a table whereas with the table it is already in the table structure and ready for set-based operations. I'm not seeing the procedural nature of the new method.
Go to Top of Page

Starting Member

1 Post

Posted - 2009-02-14 : 04:22:52
Nice Post,

seems to be like problem at my end

I am dong same what you mentions... my problem is... the data which I am passing is not captured inside the procedure.

I checked with profiller it generates textdata which contains sp_executesql and when I do that textdata with EXEC its giving proper outpuy.

here are the text data.

declare @p3 dbo.UserInfo
insert into @p3 values('Imran','Bhadelia')
insert into @p3 values('Abidali','Suthar')

exec sp_executesql N'SP_TestForTable',N'@UserTable UserInfo READONLY',@UserTable=@p3

The procedure will simply select data from coming table. And this will give me 0 rows updated.

when I modify the text and write like following

declare @p3 dbo.UserInfo
insert into @p3 values('Imran','Bhadelia')
insert into @p3 values('Abidali','Suthar')

EXEC SP_TestForTable @p3

, its working fine. Can you please guide me what wrong here?

If you need more details about this problem, please let me know will provide samle applicaiton for the same.

Few have audacity to speak truth
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 04:36:59
whts UserInfo? is it user defined datatype?
Go to Top of Page

Starting Member

1 Post

Posted - 2010-02-23 : 05:38:55
you can use SqlBulkCopy but the table valued parameter is better for me because i can play with the table inside the stored procedure and reformat it.
but in SqlBulkCopy you must not declare the user defined type i.e you should not touch the DB

Go to Top of Page

Starting Member

1 Post

Posted - 2012-04-04 : 01:35:12
Useful stuff.
Go to Top of Page

- Advertisement -