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
359 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 500 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
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.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

623 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