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
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:
I am desperate and am beginning to think of methods that are not good.
E.g.
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:
Any suggestions on how I can get
into a few parameters in order to rebuild a table variable in SQL that looks like the aforementioned please?
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
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
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
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
etcAny 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
into a few parameters in order to rebuild a table variable in SQL that looks like the aforementioned please?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER