November 25, 2013 at 6:52 am
Hi,
Now a days i am converting my .net code in SP as much as possible. i am converting all sql queries to SPs to save data. all most all done. but i am stuck at a point where i have to save data which is coming from grid.
i can't understand how do i create SP to save all rows of grid in database table in just one call of SP ?
Eg :
data in grid looks like
ID Name city dob
-- ----- ---- ----
1 aaa NY 1-JAN-2000
2 BBB NY 10-JAN-2000
3 ccc WC 11-JUN-2001
suppose we have these records in our grid and i want to save all 3 rows in table in just 1 call of SP. how i can do this ?
Help.
November 25, 2013 at 7:58 am
KcV (11/25/2013)
Hi,Now a days i am converting my .net code in SP as much as possible. i am converting all sql queries to SPs to save data. all most all done. but i am stuck at a point where i have to save data which is coming from grid.
i can't understand how do i create SP to save all rows of grid in database table in just one call of SP ?
Eg :
data in grid looks like
ID Name city dob
-- ----- ---- ----
1 aaa NY 1-JAN-2000
2 BBB NY 10-JAN-2000
3 ccc WC 11-JUN-2001
suppose we have these records in our grid and i want to save all 3 rows in table in just 1 call of SP. how i can do this ?
Help.
This is where user defined table types can come in really handy.
http://technet.microsoft.com/en-us/library/bb522526.aspx
The basic gist of it is to define your table type and then pass the datasource for your grid as your parameter. In your proc you can easily handle it as a set based operation. You can make your life easier by using a MERGE in your proc so it can handle inserts and updates all at the same time.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply