Article: SQL Server 2008: Table Valued Parameters

Please start any new threads on our new site at https://forums.sqlteam.com. 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

AskSQLTeam
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

arunv.vijay
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

corder
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.

bhadelia.imran
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.

Imran
[MCTS]
Few have audacity to speak truth
http://knowledgebaseworld.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 04:36:59

whts UserInfo? is it user defined datatype?

alaa_barqawi
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
Thanks

Alaa

notboring
Starting Member

1 Post

Posted - 2012-04-04 : 01:35:12

Useful stuff.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources