Assistance with Stored Procedure

[Tod Thames]

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.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

[posted and mailed, please reply in news]

Tod Thames (to********@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, so****@algonet.se

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

[Tod Thames]

I need a little more assistance. I did a copy and paste of the
"char_to_table_sp" to create the procedure in my DB. I followed the
examples in you email.

I have everything working to push the variables from the asp page to the
stored procedure. The pages work fine when I only put in one value,
however it doesn't work when I input more than one value.

The information below is provided.

standinglist2_test 'AAA_', 'E5', '71L, 75H'

doesn't return any values.

standinglist2_test 'AAA_', 'E5', '71L'

returns several rows.

Here is the SP I created.

CREATE procedure standinglist2_test
@rsc varchar(4),
@paygr varchar(3),
@mos varchar (5)
as
CREATE TABLE #strings (str nchar (20) NOT NULL)
EXEC charlist_to_table_sp @mos

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
#strings s INNER JOIN
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
ON (SUBSTRING(tblPersonnel.PMOS,1,3) = s.str)
WHERE (SIDPERS_PERS_UNIT_TBL.RPT_SEQ_CODE LIKE (@rsc)) and
(tblPersonnel.PAY_GR = @paygr)
and (SUBSTRING (tblPersonnel.PMOS,1,3) IN (@mos))

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;

Your help is really appreciated. If you need any other information to
assist, please let me know.

I am unable to access the website you reference in your first response
from my office. I had to wait until i got home to try it. Must be a
firewall issue.

Thanks again.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Tod Thames]

Further information below:

I am using SQL 7, so I went to the SQL Server 7 link on your site. I
used the List-of-string Procedure to try and make it work as opposed to
information below.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Tod Thames (to********@nc.ngb.army.mil) writes:

The information below is provided.

standinglist2_test 'AAA_', 'E5', '71L, 75H'

doesn't return any values.
There is a very simple explanation:
CREATE procedure standinglist2_test
@rsc varchar(4),
@paygr varchar(3),
@mos varchar (5) <----------------
Change the declaration of @mos to varchar(8000) or to text, to avoid
truncation issues.
I am unable to access the website you reference in your first response
from my office. I had to wait until i got home to try it. Must be a
firewall issue.

I registered the domain in the beginning of December, so it could be
slow propagation somewhere. You could also try with
http://www.algonet.se/~sommar, which is the same site, but a less
pretty URL.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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

[Tod Thames]

I tried changing this:

@mos varchar (5) <----------------

to

@mos varchar (8000)

I had the same problem. When one variable is sent, it works fine, but
when several are sent, it returns no rows.

So, I tried changing it to:

@mos text

and received this error:

Server: Msg 8114, Level 16, State 1, Line 1
Error converting data type text to ntext.
Server: Msg 306, Level 16, State 1, Procedure standinglist2_test, Line 9
The text, ntext, and image data types cannot be used in the WHERE,
HAVING, or ON clause, except with the LIKE or IS NULL predicates.
I think I am very close to getting this resolved. Does anyone else have
any ideas?

I tried the link you provided in your last post and still couldn't get
to the site. I think it must be the firewall here.

Tod Thames
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Tod Thames (an*******@devdex.com) writes:

I had the same problem. When one variable is sent, it works fine, but
when several are sent, it returns no rows.

I went back to the stored procedure, and there are more problems:

and (SUBSTRING (tblPersonnel.PMOS,1,3) IN (@mos))

You need to remove this condition.

If there are further problems, I would recommend that you do some
debugging on your own. First thing is to add a "SELECT * FROM #strings"
to see that the table is correct. Next is to remove condition, until
rows starts to pop up. That's probably a more effective way than asking
for help and wait for someone to come by in the newsgroups.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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

[Tod Thames]

Thanks so much for the assistance. It worked after I took that last
statement out of the SP. I actually tried some debugging, but I am not
very proficient at it. I did the "select * from #strings", but received
this message.

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#stings'.

I couldn't figure out how to get the results from a temporary table.
Since I couldn't get the results from the table that is populated, I
didn't really know where to go from there.

Anyway, it is working now and I thank you very much. That sp you wrote
amazes me.

Tod Thames
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Tod Thames (an*******@devdex.com) writes:

Thanks so much for the assistance. It worked after I took that last
statement out of the SP. I actually tried some debugging, but I am not
very proficient at it. I did the "select * from #strings", but received
this message.

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#stings'.

Judging from the error message, you mispelled the table name. But that
may of course been a type when you posted.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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