SQL SERVER 2005 & Reporting Services
Hi, I have a requirement to pass multiple values to a stored procedure. As I have found out there is no array type in T-SQL. Having googled it, there are a lot of resources out there with one variation or other but as I am not wonderfully sql orientated its difficult to try and transpose the various suggestions / examples for my own needs to get a working solution.
Basically I currently have a stored procedure that takes a parsed individual order no. It then determines how many lines are on the order and for every row on the order, performs a repeat of the following: determine the despatched qty and for every unit perform a second repeat to enter a duplicate line into a temp table from which the labels are then printed. Ie If my order has 5 lines all of qty 10 depsatched, then I get 10 identical lines inserted into the temp table for every row on the order (50 lines total in temp table).
This has been working fine but as always the requirement now is to parse multiple order nos (as selected by a Reporting Services report) to the procedure. This causes me my headache!!
The current procedure is as below: Can someone come up with a working solution that I do not have to interpet & significantly change.
As stated the order nos to be parsed will be generated from a reporting services report where the user will select multiple order nos from a drop down list of order nos in the system.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
CREATE procedure [scheme].[label_print] @Order_no char(10) as
DECLARE @order_no char (10)
-- Assign @order_no the parsed variable @Order_no
set @order_no = @Order_no
-- Calculate how many lines on the specified [parsed] order no.
DECLARE @ORDERLINECOUNT as INT;
set @ORDERLINECOUNT = (select count(1) from scheme.OrderDetails where LTRIM(order_no) = @order_no)
-- create temp table to receive the items to print.
CREATE TABLE [label_print_tmp] (
[customer] [char] (8) COLLATE Latin1_General_BIN NOT NULL ,
[name] [char] (32) COLLATE Latin1_General_BIN NOT NULL ,
[order_no] [char] (10) COLLATE Latin1_General_BIN NOT NULL ,
[order_line_no] [char] (4) COLLATE Latin1_General_BIN NOT NULL ,
[warehouse] [char] (2) COLLATE Latin1_General_BIN NOT NULL ,
[product] [char] (20) COLLATE Latin1_General_BIN NOT NULL ,
[description] [char] (20) COLLATE Latin1_General_BIN NOT NULL ,
[unit_of_sale] [char] (10) COLLATE Latin1_General_BIN NOT NULL ,
[despatched_qty] [float] NULL
) ON [PRIMARY]
-- Repeat next steps for every row on order.
While @ORDERLINECOUNT > 0
-- get line qty as labels required on a single unit basis (ie qty 10 requires 10 labels printed)
DECLARE @LINEQTYCOUNT as INT;
set @LINEQTYCOUNT = (select despatched_qty from scheme.OrderDetails where order_no = @order_no and LTRIM(order_line_no) = @ORDERLINECOUNT)
While @LINEQTYCOUNT > 0
insert into scheme.label_print_tmp select
scheme.OrderDetails.warehouse, scheme.OrderDetails.product, scheme.OrderDetails.description,
FROM scheme.OrderHeader INNER JOIN
scheme.OrderDetails ON scheme.OrderHeader.order_no = scheme.OrderDetails.order_no INNER JOIN
scheme.Customer ON scheme.OrderHeader.customer = scheme.Customer.customer
where scheme.OrderDetails.order_no =@order_no and LTRIM(order_line_no) = @ORDERLINECOUNT
and scheme.OrderDetails.line_type != 'C'
set @LINEQTYCOUNT = @LINEQTYCOUNT -1
set @ORDERLINECOUNT=@ORDERLINECOUNT -1;
select * from scheme.label_print_tmp
drop table scheme.label_print_tmp