Using a stored procedure

[MarkWright]

I am trying to pass a ProdID to a stored procedure, but I get an error:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Procedure 'PriceBreak'
expects parameter '@ProdID', which was not supplied.

The error occurred in D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line
330
Called from D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line 317
Called from D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line 1
Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 297
Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 292
Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 1
Called from D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line 330
Called from D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line 317
Called from D:\Inetpub\mystore\storesql\quickstore_catalog.cfm : line 1
Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 297
Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 292
Called from D:\Inetpub\mystore\storesql\quickstore.cfm: line 1

328 : </cfquery>
329 :
330 : <cfstoredproc procedure="PriceBreak" datasource="store2">
331 :
332 : <cfprocresult name="PricingSQL" resultset ="1">

--------------------------------------------------------------------------------
----------------------------
Here's my code in ColdFusion:

<cfstoredproc procedure="PriceBreak" datasource="store2">

<cfprocresult name="PricingSQL" resultset ="1">

<cfprocparam type = "In"
cfsqltype = "CF_SQL_INTEGER"
dbvarname = "@ProdID"
value = "#Val(URL.ProductID)#"
null = "No">

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Price1
dbvarname = "@Price1"
scale = 2>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Price2
dbvarname = "@Price2"
scale = 2>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Price3
dbvarname = "@Price3"
scale = 2>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Break1a
dbvarname = "@Break1a"

>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Break1b
dbvarname = "@Break1b"

>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Break2a
dbvarname = "@Break2a"

>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Break2b
dbvarname = "@Break2b"

>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Break3a
dbvarname = "@Break3a"

>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Break3b
dbvarname = "@Break3b"

>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Break4a
dbvarname = "@Break4a"

>

--------------------------------------------------------------------------------
---------------------------------
Here is the stored procedure in MSSQL 2000:

CREATE PROCEDURE PriceBreak (@ProdID int,
@Price1 Numeric(12,2) OUTPUT,
@Price2 Numeric(12,2) OUTPUT,
@Price3 Numeric(12,2) OUTPUT,
@Break1a Numeric(4,0) OUTPUT,
@Break1b Numeric(4,0) OUTPUT,
@Break2a Numeric(4,0) OUTPUT,
@Break2b Numeric(4,0) OUTPUT,
@Break3a Numeric(4,0) OUTPUT,
@Break3b Numeric(4,0) OUTPUT,
@Break4a Numeric(4,0) OUTPUT)
AS SELECT ZProducts.ProductID, ZProducts.OurCost
, CASE WHEN [OurCost] =0 THEN 0
WHEN [OurCost]<=0.5 THEN [OurCost]*2.5
WHEN [OurCost]<=2.5 THEN [OurCost]*2.05
WHEN [OurCost]<=5 THEN [OurCost]*2.0
WHEN [OurCost]<=50 THEN [OurCost]*1.8
WHEN [OurCost]<=100 THEN [OurCost]*1.6
ELSE [OurCost]*1.5 END AS Price1
, CASE WHEN [OurCost] =0 THEN 0
WHEN [OurCost]<=0.5 THEN [OurCost]*2.2
WHEN [OurCost]<=2.5 THEN [OurCost]*2.05
WHEN [OurCost]<=5 THEN [OurCost]*2
WHEN [OurCost]<=50 THEN [OurCost]*1.7
WHEN [OurCost]<=100 THEN [OurCost]*1.5
ELSE [OurCost]*1.4 END AS Price2
, CASE WHEN [OurCost] =0 THEN 0
WHEN [OurCost]<=0.5 THEN [OurCost]*2.1
WHEN [OurCost]<=2.5 THEN [OurCost]*2
WHEN [OurCost]<=5 THEN [OurCost]*1.9
WHEN [OurCost]<=50 THEN [OurCost]*1.6
WHEN [OurCost]<=100 THEN [OurCost]*1.3
ELSE 0 END AS Price3
, 1 AS Break1a
, CASE WHEN [OurCost] =0 THEN 0
WHEN [OurCost]<=0.5 THEN 499
WHEN [OurCost]<=2.5 THEN 99
WHEN [OurCost]<=5 THEN 99
WHEN [OurCost]<=50 THEN 24
WHEN [OurCost]<=100 THEN 9
ELSE 3 END AS Break1b
, CASE WHEN [OurCost] =0 THEN 0
WHEN [OurCost]<=0.5 THEN 499
WHEN [OurCost]<=2.5 THEN 99
WHEN [OurCost]<=5 THEN 99
WHEN [OurCost]<=50 THEN 24
WHEN [OurCost]<=100 THEN 9
ELSE 3 END +1 AS Break2a
, CASE WHEN [OurCost] =0 THEN 0
WHEN [OurCost]<=0.5 THEN 999
WHEN [OurCost]<=2.5 THEN 499
WHEN [OurCost]<=5 THEN 499
WHEN [OurCost]<=50 THEN 49
WHEN [OurCost]<=100 THEN 25
ELSE 9 END AS Break2b
, CASE WHEN [OurCost] =0 THEN 0
WHEN [OurCost]<=0.5 THEN 999
WHEN [OurCost]<=2.5 THEN 499
WHEN [OurCost]<=5 THEN 499
WHEN [OurCost]<=50 THEN 49
WHEN [OurCost]<=100 THEN 25
ELSE 9 END +1 AS Break3a
, CASE WHEN [OurCost] =0 THEN 0
WHEN [OurCost]<=0.5 THEN 4999
WHEN [OurCost]<=2.5 THEN 999
WHEN [OurCost]<=5 THEN 999
WHEN [OurCost]<=50 THEN 99
WHEN [OurCost]<=100 THEN 49
ELSE 0 END AS Break3b
, CASE WHEN [OurCost] =0 THEN 0
WHEN [OurCost]<=0.5 THEN 5000
WHEN [OurCost]<=2.5 THEN 1000
WHEN [OurCost]<=5 THEN 1000
WHEN [OurCost]<=50 THEN 100
WHEN [OurCost]<=100 THEN 50
ELSE 0 END AS Break4a
FROM ZProducts
WHERE (ZProducts.ProductID = @ProdID)
GO

