How do I gather/pass multiple input parameters to SQL stored procedure?

There is no way to pass a CR multi-value parameter to a SQL stored procedure.  To pass multiple values, there are a few basic options:

 1) Use a single value string parameter and have the user enter the multiple values in one string (eg. "1,2,3").  Then the procedure would have to interpret the string parameter to get the individual values.

 2) If you are not using any subreports, you could make your current report a subreport, have the main report accept the multi-value parameter and then convert that to a string, and pass that string to a single-value string parameter in the subreport, which would then be passed along to the stored procedure.  As above, the stored procedure would then have to interpret the string to get the individual values.  The difference between this and option #1 is that with this option you're using a multi-value parameter in the report, rather than forcing the users to combine the values in one parameter (in a string like "1,2,3").  But a subreport can not contain a subreport, so this option is not available if your report needs any subreports.

 3) Actually use multiple parameters in your stored procedure, one for each possible parameter value.  Something like:

@Priority1 int,
@Priority2 int,
@Priority3 int,
@PartNum1 varchar(80),
@PartNum2 varchar(80),
@PartNum3 varchar(80),
@BldQty1 int,
@BldQty2 int,
@BldQty3 int

 Not pretty, but it is, technically, an option.


 If you have more than one parameter that allows multiple values and you have to associate the values from each parameter, so that the first value in parameter A is associated with the first value in parameter B, and so on, that complicates things a bit further, but if it's a simple one-to-one relationship and you assume that the values are always entered in the same relative order (so that you're associating value 1 with value 1, value 2 with value 2, etc.), then I don't see that as a big problem.

 James

10 of each parameter seems like an awful lot of parameters to me.

 As for changing the procedure, I'm not sure, because I'm not sure that I fully understand what it's doing, but here are my thoughts.

 First of all, since you'll now have a bunch of "optional" parameters, you'll have to decide how to handle that.  You'll presumably want to set some default value for the parameters in the report.  It will either need to be some value that will never return any results, or a value that you specifically check for in the procedure.  @PartNum seems to be the driving force, so that may be the parameter that you need to focus on (eg. have the default for that be a value that will never be found in part.partnum).

 For @Priority and @PartNum, it looks like you basically need to repeat what lines 22 - 27 do for each pair of parameter values.  The most straightforward approach may be to simply repeat those lines for each pair of parameters.  Something like the following.  The only difference in each SELECT is which @PriorityX and @PartNumX parameters it uses.

; WITH BOM AS (
SELECT     0 AS LEVEL, 000 AS seq, part.partnum AS parent, revisionnum AS P_revisionnum, part.partnum,
                          revisionnum AS C_revisionnum, partdescription, CONVERT(decimal(18, 8), 0) AS qty, ium, typecode,
                          CONVERT(varchar(800), (CONVERT(varchar(2), @Priority1) + part.partnum)) AS path
               FROM         part INNER JOIN
                                 NC_PARTREV2 ON part.partnum = NC_PARTREV2.partnum
               WHERE     part.partnum = @PartNum1
                                    UNION ALL
SELECT     0 AS LEVEL, 000 AS seq, part.partnum AS parent, revisionnum AS P_revisionnum, part.partnum,
                          revisionnum AS C_revisionnum, partdescription, CONVERT(decimal(18, 8), 0) AS qty, ium, typecode,
                          CONVERT(varchar(800), (CONVERT(varchar(2), @Priority2) + part.partnum)) AS path
               FROM         part INNER JOIN
                                 NC_PARTREV2 ON part.partnum = NC_PARTREV2.partnum
               WHERE     part.partnum = @PartNum2
                                    UNION ALL
SELECT     0 AS LEVEL, 000 AS seq, part.partnum AS parent, revisionnum AS P_revisionnum, part.partnum,
                          revisionnum AS C_revisionnum, partdescription, CONVERT(decimal(18, 8), 0) AS qty, ium, typecode,
                          CONVERT(varchar(800), (CONVERT(varchar(2), @Priority3) + part.partnum)) AS path
               FROM         part INNER JOIN
                                 NC_PARTREV2 ON part.partnum = NC_PARTREV2.partnum
               WHERE     part.partnum = @PartNum3
                                    UNION ALL

 and so on for parameter pairs 4, 5, 6, etc.


 In theory, I think you could do essentially the same thing using CASE, but there is one difference.  If the same value could be used in more than one @PartNumX parameter (eg. @PartNum1 and @PartNum5 had the same value), the approach above would repeat that part once for each matching parameter, using a different @PriorityX parameter value each time (eg. @Priority1 the first time and @Priority5 the second time).  OTOH, the approach below would just include that part once, using the @PriorityX value that corresponded to the first matching @PartNumX (@Priority1 in the above example).

 Using CASE there would just be one SELECT:

; WITH BOM AS (
SELECT     0 AS LEVEL, 000 AS seq, part.partnum AS parent, revisionnum AS P_revisionnum, part.partnum,
                          revisionnum AS C_revisionnum, partdescription, CONVERT(decimal(18, 8), 0) AS qty, ium, typecode,
                          CONVERT(varchar(800),
 (CONVERT(varchar(2),
 CASE part.partnum
  WHEN @PartNum1 THEN @Priority1
  WHEN @PartNum2 THEN @Priority2
  WHEN @PartNum3 THEN @Priority3

   and so on for parameter pairs 4, 5, 6, etc.

 END -- End of the CASE statement
) + part.partnum)) AS path
               FROM         part INNER JOIN
                                 NC_PARTREV2 ON part.partnum = NC_PARTREV2.partnum
               WHERE     part.partnum IN (@PartNum1, @PartNum2, @PartNum3,
 and so on for the remaining @PartNumX parameters )
                                    UNION ALL


 As for @BldQty, you could theoretically use a similar CASE statement there (with the same caveat about two or more @PartNumX parameters having the same value).  Change line 43 to:

(CASE bom.partnum
  WHEN @PartNum1 THEN @BldQty1
  WHEN @PartNum2 THEN @BldQty2
  WHEN @PartNum3 THEN @BldQty3

   and so on for parameter pairs 4, 5, 6, etc.

 END -- End of the CASE statement
 * qty) AS QtyNeeded,
                ium, typecode, path, warehousecode, onhandqty,


 Like I said, I'm not sure that I fully understand what your procedure is doing, so some, or all, of this may be wrong, but it _seems_ reasonable.

 James

James, your solution got me thinking, and I ended up doing it a different way.  Since you were the catalyst for this thought process, I'm definitely giving you the points.  Thanks!

ALTER PROCEDURE [dbo].[NC_SHORTAGES] 
(@Priority1 AS int = NULL,
 @Priority2 AS int = NULL,
 @Priority3 AS int = NULL,
 @Priority4 AS int = NULL,
 @Priority5 AS int = NULL,
 @PartNum1 AS varchar(80) = NULL,
 @PartNum2 AS varchar(80) = NULL,
 @PartNum3 AS varchar(80) = NULL,
 @PartNum4 AS varchar(80) = NULL,
 @PartNum5 AS varchar(80) = NULL,
 @BldQty1 AS int = NULL,
 @BldQty2 AS int = NULL,
 @BldQty3 AS int = NULL,
 @BldQty4 AS int = NULL,
 @BldQty5 AS int = NULL)
 
    AS 
    BEGIN    
	  SET ANSI_NULLS ON;
        If IsNull(object_id('tempdb..##partqty'),0) <> 0 DROP TABLE ##partqty;
        
        CREATE table ##partqty (line_no integer, level integer, seq integer,
                                 parpart char(30), parrev char(12), 
                                 chpart char(30), chrev char(12), 
                                 pdesc char(50), reqqty integer, uom char(4),
                                 typecd char(1), path char(40),
                                 whse char(7), ohqty integer, 
                                 allocqty integer, nonnetqty integer, 
                                 avqty integer, onordr integer);
 
        DECLARE @test int,
                @UsePrio int,
                @UsePart varchar(80),
                @UseQty int;
                    
        SET @test = 1;
        WHILE @test < 6
        BEGIN    
  		  IF (@test = 1 and (@Priority1 IS NULL OR
		                     @PartNum1  IS NULL OR
		                     @BldQty1   IS NULL)) OR
             (@test = 2 and (@Priority2 IS NULL OR
		                     @PartNum2  IS NULL OR
		                     @BldQty2   IS NULL)) OR
		     (@test = 3 and (@Priority3 IS NULL OR
		                     @PartNum3  IS NULL OR
		                     @BldQty3   IS NULL)) OR
		     (@test = 4 and (@Priority4 IS NULL OR
		                     @PartNum4  IS NULL OR
		                     @BldQty4   IS NULL)) OR
		     (@test = 5 and (@Priority5 IS NULL OR
		                     @PartNum5  IS NULL OR
		                     @BldQty5   IS NULL))                                                          		                     
		  BEGIN
		    SET @test = @test + 1;
		    CONTINUE;
		  END;  
		  ELSE 
		  BEGIN
 
		    SET @UsePrio = CASE @test 
		                        WHEN 1 THEN @Priority1
		                        WHEN 2 THEN @Priority2
		                        WHEN 3 THEN @Priority3
		                        WHEN 4 THEN @Priority4
		                        WHEN 5 THEN @Priority5
		                   END;
 
		    SET @UsePart = CASE @test 
		                        WHEN 1 THEN @PartNum1
		                        WHEN 2 THEN @PartNum2
		                        WHEN 3 THEN @PartNum3
		                        WHEN 4 THEN @PartNum4
		                        WHEN 5 THEN @PartNum5
		                   END;
 
		    SET @UseQty  = CASE @test 
		                        WHEN 1 THEN @BldQty1
		                        WHEN 2 THEN @BldQty2
		                        WHEN 3 THEN @BldQty3
		                        WHEN 4 THEN @BldQty4
		                        WHEN 5 THEN @BldQty5
		                   END;
		    SET @test = @test + 1;
		  END;
		
		  SET NOCOUNT ON;
 
     
; WITH BOM AS (SELECT     0 AS LEVEL, 000 AS seq, part.partnum AS parent, revisionnum AS P_revisionnum, 
                          part.partnum, revisionnum AS C_revisionnum, partdescription, CONVERT(decimal(18, 8), 0) AS qty, 
                          ium, typecode, CONVERT(varchar(800), CONVERT(varchar(800), @UsePrio) + part.partnum) AS path
               FROM         part INNER JOIN
                                 NC_PARTREV2 ON part.partnum = NC_PARTREV2.partnum
               WHERE     part.partnum = @UsePart 
                                    UNION ALL
               SELECT     B.LEVEL + 1, CONVERT(int, M.mtlseq), M.partnum, R.revisionnum, M.mtlpartnum, CR.revisionnum, 
                          P.partdescription, CONVERT(decimal(18, 8), M.qtyper), P.IUM, P.typecode, 
                          (CONVERT(varchar(800), b.path + ',' + CONVERT(varchar(800), M.mtlseq)))
               FROM         BOM B INNER JOIN
                                  PARTMTL M ON M.partnum = B.partnum INNER JOIN
                                  NC_PARTREV2 R ON R.partnum = B.partnum AND M.revisionnum = R.revisionnum INNER JOIN
                                  PART P ON P.partnum = M.mtlpartnum INNER JOIN
                                  NC_PARTREV2 CR ON CR.partnum = P.partnum)
 
                         
   INSERT INTO ##partqty
     (level, seq, parpart, parrev, chpart, chrev, pdesc, reqqty, uom, typecd, path, whse, ohqty, 
             allocqty, nonnetqty, avqty)
     SELECT     cast(LEVEL as int), cast(seq as int), cast(parent as char), 
                cast(P_revisionnum as char), cast(bom.partnum as char), cast(C_revisionnum as char), 
                cast(partdescription as char), cast((@UseQty * qty) as int), 
                cast(ium as char), cast(typecode as char), cast(path as char),
                cast(warehousecode as char), cast(onhandqty as int), cast(allocqty as int), 
                cast(nonnettableqty as int), cast((onhandqty + nonnettableqty - allocqty) as int)
     FROM       BOM INNER JOIN
                partwhse ON bom.partnum = partwhse.partnum
     ORDER BY path;
     
     END; --WHILE 
   		    	
     UPDATE tblPOOLS p WITH (READPAST)
     SET LOCKED = 'TRUE'
     WHERE p.somepoolsfield IN  (SELECT TOP (5) t.somefield FROM tblTABLE t WHERE t.STAGECODE = 'AT' ORDER BY STAGEDDATE)
 
     select line_no = @counter + 1 = @counter, level, seq, parpart, parrev, chpart, chrev, pdesc, reqqty, uom, typecd, path, whse, ohqty, 
             allocqty, nonnetqty, avqty from ##partqty order by path;
	END
	RETURN
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130: