SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: SQL Server 2008: Table Valued Parameters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 07/24/2008 :  09:29:03  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote

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 Posts

Posted - 08/11/2008 :  16:02:49  Show Profile  Reply with Quote
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

corder
Starting Member

USA
2 Posts

Posted - 12/05/2008 :  14:28:12  Show Profile  Click to see corder's MSN Messenger address  Reply with Quote
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

bhadelia.imran
Starting Member

India
1 Posts

Posted - 02/14/2009 :  04:22:52  Show Profile  Visit bhadelia.imran's Homepage  Click to see bhadelia.imran's MSN Messenger address  Reply with Quote
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/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/14/2009 :  04:36:59  Show Profile  Reply with Quote
whts UserInfo? is it user defined datatype?
Go to Top of Page

alaa_barqawi
Starting Member

Jordan
1 Posts

Posted - 02/23/2010 :  05:38:55  Show Profile  Reply with Quote
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
Go to Top of Page

notboring
Starting Member

USA
1 Posts

Posted - 04/04/2012 :  01:35:12  Show Profile  Reply with Quote
Useful stuff.

Edited by - notboring on 04/04/2012 01:49:48
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000