-------------------------------------------

I must be close, right? Why is this not working? I have it done already with
regular queries to the same datasource, but I'm trying to switch to stored
procedures for the performance boost.

I remain,

normalized

[philh]

Just grasping at straws here, but did you try putting cfprocresult after all of the params?

[MarkWright]

Your right, I did have it out of order, but that did not solve the problem. I
still get the same error. I must be missing something easy. This is the first
time I've tried to use a stored procedure instead of a query. If I figure this
out, I will eventually convert all my queries to SP's.

Let me know if you have any other ideas.

[The ScareCrow]

All I can see is you most probably should include the scale attribute for the
out params with a scale of zero, just incase it does not default to zero.

I also think I read somewhere that with cf 6.1 (I think) that you don't use
the "dbvarname" attribute, but the params have to be listed in the same order
as the sp.

Also, maybe silly, but check and ensure that Val(URL.ProductID) does infact
contain a value.

Ken

[MarkWright]

#VAL(ProductID)# definately has a value because it shows up in the URL. I use
the dbvarname, so I don't have to pass the values in order, but I will try
without it and let you know. I will also add the scale attribute if necessary.

Thanks for the help so far! Hope we figure this out.

[paross1]

FYI, with CF MX, dbvarname is no longer used.

[url]http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b16.htm#wp1102102[/url]

ColdFusion MX:

Changed the dbvarname attribute behavior: it is now ignored for all drivers.
ColdFusion MX uses JDBC 2.2 and does not support named parameters.

ColdFusion MX supports positional parameters only and you must code
cfprocparam tags in the same order as the associated parameters in the stored
procedure definition.[/b[

Phil

[MarkWright]

Okay, I took the dbvarname out, so now it looks like this:

--------------------------------------------------------------------------------
---------------------------
<cfstoredproc procedure="PriceBreak" datasource="store2">

<cfprocparam type = "In"
cfsqltype = "CF_SQL_INTEGER"
value = "#Val(URL.ProductID)#"
null = "No">

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Price1
scale = 2>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Price2
scale = 2>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Price3
scale = 2>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Break1a

>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Break1b

>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Break2a

>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Break2b

>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Break3a

>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Break3b

>

<cfprocparam type = "Out"
cfsqltype = "CF_SQL_NUMERIC"
variable = Break4a

>

<cfprocresult name="PricingSQL" resultset ="1">


--------------------------------------------------------------------------------
-------

I still get the same error. I'm only passing one variable into the stored
procedure, so I definately am not going out of order. Please help with some
more ideas! I just don't get it
:confused;

[MarkWright]

PS

I haven't changed my SP at all, so the order is the same as originally posted.

[The ScareCrow]

I assume that you are still getting an error ?

Just because the URL.ProductID is in the url does not mean it is being passed
to the sp.

Try the following:

1. cfoutput the URL.ProductID just before the sp call

2. Remove the Val function

3. Comment out the output vars from the cf code and the sp, just to ensure
these are not causing the problem

Ken

[MarkWright]

Before that last response, I changed my procedure call to a pass-thru query
just to make sure I was actually passing ProdID...and the value was passed, but
I have not tested the output variables yet. I will comment them out and do a
regular stored procedure call and see if that works. Problem is...If it works
by taking them out, then what? I need those values.

I don't want to get rid of the VAL function. I have it there because I don't
want somebody to be able to put a Delete query in the URL instead of the
ProdID. The VAL function only allows numbers to pass into my db. I just want
the extra level of security.

[MarkWright]

For some reason my quick reply didn't show up...so here's where I'm at.

I commented out the output parameters...still get same error

I removed the Val function...still get same error

I can output the ProdID before the procedure call and I can use a pass-thru
query to pass the value.

I'm starting to think my coding is not the problem (although it usually is).
Maybe there's a setting on the server that if you're logged on as MarkWright,
stored procedures don't work : )

Thanks for the help so far. Let's keep trying. We are just missing
something...but what?

[paross1]

This may seem like a stupid question, but have you included a closing
</cfstoredproc> tag? I don't see one on any of your posts, but you may have
just cut off the tag when you copied the example for posting. At any rate, it
should follow your cfprocresult tag.

Phil

[MarkWright]

OMG....no I didn't have the closing tag.

Now I get a different error...too many parameters. I'll have to look into this further and let you know.

[MarkWright]

It works! I had added an extra parameter during all this testing that I didn't
have in my procedure. That was an easy fix.

The original problem was solved when I added the closing tag (another easy
fix). The simple things are always the ones that are overlooked. You
see....there is no such thing as a stupid question.

I am a little annoyed that the ColdFusion MX Bible used dbvarname in the
examples when that is no longer used, but I don't think that ever was an issue
since I already had the parameters in the right order. I guess the book I have
was written before this feature was removed.

Thank you everone for sticking this one out!

I remain,

normalized