how to call this stored proc in coldfusion
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
