[posted and mailed, please reply in news]

Tod Thames (tod.thames@nc.ngb.army.mil) writes:
> I currently have a sql statement that works great. I want to convert it
> to a stored procedure so I can generate results from a webpage. Below
> is the stored procedure that is working fine.
>
> select SUBSTRING(tblPersonnel.SSN_SM,6,9) AS L4,
> SIDPERS_PERS_UNIT_TBL.UNAME,
> SIDPERS_PERS_UNIT_TBL.ADDR_CITY, SIDPERS_PERS_UNIT_TBL.PR_NBR,
> [tblPersonnel].[ADDR_CITY] + ' ' + [tblPersonnel].[ZIP] AS HOR,
> SMOSC=(case [tblSTAP Info].[SMOS Considered]
> when "1" then "Yes"
> else "No"
> end),
> FIRSTSGTC =(case [tblSTAP Info].[1SG]
> when "1" then "Yes"
> else "No"
> end),
> CSMC=(case [tblSTAP Info].[CSM]
> when "1" then "Yes"
> else "No"
> end),
> tblPersonnel.*, [tblSTAP Info].*
> FROM SIDPERS_PERS_UNIT_TBL
> INNER JOIN (tblPersonnel INNER JOIN [tblSTAP Info] ON
> tblPersonnel.SSN_SM = [tblSTAP Info].SSN)
> ON SIDPERS_PERS_UNIT_TBL.UPC = tblPersonnel.UPC
> WHERE (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE ('AA__')) and
> (tblPersonnel.PAY_GR = 'E5')
> and (SUBSTRING (tblPersonnel.PMOS,1,3) IN ('71L', '75H'))
>
> and ([tblSTAP Info].TotalPoints >=
> (case tblPersonnel.PAY_GR
> when "E4" then 350
> when "E5" then 400
> when "E6" then 450
> when "E7" then 500
> when "E8" then 600
> else 0
> end))
> AND [tblSTAP Info].NotConsidered = 0
> ORDER BY tblPersonnel.PAY_GR DESC , [tblSTAP Info].TotalPoints DESC ,
> tblPersonnel.NAME_IND;
>
> I would like the 3 items under the where clause to recieve a variable
> from the website:
>
> (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE ('AA__'))
>
> (tblPersonnel.PAY_GR = 'E5')
>
> (SUBSTRING (tblPersonnel.PMOS,1,3) IN ('71L', '75H'))
>
>
>
> Everytime I try to make this a stored procedure and try to pass multiple
> values in the PMOS field, I get an error stating too many variables.
>
> If anyone can tell me what the Stored Procedure should look like AND
> what the ASP should look like to pass the variables, I would be much
> obliged.

The SP would look like this:

CREATE PROCEDURE TodTahems @rpt_seq_code_pattern varchar(25),
@pay_gr char(2),
@pmos text
select SUBSTRING(tblPersonnel.SSN_SM,6,9) AS L4,
...
ON SIDPERS_PERS_UNIT_TBL.UPC = tblPersonnel.UPC
JOIN iter_charlist_to_table(@pmos) AS pmos ON
SUBSTRING (tblPersonnel.PMOS,1,3) = pmos.str
WHERE (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE @rpt_seq_code) and
(tblPersonnel.PAY_GR = @paygr)
...

The function iter_charlist_to_table unpacks a comma-separated list
into a table. You find the code here:
http://www.sommarskog.se/arrays-in-s...ist-of-strings

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp