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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to enforce inte 8 43
Sql Data via Excel--performance issues 2 55
SQL 2005 - Memory Table Column Names 11 69
Need help with a query 6 67
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

911 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

22 Experts available now in Live!

Get 1:1 Help Now