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![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-...list-of-strings
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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!|||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!|||Tod Thames (tod.thames@.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, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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!|||Tod Thames (anonymous@.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, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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!|||Tod Thames (anonymous@.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, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
No comments:
Post a Comment