Skip to main content
Known Participant
May 8, 2009
Question

how to call this stored proc in coldfusion

  • May 8, 2009
  • 1 reply
  • 1282 views

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

(

    @7206744_dpt_cd char(2)
    , @7206744_com_cd char(3)
    , @11128747_com_cd char(5)
    , @8327888_upc_no char(13)
    , @4197952_lng_dsc_tx varchar(100)
    , @4197952_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
    @7206744_dpt_cd
    , @7206744_com_cd
    , @11128747_com_cd
    , @8327888_upc_no
    , @4197952_lng_dsc_tx
    , @4197952_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 = @8327888_upc_no
    )
    BEGIN
    -- insert record
    PRINT 'Inserting UPC: ' + @8327888_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'
            , @7206744_dpt_cd
            , @7206744_com_cd
            , @11128747_com_cd
            , '0000000000000'
            , @8327888_upc_no
            , @4197952_lng_dsc_tx
            , @4197952_lng_dsc_tx
            , @4197952_sht_dsc_tx
            , 'N'
            , @rev_by
            , @rev_dt
        )
    END
    ELSE
    BEGIN
    -- skipping
    PRINT 'skipping UPC: ' + @8327888_upc_no
    END

    -- This is executed as long as the previous fetch succeeds.
    FETCH NEXT FROM upc_cursor
    INTO
        @7206744_dpt_cd
        , @7206744_com_cd
        , @11128747_com_cd
        , @8327888_upc_no
        , @4197952_lng_dsc_tx
        , @4197952_sht_dsc_tx
        , @rev_by
        , @rev_dt

END

CLOSE upc_cursor
DEALLOCATE upc_cursor
GO

This topic has been closed for replies.

1 reply

ilssac
Inspiring
May 8, 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.

cfnewAuthor
Known Participant
May 8, 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.

ilssac
Inspiring
May 8, 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.