how to call this stored proc in coldfusion

Report · May 08, 2009

Hi i created stored proc and trying to call in coldfusion using <cfstoredproc> tag but i am getting error as

[Macromedia][SQLServer JDBC Driver][SQLServer]Procedure or Function 'getData' expects parameter '@cpt_dpt_cd', which was not supplied.

my stored proc is :

-- create a cursor for remaining dupes and for each odd row, insert rec
CREATE PROCEDURE dbo.getData

(

    @Deleted User_dpt_cd char(2)
    , @Deleted User_com_cd char(3)
    , @SuB_com_cd char(5)
    , @CoN_upc_no char(13)
    , @PID_lng_dsc_tx varchar(100)
    , @PID_sht_dsc_tx varchar(100)
    , @rev_by varchar(8)
    , @rev_dt datetime
)
WITH RECOMPILE
AS
SET NOCOUNT ON

DECLARE upc_cursor CURSOR FOR
SELECT *
FROM di_audit_corp_upc_ldr_tbl
WHERE con_upc_no IN (
    SELECT con_upc_no -- COUNT(*)
    FROM [dbo].[di_audit_corp_upc_ldr_tbl]
    GROUP BY con_upc_no
    HAVING COUNT(*) > 1
)
ORDER BY con_upc_no

OPEN upc_cursor

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM upc_cursor
INTO
    @Deleted User_dpt_cd
    , @Deleted User_com_cd
    , @SuB_com_cd
    , @CoN_upc_no
    , @PID_lng_dsc_tx
    , @PID_sht_dsc_tx
    , @rev_by
    , @rev_dt

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
    -- if not exists
    IF NOT EXISTS (
        SELECT con_upc_no
        FROM dbo.di_audit_corp_upc_tbl
        WHERE con_upc_no = @CoN_upc_no
    )
    BEGIN
    -- insert record
    PRINT 'Inserting UPC: ' + @CoN_upc_no

        INSERT INTO dbo.di_audit_corp_upc_tbl (
            rcp_dpt_cd
            , cpt_dpt_cd
            , cpt_com_cd
            , sub_com_cd
            , cas_upc_no
            , con_upc_no
            , con_upc_tx
            , pid_lng_dsc_tx
            , pid_sht_dsc_tx
            , aut_vld_cd
            , rev_by
            , rev_dt
        ) VALUES (
            '07'
            , @Deleted User_dpt_cd
            , @Deleted User_com_cd
            , @SuB_com_cd
            , '0000000000000'
            , @CoN_upc_no
            , @PID_lng_dsc_tx
            , @PID_lng_dsc_tx
            , @PID_sht_dsc_tx
            , 'N'
            , @rev_by
            , @rev_dt
        )
    END
    ELSE
    BEGIN
    -- skipping
    PRINT 'skipping UPC: ' + @CoN_upc_no
    END

    -- This is executed as long as the previous fetch succeeds.
    FETCH NEXT FROM upc_cursor
    INTO
        @Deleted User_dpt_cd
        , @Deleted User_com_cd
        , @SuB_com_cd
        , @CoN_upc_no
        , @PID_lng_dsc_tx
        , @PID_sht_dsc_tx
        , @rev_by
        , @rev_dt

END

CLOSE upc_cursor
DEALLOCATE upc_cursor
GO

Report · May 08, 2009

Yup that code shows an expected parameter @cpt_dpt_cd. It is the very first one in the list.

The trouble is the error message is "expects parameter @cpt_dpt_cd', which was not supplied".

It is the CFML <cfstoredproc...> tag that needs to be inspected not the SQL stored proceedure. The <cfstoredproce...> tag apparently does not have a <cfprocparam...> tag that corresponds the the @cpt_dpt_cd parameter expected by the stored procedure code. According to the error message you provided which is all the information I currently have to go on.

Report · May 08, 2009

Hi Lan Thanks for ur help and i need to know how to declare these in the <cfprocparam...> tag will u plzzz show me i am new to this stuff of coldfusion plzzzz help me Lan

Thanks,

Kiran.

Report · May 08, 2009

First of this is the third time you have called my "Lan" my name is "Ian" that is an I not an L

The first place to start would be a review of the documentation for the <cfprocparam...> tag.

http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_p-q_14.html#1102102

A quick guess would be that your first one would look something like:

<cfqueryparam value="AB" CFSQLType="CF_SQL_CHAR">

You will need one of these of each required IN parameter of the stored procedure. They will need to be in the same order they are declared in the procedure and the value and cfSQLType will need to match the data type of the parameter. The <cfqueryparam...> tags go inside the opening <cfstoredproc...> and closing </cfstoredproc> tags.

Report · May 08, 2009

Hi Ian sorry for that thanks for ur help

Report · May 08, 2009

kirannaga,

The stored procedure is a bit puzzling. First, it declares input variables that seem to be overridden by the cursor. Second, why use a cursor at all?