Anyone? Store Procedures

Report · Sep 24, 2009

Good afternoon all,

I created a stored procedure in SQL from:

<cfquery name="rsDisposition" datasource="#REQUEST.datasource#">
SELECT tlkp_Defects.Defect_Title,
SUM(case WHEN tbl_Assembly_Holds.Disposition_ID = 1 then 1 else 0 end) FixedOnLine,
        SUM(case WHEN tbl_Assembly_Holds.Disposition_ID = 2 then 1 else 0 end) Repair,
        SUM(case WHEN tbl_Assembly_Holds.Disposition_ID = 3 then 1 else 0 end) Scrap

FROM
    tbl_Assembly_Holds
    join tlkp_Disposition on tbl_Assembly_Holds.Disposition_ID = tlkp_Disposition.Disposition_ID
    join tbl_Assembly_Hold_Defects on tbl_Assembly_Hold_Defects.TagNumber = tbl_Assembly_Holds.TagNumber
    join tlkp_Defects on tbl_Assembly_Hold_Defects.Defect_ID = tlkp_Defects.Defect_ID
WHERE
tbl_Assembly_Holds.Record_date Between '#FORM.dateFrom#' And '#FORM.dateTo#'
GROUP BY
    tlkp_Defects.Defect_Title
</cfquery>

I am trying to call it from CF8 but I am getting errors, here is my code:

</cfstoredproc>

Any ideas on how to make this work? I want convert most of queries to Stored Procedures.

Thanks in advance,

DJ Khalif

Report · Sep 24, 2009

What do you mean by "created a stored procedure in SQL form"?

If your first <cfquery....> block was supposed to be a stored procedure....

Every time I have ever seen stored procedure SQL code it started of with "CREATE OR REPLACE STORED PROCEDURE"....

But since you gave such a vague description of your problem with no indication what errors you are experiencing or how your code is not working... I am really guessing on what the issue might be.

Report · Sep 24, 2009

I created a stored procedure in SQL from:
....

I am trying to call it from CF8 but I am getting errors,

But what error are you receiving and what does the actual stored procedure sql look like?

Report · Sep 24, 2009

Ian,

I created the SP using the query in within the block. I verified in SQL 2005 by entering actual dates in '#FORM.dateFrom#' and ....

Below is the error:

Error Executing Database Query.

[Macromedia][SQLServer JDBC Driver][SQLServer]Procedure defect_yield has no parameters and arguments were supplied.

The error occurred in C:\inetpub\wwwroot\AtticaProducts\excelPages\dispositioncount.cfm: line 7

5 :       <cfprocparam type = "IN"
6 :    CFSQLType = CF_SQL_DATE
7 :       value = "#FORM.dateTo#" dbVarName = @param2>
8 : 
9 : </cfstoredproc>

SQLSTATE	HY000
SQL	{call defect_yield( (param 1) , (param 2) )}
VENDORERRORCODE	8146
DATASOURCE	atDat

SP:

USE

[productDetails]

GO

/****** Object: StoredProcedure [dbo].[defect_yield] Script Date: 09/24/2009 14:49:40 ******/

SET

ANSI_NULLS

ON

GO

SET

QUOTED_IDENTIFIER

ON

GO

ALTER

PROCEDURE [dbo].

[defect_yield]

AS

SELECT

productDetails.dbo.tlkp_Defects.Defect_Title,

SUM(case WHEN productDetails.dbo.tbl_Assembly_Holds.Disposition_ID = 1 then 1 else 0 end) FixedOnLine

,

SUM(case WHEN productDetails.dbo.tbl_Assembly_Holds.Disposition_ID = 2 then 1 else 0 end) Repair

,

SUM(case WHEN productDetails.dbo.tbl_Assembly_Holds.Disposition_ID = 3 then 1 else 0 end)

Scrap

FROM

productDetails

.dbo.

tbl_Assembly_Holds

join productDetails.dbo.tlkp_Disposition on productDetails.dbo.tbl_Assembly_Holds.Disposition_ID = productDetails.dbo.tlkp_Disposition.

Disposition_ID

join productDetails.dbo.tbl_Assembly_Hold_Defects on productDetails.dbo.tbl_Assembly_Hold_Defects.TagNumber = productDetails.dbo.tbl_Assembly_Holds.

TagNumber

join productDetails.dbo.tlkp_Defects on productDetails.dbo.tbl_Assembly_Hold_Defects.Defect_ID = productDetails.dbo.tlkp_Defects.

Defect_ID

WHERE

productDetails

.dbo.tbl_Assembly_Holds.Record_date Between 'productDetails.dbo.tbl_Assembly_Holds.Record_date' And

'productDetails.dbo.tbl_Assembly_Holds.Record_date'

GROUP

BY

productDetails

.dbo.tlkp_Defects.

Defect_Title

Report · Sep 24, 2009

Procedure defect_yield has no parameters and arguments were supplied.

ALTER PROCEDURE .[defect_yield]
(No parameters defined here)
AS
SELECT

As the error message says, you are passing int two arguments (#FORM.dateFrom# and #FORM.dateTo#), but your procedure does not expect any. If you want to pass variable dates into the procedure, you need to add those parameters to your procedure declaration

ALTER PROCEDURE .[defect_yield]

@DateFrom DATETIME,

@DateTo DATETIME

AS

SELECT

.....

WHERE productDetails
.dbo.tbl_Assembly_Holds.Record_date Between
'productDetails.dbo.tbl_Assembly_Holds.Record_date' And
'productDetails.dbo.tbl_Assembly_Holds.Record_date'
GROUP BY .....

Once you add the parameters, you can use those SQL variables within the procedure's query, just like in your original cfquery. More or less.

WHERE tbl_Assembly_Holds.Record_date Between @DateFrom AND @DateTo .....

Report · Sep 25, 2009

cfsearching,

That did it. I added the following:

...and it worked. I want to speed up my process time and portability of my application.

Thanks.

DJ Khalif

Report · Sep 25, 2009

djkhalif wrote:
<cfprocparam type = "IN"
CFSQLType = CF_SQL_DATE
value = "#FORM.dateFrom#" dbVarName = @DateFrom>

Do not use dbVarName, it is deprecated. You must use positional notation. ie The order of your cfprocparam statements must match the declaration of the variables in your stored procedure SQL or an error may occur.

CFSQLType = CF_SQL_DATE

Assuming the forum did not mangle your code, you may want to add some quotes there. Just to keep the code clean.

Report · Sep 24, 2009

Here's a tip...

Always test your stored procedure by trying to call it within the SQL development-environment, exactly as you intend for ColdFusion to call it. Make sure that it works as you intended, then address the now-separate issue of getting the ColdFusion call to work.

Report · Sep 25, 2009

TLC-IT wrote:
Always test your stored procedure by trying to call it within the SQL development-environment, exactly as you intend for ColdFusion to call it. Make sure that it works as you intended, then address the now-separate issue of getting the ColdFusion call to work.

Sound advice. There is no point adding CF to the mix if you are not even certain the procedure works as expected ... or at all.

Report · Sep 27, 2009

cfsearching,

Before I attempted this is CF, I ran in SSMS but I had static dates. It worked.

Thanks,

DJ Khalif

Adobe Community

Anyone? Store Procedures

1 Correct answer

Error Executing Database Query.