Using Replace Function with SQL Stored Procedure

[Cheryl73]

Hi:

I'm developing a web app using Dreamweaver MX (ASP, ADO) and SQL Server 2000 for the database. I have a stored procedure that has two parameters, one called @Supplier and one called @Agreement.

Here is the code for the stored procedure. Sorry about the length but what is does is check for combinations of @Supplier and @Agreement (each parameter having a possibility of 3 values - 1, 2, or 3

CREATE PROCEDURE dbo.procQryByOrg_3
@Supplier int = 3,
@Agreement int = 3

As

SET NOCOUNT ON


if (@Supplier=1 and (@Agreement=1 or @Agreement=2))
begin
SELECT sum(NRManpower) as manpower_total,Sum(NRCost+NRWorkyearCost) as nr_total,sum(RCost) as r_total, sum(DRCost) as dr_total
FROM qryAnnexSummation_View
INNER JOIN (qryOrg INNER JOIN QryCategories_View ON qryOrg.Org = QryCategories_View.Org) ON qryAnnexSummation_View.Categories = QryCategories_View.Categories
WHERE (qryAnnexSummation_View.Supplier = 'Bolling AFB') and (qryAnnexSummation_View.Agreement = @Agreement)


SELECT qryAnnexSummation_View.[ID #],
qryAnnexSummation_View.Agreement,
qryAnnexSummation_View.Supplier,
qryAnnexSummation_View.Receiver,
qryAnnexSummation_View.WorkyearCost,
qryAnnexSummation_View.Categories,
qryAnnexSummation_View.SubCategory,
qryAnnexSummation_View.NRManpower,
qryAnnexSummation_View.NRWorkyearCost,
qryAnnexSummation_View.RCost,
qryAnnexSummation_View.NRCost,
qryAnnexSummation_View.DRCost,
qryAnnexSummation_View.RCost_Sum,
qryAnnexSummation_View.NRCost_Sum,
qryAnnexSummation_View.Manpower_sum,
qryAnnexSummation_View.DRCost_Sum,
'OrgSC' = CASE WHEN qryAnnexSummation_View.SubCategory Like 'Manpower%' Or qryAnnexSummation_View.SubCategory Like 'Logis%' THEN 'XP'
WHEN qryAnnexSummation_View.SubCategory Like 'Comp%' THEN 'FM'
WHEN qryAnnexSummation_View.SubCategory Like 'Inspec%' Or qryAnnexSummation_View.SubCategory Like 'Cere%' Or qryAnnexSummation_View.SubCategory Like 'Equa%' Or qryAnnexSummation_View.SubCategory Like 'EEO%' THEN 'WING' ELSE qryOrg.Org END,
qryOrg.*
FROM qryAnnexSummation_View
INNER JOIN (qryOrg INNER JOIN QryCategories_View ON qryOrg.Org = QryCategories_View.Org) ON qryAnnexSummation_View.Categories = QryCategories_View.Categories
WHERE (qryAnnexSummation_View.Supplier = 'Bolling AFB') and (qryAnnexSummation_View.Agreement = @Agreement)
ORDER BY OrgSC, [ID #]
end

else
if (@Supplier=1 and @Agreement=3)
begin
SELECT sum(NRManpower) as manpower_total,Sum(NRCost+NRWorkyearCost) as nr_total,sum(RCost) as r_total, sum(DRCost) as dr_total
FROM qryAnnexSummation_View
INNER JOIN (qryOrg INNER JOIN QryCategories_View ON qryOrg.Org = QryCategories_View.Org) ON qryAnnexSummation_View.Categories = QryCategories_View.Categories
WHERE (qryAnnexSummation_View.Supplier = 'Bolling AFB')


SELECT qryAnnexSummation_View.[ID #],
qryAnnexSummation_View.Agreement,
qryAnnexSummation_View.Supplier,
qryAnnexSummation_View.Receiver,
qryAnnexSummation_View.WorkyearCost,
qryAnnexSummation_View.Categories,
qryAnnexSummation_View.SubCategory,
qryAnnexSummation_View.NRManpower,
qryAnnexSummation_View.NRWorkyearCost,
qryAnnexSummation_View.RCost,
qryAnnexSummation_View.NRCost,
qryAnnexSummation_View.DRCost,
qryAnnexSummation_View.RCost_Sum,
qryAnnexSummation_View.NRCost_Sum,
qryAnnexSummation_View.Manpower_sum,
qryAnnexSummation_View.DRCost_Sum,
'OrgSC' = CASE WHEN qryAnnexSummation_View.SubCategory Like 'Manpower%' Or qryAnnexSummation_View.SubCategory Like 'Logis%' THEN 'XP'
WHEN qryAnnexSummation_View.SubCategory Like 'Comp%' THEN 'FM'
WHEN qryAnnexSummation_View.SubCategory Like 'Inspec%' Or qryAnnexSummation_View.SubCategory Like 'Cere%' Or qryAnnexSummation_View.SubCategory Like 'Equa%' Or qryAnnexSummation_View.SubCategory Like 'EEO%' THEN 'WING' ELSE qryOrg.Org END,
qryOrg.*
FROM qryAnnexSummation_View
INNER JOIN (qryOrg INNER JOIN QryCategories_View ON qryOrg.Org = QryCategories_View.Org) ON qryAnnexSummation_View.Categories = QryCategories_View.Categories
WHERE (qryAnnexSummation_View.Supplier = 'Bolling AFB')
ORDER BY OrgSC, [ID #]
end
else
if (@Supplier=2 and (@Agreement=1 or @Agreement=2))
begin
SELECT sum(NRManpower) as manpower_total,Sum(NRCost+NRWorkyearCost) as nr_total,sum(RCost) as r_total, sum(DRCost) as dr_total
FROM qryAnnexSummation_View
INNER JOIN (qryOrg INNER JOIN QryCategories_View ON qryOrg.Org = QryCategories_View.Org) ON qryAnnexSummation_View.Categories = QryCategories_View.Categories
WHERE (qryAnnexSummation_View.Supplier Like '%SAF%') and (qryAnnexSummation_View.Agreement = @Agreement)

SELECT qryAnnexSummation_View.[ID #],
qryAnnexSummation_View.Agreement,
qryAnnexSummation_View.Supplier,
qryAnnexSummation_View.Receiver,
qryAnnexSummation_View.WorkyearCost,
qryAnnexSummation_View.Categories,
qryAnnexSummation_View.SubCategory,
qryAnnexSummation_View.NRManpower,
qryAnnexSummation_View.NRWorkyearCost,
qryAnnexSummation_View.RCost,
qryAnnexSummation_View.NRCost,
qryAnnexSummation_View.DRCost,
qryAnnexSummation_View.RCost_Sum,
qryAnnexSummation_View.NRCost_Sum,
qryAnnexSummation_View.Manpower_sum,
qryAnnexSummation_View.DRCost_Sum,
'OrgSC' = CASE WHEN qryAnnexSummation_View.SubCategory Like 'Manpower%' Or qryAnnexSummation_View.SubCategory Like 'Logis%' THEN 'XP'
WHEN qryAnnexSummation_View.SubCategory Like 'Comp%' THEN 'FM'
WHEN qryAnnexSummation_View.SubCategory Like 'Inspec%' Or qryAnnexSummation_View.SubCategory Like 'Cere%' Or qryAnnexSummation_View.SubCategory Like 'Equa%' Or qryAnnexSummation_View.SubCategory Like 'EEO%' THEN 'WING' ELSE qryOrg.Org END,
qryOrg.*
FROM qryAnnexSummation_View
INNER JOIN (qryOrg INNER JOIN QryCategories_View ON qryOrg.Org = QryCategories_View.Org) ON qryAnnexSummation_View.Categories = QryCategories_View.Categories
WHERE (qryAnnexSummation_View.Supplier Like '%SAF%') and (qryAnnexSummation_View.Agreement = @Agreement)
ORDER BY OrgSC, [ID #]
end

else
if (@Supplier=2 and @Agreement=3)
begin
SELECT sum(NRManpower) as manpower_total,Sum(NRCost+NRWorkyearCost) as nr_total,sum(RCost) as r_total, sum(DRCost) as dr_total
FROM qryAnnexSummation_View
INNER JOIN (qryOrg INNER JOIN QryCategories_View ON qryOrg.Org = QryCategories_View.Org) ON qryAnnexSummation_View.Categories = QryCategories_View.Categories
WHERE (qryAnnexSummation_View.Supplier Like '%SAF%')

SELECT qryAnnexSummation_View.[ID #],
qryAnnexSummation_View.Agreement,
qryAnnexSummation_View.Supplier,
qryAnnexSummation_View.Receiver,
qryAnnexSummation_View.WorkyearCost,
qryAnnexSummation_View.Categories,
qryAnnexSummation_View.SubCategory,
qryAnnexSummation_View.NRManpower,
qryAnnexSummation_View.NRWorkyearCost,
qryAnnexSummation_View.RCost,
qryAnnexSummation_View.NRCost,
qryAnnexSummation_View.DRCost,
qryAnnexSummation_View.RCost_Sum,
qryAnnexSummation_View.NRCost_Sum,
qryAnnexSummation_View.Manpower_sum,
qryAnnexSummation_View.DRCost_Sum,
'OrgSC' = CASE WHEN qryAnnexSummation_View.SubCategory Like 'Manpower%' Or qryAnnexSummation_View.SubCategory Like 'Logis%' THEN 'XP'
WHEN qryAnnexSummation_View.SubCategory Like 'Comp%' THEN 'FM'
WHEN qryAnnexSummation_View.SubCategory Like 'Inspec%' Or qryAnnexSummation_View.SubCategory Like 'Cere%' Or qryAnnexSummation_View.SubCategory Like 'Equa%' Or qryAnnexSummation_View.SubCategory Like 'EEO%' THEN 'WING' ELSE qryOrg.Org END,
qryOrg.*
FROM qryAnnexSummation_View
INNER JOIN (qryOrg INNER JOIN QryCategories_View ON qryOrg.Org = QryCategories_View.Org) ON qryAnnexSummation_View.Categories = QryCategories_View.Categories
WHERE (qryAnnexSummation_View.Supplier Like '%SAF%')
ORDER BY OrgSC, [ID #]
end
else
if (@Supplier=3 and (@Agreement=1 or @Agreement=2))
begin
SELECT sum(NRManpower) as manpower_total,Sum(NRCost+NRWorkyearCost) as nr_total,sum(RCost) as r_total, sum(DRCost) as dr_total
FROM qryAnnexSummation_View
INNER JOIN (qryOrg INNER JOIN QryCategories_View ON qryOrg.Org = QryCategories_View.Org) ON qryAnnexSummation_View.Categories = QryCategories_View.Categories
WHERE (qryAnnexSummation_View.Agreement = @Agreement)

SELECT qryAnnexSummation_View.[ID #],
qryAnnexSummation_View.Agreement,
qryAnnexSummation_View.Supplier,
qryAnnexSummation_View.Receiver,
qryAnnexSummation_View.WorkyearCost,
qryAnnexSummation_View.Categories,
qryAnnexSummation_View.SubCategory,
qryAnnexSummation_View.NRManpower,
qryAnnexSummation_View.NRWorkyearCost,
qryAnnexSummation_View.RCost,
qryAnnexSummation_View.NRCost,
qryAnnexSummation_View.DRCost,
qryAnnexSummation_View.RCost_Sum,
qryAnnexSummation_View.NRCost_Sum,
qryAnnexSummation_View.Manpower_sum,
qryAnnexSummation_View.DRCost_Sum,
'OrgSC' = CASE WHEN qryAnnexSummation_View.SubCategory Like 'Manpower%' Or qryAnnexSummation_View.SubCategory Like 'Logis%' THEN 'XP'
WHEN qryAnnexSummation_View.SubCategory Like 'Comp%' THEN 'FM'
WHEN qryAnnexSummation_View.SubCategory Like 'Inspec%' Or qryAnnexSummation_View.SubCategory Like 'Cere%' Or qryAnnexSummation_View.SubCategory Like 'Equa%' Or qryAnnexSummation_View.SubCategory Like 'EEO%' THEN 'WING' ELSE qryOrg.Org END,
qryOrg.*
FROM qryAnnexSummation_View
INNER JOIN (qryOrg INNER JOIN QryCategories_View ON qryOrg.Org = QryCategories_View.Org) ON qryAnnexSummation_View.Categories = QryCategories_View.Categories
WHERE (qryAnnexSummation_View.Agreement = @Agreement)
ORDER BY OrgSC, [ID #]
end
else
if (@Supplier=3 and @Agreement=3)
begin
SELECT sum(NRManpower) as manpower_total,Sum(NRCost+NRWorkyearCost) as nr_total,sum(RCost) as r_total, sum(DRCost) as dr_total
FROM qryAnnexSummation_View
INNER JOIN (qryOrg INNER JOIN QryCategories_View ON qryOrg.Org = QryCategories_View.Org) ON qryAnnexSummation_View.Categories = QryCategories_View.Categories

SELECT qryOrg.org as org2,sum(NRManpower) as manpower_total,Sum(NRCost+NRWorkyearCost) as nr_total,sum(RCost) as r_total, sum(DRCost) as dr_total
FROM qryAnnexSummation_View
INNER JOIN (qryOrg INNER JOIN QryCategories_View ON qryOrg.Org = QryCategories_View.Org) ON qryAnnexSummation_View.Categories = QryCategories_View.Categories
group by qryOrg.org
order by qryOrg.org

SELECT qryAnnexSummation_View.[ID #],
qryAnnexSummation_View.Agreement,
qryAnnexSummation_View.Supplier,
qryAnnexSummation_View.Receiver,
qryAnnexSummation_View.WorkyearCost,
qryAnnexSummation_View.Categories,
qryAnnexSummation_View.SubCategory,
qryAnnexSummation_View.NRManpower,
qryAnnexSummation_View.NRWorkyearCost,
qryAnnexSummation_View.RCost,
qryAnnexSummation_View.NRCost,
qryAnnexSummation_View.DRCost,
qryAnnexSummation_View.RCost_Sum,
qryAnnexSummation_View.NRCost_Sum,
qryAnnexSummation_View.Manpower_sum,
qryAnnexSummation_View.DRCost_Sum,
'OrgSC' = CASE WHEN qryAnnexSummation_View.SubCategory Like 'Manpower%' Or qryAnnexSummation_View.SubCategory Like 'Logis%' THEN 'XP'
WHEN qryAnnexSummation_View.SubCategory Like 'Comp%' THEN 'FM'
WHEN qryAnnexSummation_View.SubCategory Like 'Inspec%' Or qryAnnexSummation_View.SubCategory Like 'Cere%' Or qryAnnexSummation_View.SubCategory Like 'Equa%' Or qryAnnexSummation_View.SubCategory Like 'EEO%' THEN 'WING' ELSE qryOrg.Org END,
qryOrg.*
FROM qryAnnexSummation_View
INNER JOIN (qryOrg INNER JOIN QryCategories_View ON qryOrg.Org = QryCategories_View.Org) ON qryAnnexSummation_View.Categories = QryCategories_View.Categories
ORDER BY OrgSC, [ID #]
end


GO

------------------ End of the stored proc code----------

I have a .asp web page that calls the stored procedure using the following code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../../Connections/sar.asp" -->
<%
Dim PreOrgSC, PreID
Dim TotalManpower, TotalNRCost, TotalRCost,TotalDRCost

Dim rsOrgRpt
Dim rsOrgRpt_numRows
dim includes
dim excludes

'Initalize the variables for includes and excludes.
includes=""
excludes=""
if trim(request("txtInclude") & "")<>"" then
ars=split(trim(request("txtInclude") & ""),",")
includes=includes & " ([ID #] like '" & trim(ars(0)) & "' "
for i=1 to ubound(ars,1)
includes=includes & " or [ID #] like '" & trim(ars(i)) & "' "
next
includes=includes & ") "
end if
if trim(request("txtExclude") & "")<>"" then
ars=split(trim(request("txtExclude") & ""),",")
excludes=excludes & " ([ID #] not like '" & trim(ars(0)) & "' "
for i=1 to ubound(ars,1)
excludes=excludes & " and [ID #] not like '" & trim(ars(i)) & "' "
next
excludes=excludes & ") "
end if


Set rsOrgRpt = Server.CreateObject("ADODB.Recordset")
rsOrgRpt.ActiveConnection = MM_sar_STRING
rsOrgRpt.Source = "exec dbo.procQryByOrg_3 " & request("optWhere") & "," & request("optWhat") & ",'" & replace(includes,"'","''") & "','" & replace(excludes,"'","''") & "'"...

In the text boxes called txtInclude and txtExclude, users can type in values like 1% (e.g. They would want the result dataset to only return values where a field called [ID #] begins with the number 1). Users can add in multiple parameter values e.g. 1%, 3%.

How can I get the following code to work, using the replace function:

rsOrgRpt.Source = "exec dbo.procQryByOrg_3 " & request("optWhere") & "," & request("optWhat") & ",'" & replace(includes,"'","''") & "','" & replace(excludes,"'","''") & "'"


Without returning the this error:
Microsoft OLE DB Provider for SQL Server error '80040e14'

Procedure or function procQryByOrg_3 has too many arguments specified.

/SARTS/SARTSISSA/SARTS_Reports/CostByOrg.asp, line 43


When I supply only two of the argments using the following line of code it works just fine:

rsOrgRpt.Source = "exec dbo.procQryByOrg_3 " & request("optWhere") & "," & request("optWhat")

Is it possible for me to use the includes and excludes with the stored procedure? Do I need to add the extra two parameters to the stored procedure? If so, how might I do that?

Any help would be greatly appreciated.

Thanks very much,
Cheryl73

 

[ArtieChoke]

Do I need to add the extra two parameters to the stored procedure?

Short answer - yes. You will need to modify the sp to handle the extra parameters. Are you trying to use them as part of the where clause?



"I think we're all Bozos on this bus!" - Firesign Theatre

 

[Cheryl3D]

Yes ArtieChoke, I am trying to add them to the WHERE clause.

Any ideas as to the best syntax to use?

Thanks,

Cheryl

 

[ESquared]

oh my goodness...

Using aliases will make your code soooooooo much easier to read and scan.

SELECT S.Agreement
   FROM qryAnnexSummation_View S

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.