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
351 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
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now