Using Replace Function with Stored Procedure with 4 Params

[Cheryl3D]

Hi:

I'm developing a web app using Dreamweaver MX (ASP, ADO) and SQL Server 2000 for the database. I have a stored procedure called 'procQryByOrg_3' that needs to have 4 parameters (@Supplier, @Agreement, @Includes and @Excludes).

Here is the code for the stored procedure. Sorry about the length, but what the code does is check for combinations of @Supplier and @Agreement (1,3) or (3,3). Both of these parameters has three possible values 1,2,0r 3. There are also two more parameters called @Includes and @Excludes. This two parameters are meant to either include or exclude the [ID #] value where the value begins with 1% or 3%, etc. As a result in the stored procedure, I used tried to use where [ID #] like 1% (for includes) or where [ID #] not like 1% (for excludes). I would like the last two parameters to be optional.

So when the procedure is executed then the command would be similar to the following in SQL Server Query Analyzer:

exec procQryByOrg_3 3,3,'1%','2%'

I'll paste the code for the stored procedure below. But when I run this my includes and excludes don't process correctly.

Also, based on the stored procedure below if I run the following:
exec procQryByOrg_3 1,3,'1%','2%'

The following error message appears in SQL Server:

Syntax error converting the nvarchar value '11th WING' to a column of data type int.

I'm not sure why it is generating this error.

----------------------------------------------
Code for Stored Procedure
ALTER PROCEDURE dbo.procQryByOrg_3
@Supplier int = 3,
@Agreement int = 3,
@Includes nvarchar(50),
@Excludes nvarchar(50)

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 = @Supplier) AND (qryAnnexSummation_View.Agreement = @Agreement)AND
([ID #] like @Includes) or ([ID #] not like @Excludes)


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 = '@Supplier') AND (qryAnnexSummation_View.Agreement = @Agreement)
AND ([ID #] like @Includes) or ([ID #] not like @Excludes)
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 = @Supplier)AND([ID #] like @Includes) or ([ID #] not like @Excludes)


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
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 Procedure code------

I have a ASP page that calls the stored procedure using the following 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

My question is how can I get the ASP page to run successfully with all 4 parameters with any error messages?

Any help would be greatly appreciated.

Thanks,
Cheryl

 

[DreXor]

set it up to run with all four all the time instead of trying to do optional additional args, supply all four, even if 3 of them are blank.

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[donutman]

As far as

Syntax error converting the nvarchar value '11th WING' to a column of data type int.

Because of the length of the SP, I think you'll have to "comment out" increasingly larger portions of the code until you can zero-in on the offending code. Do a little detective work on the SP.
I think there's a debugger in VB.NET to run SPs step-by-step, but I've never used it.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Mr3Putt]

Having default values for your parameters in a Stored Proc doesn't help you in ASP (VBScript). You've got to send ALL the params every time.

Sometimes that takes some goofing around. Like when there ISN'T a value. If you may or may not have a particular parameter, you may need to do something like the following:

if len(vbParam1) < 1 then
vbParam1 = "NULL"
else
vbParam1 = "'" & vbParam1 & "'"
end if
... repeat for values that are evaluated as STRINGS ...

if len(vbParam2) < 1 then
vbParam2 = "NULL"
end if
... repeat for values evaluated as NUMBERS...

Then execute your Proc (dear lord, I'm about to write a param for dynamic SQL... the horror)

dynSQL = "sp_myProcedure " & vbParam1 & ", " & vbParam2
Exec dynSQL

The above bout of screwing around makes sure that you have "ticks" around your string-values, and your NULL values get submitted correctly.

You may need to massage your Procs a bit to understand NULLs. Good luck with your project.

[red]Note:[/red] [gray]The above comments are the opinionated ravings of Mr3Putt. As such, Mr3Putt accepts no responsibility for damages, real or contrived, resulting from acceptance of his opinions as fact.[/gray]

 

[Mr3Putt]

donut... there's a step by step SP evaluator somewhere in the Query Analyzer for Office 2003 (or was it in SQL-2k?)

I also suppose I should show just a little bit of how the code would look to the SP.

  string vbParam1 empty
  number vbParam2   2

dynSQL would equal "sp_myProcedure NULL, 2"

  string vbParam1 'mr3putt: dork'
  number vbParam2   empty

dynSQL would equal "sp_myProcedure 'mr3putt: dork', NULL"

[red]Note:[/red] [gray]The above comments are the opinionated ravings of Mr3Putt. As such, Mr3Putt accepts no responsibility for damages, real or contrived, resulting from acceptance of his opinions as fact.[/gray]