Copy link to clipboard
Copied
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 procedure="defect_yield" datasource="#REQUEST.datasource#">
<cfprocparam type = "IN"
CFSQLType = CF_SQL_DATE
value = "#FORM.dateFrom#">
<cfprocparam type = "IN"
CFSQLType = CF_SQL_DATE
value = "#FORM.dateTo#">
</cfstoredproc>
Any ideas on how to make this work? I want convert most of queries to Stored Procedures.
Thanks in advance,
DJ Khalif
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 productDetail
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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> | ||||||||
|
SP:
USE
[productDetails]
GO
/****** Object: StoredProcedure [dbo].[defect_yield] Script Date: 09/24/2009 14:49:40 ******/
SET
ANSI_NULLSON
GO
SET
QUOTED_IDENTIFIERON
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
Copy link to clipboard
Copied
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 .....
Copy link to clipboard
Copied
cfsearching,
That did it. I added the following:
<cfstoredproc procedure="defect_yield" datasource="#REQUEST.datasource#">
<cfprocparam type = "IN"
CFSQLType = CF_SQL_DATE
value = "#FORM.dateFrom#" dbVarName = @DateFrom>
<cfprocparam type = "IN"
CFSQLType = CF_SQL_DATE
value = "#FORM.dateTo#" dbVarName = @DateTo>
<cfprocresult name = Defect>
</cfstoredproc>
...and it worked. I want to speed up my process time and portability of my application.
Thanks.
DJ Khalif
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
cfsearching,
Before I attempted this is CF, I ran in SSMS but I had static dates. It worked.
Thanks,
DJ Khalif