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(tblPe rsonnel.SSN_SM, 6,9) AS L4,
SIDPERS_PERS_UN IT_TBL.UNAME,
SIDPERS_PERS_UN IT_TBL.ADDR_CIT Y, SIDPERS_PERS_UN IT_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_UN IT_TBL
INNER JOIN (tblPersonnel INNER JOIN [tblSTAP Info] ON
tblPersonnel.SS N_SM = [tblSTAP Info].SSN)
ON SIDPERS_PERS_UN IT_TBL.UPC = tblPersonnel.UP C
WHERE (SIDPERS_PERS_U NIT_TBL.RPT_SEQ _CODE LIKE ('AA__')) and
(tblPersonnel.P AY_GR = 'E5')
and (SUBSTRING (tblPersonnel.P MOS,1,3) IN ('71L', '75H'))

and ([tblSTAP Info].TotalPoints >=
(case tblPersonnel.PA Y_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.PA Y_GR DESC , [tblSTAP Info].TotalPoints DESC ,
tblPersonnel.NA ME_IND;

I would like the 3 items under the where clause to recieve a variable
from the website:

(SIDPERS_PERS_U NIT_TBL.RPT_SEQ _CODE LIKE ('AA__'))

(tblPersonnel.P AY_GR = 'E5')

(SUBSTRING (tblPersonnel.P MOS,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]

Re: Assistance with Stored Procedure

[posted and mailed, please reply in news]

Tod Thames (tod.thames@nc. ngb.army.mil) writes:[color=blue]
> 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(tblPe rsonnel.SSN_SM, 6,9) AS L4,
> SIDPERS_PERS_UN IT_TBL.UNAME,
> SIDPERS_PERS_UN IT_TBL.ADDR_CIT Y, SIDPERS_PERS_UN IT_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_UN IT_TBL
> INNER JOIN (tblPersonnel INNER JOIN [tblSTAP Info] ON
> tblPersonnel.SS N_SM = [tblSTAP Info].SSN)
> ON SIDPERS_PERS_UN IT_TBL.UPC = tblPersonnel.UP C
> WHERE (SIDPERS_PERS_U NIT_TBL.RPT_SEQ _CODE LIKE ('AA__')) and
> (tblPersonnel.P AY_GR = 'E5')
> and (SUBSTRING (tblPersonnel.P MOS,1,3) IN ('71L', '75H'))
>
> and ([tblSTAP Info].TotalPoints >=
> (case tblPersonnel.PA Y_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.PA Y_GR DESC , [tblSTAP Info].TotalPoints DESC ,
> tblPersonnel.NA ME_IND;
>
> I would like the 3 items under the where clause to recieve a variable
> from the website:
>
> (SIDPERS_PERS_U NIT_TBL.RPT_SEQ _CODE LIKE ('AA__'))
>
> (tblPersonnel.P AY_GR = 'E5')
>
> (SUBSTRING (tblPersonnel.P MOS,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.[/color]

The SP would look like this:

CREATE PROCEDURE TodTahems @rpt_seq_code_p attern varchar(25),
@pay_gr char(2),
@pmos text
select SUBSTRING(tblPe rsonnel.SSN_SM, 6,9) AS L4,
...
ON SIDPERS_PERS_UN IT_TBL.UPC = tblPersonnel.UP C
JOIN iter_charlist_t o_table(@pmos) AS pmos ON
SUBSTRING (tblPersonnel.P MOS,1,3) = pmos.str
WHERE (SIDPERS_PERS_U NIT_TBL.RPT_SEQ _CODE LIKE @rpt_seq_code) and
(tblPersonnel.P AY_GR = @paygr)
...

The function iter_charlist_t o_table unpacks a comma-separated list
into a table. You find the code here:

http://www.sommarskog.se/arrays-in-sql.html#iter-list-of-strings

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

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Tod Thames]

Re: Assistance with Stored Procedure

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_t est 'AAA_', 'E5', '71L, 75H'

doesn't return any values.

standinglist2_t est 'AAA_', 'E5', '71L'

returns several rows.

Here is the SP I created.

CREATE procedure standinglist2_t est
@rsc varchar(4),
@paygr varchar(3),
@mos varchar (5)
as
CREATE TABLE #strings (str nchar (20) NOT NULL)
EXEC charlist_to_tab le_sp @mos

select SUBSTRING(tblPe rsonnel.SSN_SM, 6,9) AS L4,
SIDPERS_PERS_UN IT_TBL.UNAME,
SIDPERS_PERS_UN IT_TBL.ADDR_CIT Y, SIDPERS_PERS_UN IT_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_UN IT_TBL INNER JOIN
tblPersonnel INNER JOIN
[tblSTAP Info] ON
tblPersonnel.SS N_SM = [tblSTAP Info].SSN
ON SIDPERS_PERS_UN IT_TBL.UPC = tblPersonnel.UP C
ON (SUBSTRING(tblP ersonnel.PMOS,1 ,3) = s.str)
WHERE (SIDPERS_PERS_U NIT_TBL.RPT_SEQ _CODE LIKE (@rsc)) and
(tblPersonnel.P AY_GR = @paygr)
and (SUBSTRING (tblPersonnel.P MOS,1,3) IN (@mos))

and ([tblSTAP Info].TotalPoints >=
(case tblPersonnel.PA Y_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.PA Y_GR DESC , [tblSTAP Info].TotalPoints DESC ,
tblPersonnel.NA ME_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]

Re: Assistance with Stored Procedure

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]

Re: Assistance with Stored Procedure

Tod Thames (tod.thames@nc. ngb.army.mil) writes:[color=blue]
> The information below is provided.
>
> standinglist2_t est 'AAA_', 'E5', '71L, 75H'
>
> doesn't return any values.[/color]

There is a very simple explanation:
[color=blue]
> CREATE procedure standinglist2_t est
> @rsc varchar(4),
> @paygr varchar(3),
> @mos varchar (5) <----------------[/color]

Change the declaration of @mos to varchar(8000) or to text, to avoid
truncation issues.
[color=blue]
> 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.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Tod Thames]

Re: Assistance with Stored Procedure

I tried changing this:
[color=blue]
> @mos varchar (5) <----------------[/color]

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_t est, 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]

Re: Assistance with Stored Procedure

Tod Thames (anonymous@devd ex.com) writes:[color=blue]
> I had the same problem. When one variable is sent, it works fine, but
> when several are sent, it returns no rows.[/color]

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

and (SUBSTRING (tblPersonnel.P MOS,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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Tod Thames]

Re: Assistance with Stored Procedure

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]

Re: Assistance with Stored Procedure

Tod Thames (anonymous@devd ex.com) writes:[color=blue]
> 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'.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp