Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2005- how to pass e.g. 5 Orders(ID,Price,Discount) as variables for 1 transaction

Posted on 2013-06-03
2
Medium Priority
?
360 Views
Last Modified: 2013-06-04
Hi, I have a nr of records(e.g. Orders) which need to be grouped into one Batch

OrderID	Price Discount
1	5.12	1.00
2	6.00	1.05
3	7.00	0
4	8.00	0
5	9.00	0

Open in new window


The problem is that I work with SQL 2005, and I cannot pass a table variable with the above mentioned example.
I for e.g. need to send these 5 lines to a proc, and need to insert all 5 lines (they can vary and there is no maximum nr of lines set - but I am aware that the
variables I pass to SQL 2005 have a character limit of 8000 chars - there is a check for this is and it will not be reached) in a transaction or rollback the transaction.

I do not have an idea of how I will get the above lines into variables in order to pass them as a variable


Example:

Alter Procedure sp_Batch
(
@XYZ nvarchar(max)
)


BEGIN TRANSACTION

	Insert into Batch(OrderID, Price, Discount)

COMMIT

Open in new window





I am desperate and am beginning to think of methods that are not good.
E.g.




Alter Procedure sp_Batch
(
@OrderIDs nvarchar(max)
@Prices nvarchar(max)
@Discounts nvarchar(max)
)

set @OrderIDs  = '1,2,3,4,5'
set @Prices    = '5.12, 6 , 7, 8,9 '
set @Discounts = '1, 1.05,0,0,0

Open in new window


This would get me all the variables, but now I do not know how to get them back into a @Table variable.

Manually it would have been:
Insert into @Table(OrderID,Price,Discount) select 1,5.12,1 
Insert into @Table(OrderID,Price,Discount) select 2,6,1.05 

Open in new window

etc

Any suggestions on how I can get
OrderID	Price Discount
1	5.12	1.00
2	6.00	1.05
3	7.00	0
4	8.00	0
5	9.00	0

Open in new window


into a few parameters in order to rebuild a table variable in SQL that looks like the aforementioned please?
0
Comment
Question by:jxharding
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 2000 total points
ID: 39217401
ok, from where you have to send these values is it an other Stored procedure or an application code (like c#, vb.net...).

if it is an other stored procedure and if you are sure that they run in the same context then create a temporary table in your calling Stored procedure and populate it with the values and then use the temporary table in the called stored procedure to retrieve the values (very simple method)...

Incase your requirement is simple and straight passing values, then you can start using SQL Server XML and pass it as a XML doc instead (the 8000 char limit does not apply to XML)....
0
 

Author Closing Comment

by:jxharding
ID: 39218330
XML was the way to go, thank you for the help!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question