Link to home
Start Free TrialLog in
Avatar of jxharding
jxharding

asked on

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

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?
ASKER CERTIFIED SOLUTION
Avatar of Surendra Nath
Surendra Nath
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jxharding
jxharding

ASKER

XML was the way to go, thank you for the help!