Skip to main content
C
cfnew
Known Participant
May 8, 2009
Answered

need help to convert this to sored procedure and call in coldfusion

  • May 8, 2009
  • 1 reply
  • 620 views

Hi i am new to coldfusion and i haed some code with me and i need this to convert in to stored procedure and call back in coldfusion.Can anyone help me how to make this data in to stored procedure and calling in coldfusion.

Here is the my code

SET NOCOUNT ON

    DECLARE
    @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


    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
<cftransaction action="commit"/>
    -- 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

This topic has been closed for replies.
Correct answer JR__Bob__Dobbs-qSBHQ2

If I understand your SQL code correctly you are selecting some records from the table di_audit_corp_upc_ldr_tbl.  For each record in the results you insert into di_audit_corp_upc_tbl if a matching record does not already exist in di_audit_corp_upc_tbl.  If this summary is correct you might try rewritting your query to avoid using a cursor.


Something like the sample below might work ( I have not tested this code ).

INSERT 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 )
SELECT  '07',  cpt_dpt_cd, cpt_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
FROM di_audit_corp_upc_ldr_tbl AS A
WHERE  NOT EXISTS
            (
                SELECT *
                FROM di_audit_corp_upc_tbl AS B
                WHERE B.con_upc_no = A.con_upc_no
            ) -- check to see if record already exists in destination table
   
    AND
        (
            SELECT COUNT(*)
            FROM di_audit_corp_upc_ldr_tbl AS C
            WHERE C.con_upc_no = A.con_upc_no
        ) > 1  -- replaces HAVING clause in original query used to select for cursor


Note that I assume you are using Microsoft SQL Server.

For questions about improving SQL queries you might be better off posting this kind of question to a forum specific to the database server you are using.

1 reply

J
JR__Bob__Dobbs-qSBHQ2Correct answer
Inspiring
May 11, 2009

If I understand your SQL code correctly you are selecting some records from the table di_audit_corp_upc_ldr_tbl.  For each record in the results you insert into di_audit_corp_upc_tbl if a matching record does not already exist in di_audit_corp_upc_tbl.  If this summary is correct you might try rewritting your query to avoid using a cursor.


Something like the sample below might work ( I have not tested this code ).

INSERT 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 )
SELECT  '07',  cpt_dpt_cd, cpt_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
FROM di_audit_corp_upc_ldr_tbl AS A
WHERE  NOT EXISTS
            (
                SELECT *
                FROM di_audit_corp_upc_tbl AS B
                WHERE B.con_upc_no = A.con_upc_no
            ) -- check to see if record already exists in destination table
   
    AND
        (
            SELECT COUNT(*)
            FROM di_audit_corp_upc_ldr_tbl AS C
            WHERE C.con_upc_no = A.con_upc_no
        ) > 1  -- replaces HAVING clause in original query used to select for cursor


Note that I assume you are using Microsoft SQL Server.

For questions about improving SQL queries you might be better off posting this kind of question to a forum specific to the database server you are using.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices