Hi Need help creating a stored procedure that passes parameters. I ran a trace on a report and collected the following data....
**select InternalID from Entities WITH (NOLOCK) where EntitySys in (712)
IF (SELECT OBJECT_ID('TempDB.dbo.#tmpRevTransRpt')) IS NOT NULL
BEGIN
DROP TABLE #tmpRevTransRpt
END
CREATE TABLE [dbo].[#tmpRevTransRpt] (
[InternalID] [varchar] (72) NULL ,
[DisplayName] [varchar] (72) NULL ,
[ResidentSys] [int] NULL ,
[PlanSys] [int] NULL ,
[ServiceSys] [int] NULL ,
[TranSys] [int] NOT NULL ,
[TranComponent] [int] NOT NULL,
[TranDate] [datetime] NULL ,
[EpisodeSys] [int] NULL ,
[BedSys] [int] NULL ,
[OrgBreak] [varchar] (20) NULL ,
[name1] [varchar] (20) NULL ,
[name2] [varchar] (20) NULL ,
[name3] [varchar] (20) NULL ,
[name4] [varchar] (20) NULL ,
[name5] [varchar] (20) NULL ,
[name6] [varchar] (20) NULL ,
[name7] [varchar] (20) NULL ,
[name8] [varchar] (20) NULL ,
[name9] [varchar] (20) NULL ,
[name10] [varchar] (20) NULL ,
[name11] [varchar] (20) NULL ,
[name12] [varchar] (20) NULL ,
[name13] [varchar] (20) NULL ,
[name14] [varchar] (20) NULL ,
[name15] [varchar] (20) NULL ,
[CertSys] [int] NULL ,
[RevAmt] [money] DEFAULT 0 ,
[ContractAdjAmt] [money] DEFAULT 0 ,
[ARAmt] [money] DEFAULT 0 ,
[CoinsARAmt] [money] DEFAULT 0 ,
[OtherAmt] [money] DEFAULT 0 ,
[Units] [money] DEFAULT 0 ,
[PayerTypeDesc] [varchar] (72) NULL ,
[ServiceID] [Varchar] (32) DEFAULT ' ' ,
[ServiceDesc] [varchar] (72) DEFAULT ' ' ,
[PayerType] [varchar] (8) NULL ,
[PlanID] [varchar] (32) NULL ,
[PlanDesc] [varchar] (72) NULL ,
[ServiceTypeDesc] [varchar] (72) DEFAULT ' ' ,
[AdmissionID] [varchar] (32) NULL ,
[CertBegin] [datetime] NULL ,
[CertEnd] [datetime] NULL
) ON [PRIMARY]
/*
SELECT Organizations.*, (SELECT COUNT(*) FROM OrganizationLevels (NOLOCK)
WHERE OrganizationLevels.OrgLevel >= Organizations.OrgLevel
AND OrganizationLevels.OrgSet = 1) AS LevelNbr
FROM Organizations (NOLOCK) WHERE Organizations.OrgLevel = 60
AND Organizations.OrgSet = 1 */
EXEC prcInsertRevTransReportData 'dbo.#tmpRevTransRpt','(712=Ent4)','1','','','''RO''','02/01/2012','05/03/2012','','''CR'',''CP''','04/01/2012','5/3/2012 23:59:59','4','',1
CREATE NONCLUSTERED INDEX [Idx_TranSysTranComponentPlanSys] ON [dbo].[#tmpRevTransRpt]([TranSys],[TranComponent],[PlanSys])
EXEC prcUpdRevTransReportServiceData '#tmpRevTransRpt', ''
EXEC prcUpdRevTransReportRevContraUnitAmountData '#tmpRevTransRpt','02/01/2012','05/03/2012','04/01/2012','5/3/2012 23:59:59','',1
EXEC prcUpdRevTransReportARCoinsOthAmountData '#tmpRevTransRpt','02/01/2012','05/03/2012','04/01/2012','5/3/2012 23:59:59','',1
EXEC prcUpdRevTransReportResidentData '#tmpRevTransRpt'
EXEC prcUpdRevTransReportPayerData '#tmpRevTransRpt'
EXEC prcUpdRevTransReportAdmissionData '#tmpRevTransRpt'
EXEC prcRTGetRevTransReportData '#tmpRevTransRpt','4',' Name15, Name14, Name13, Name12, Name11, Name10, Name9, Name8, Name7, Name6, Name5, Name4,ServiceID, year(trandate), month(trandate) '**
Now I am trying to create a stored procedure that will run the data above and pass certain parameters, here is what I have below;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CRC_RevTransactionRPT] @Ent4 int,@TranDate varchar(32), @EntryDate varchar(32)
WITH EXECUTE AS 'HMXAFO32'
AS
SET NOCOUNT ON;
-- exec [dbo].[CRC_RevTransactionRPT] 712,'05/03/2012','04/01/2012'
IF (SELECT OBJECT_ID('TempDB.dbo.#tmpRevTransRpt')) IS NOT NULL
BEGIN
DROP TABLE #tmpRevTransRpt
END
CREATE TABLE [dbo].[#tmpRevTransRpt] (
[InternalID] [varchar] (72) NULL ,
[DisplayName] [varchar] (72) NULL ,
[ResidentSys] [int] NULL ,
[PlanSys] [int] NULL ,
[ServiceSys] [int] NULL ,
[TranSys] [int] NOT NULL ,
[TranComponent] [int] NOT NULL,
[TranDate] [datetime] NULL ,
[EpisodeSys] [int] NULL ,
[BedSys] [int] NULL ,
[OrgBreak] [varchar] (20) NULL ,
[name1] [varchar] (20) NULL ,
[name2] [varchar] (20) NULL ,
[name3] [varchar] (20) NULL ,
[name4] [varchar] (20) NULL ,
[name5] [varchar] (20) NULL ,
[name6] [varchar] (20) NULL ,
[name7] [varchar] (20) NULL ,
[name8] [varchar] (20) NULL ,
[name9] [varchar] (20) NULL ,
[name10] [varchar] (20) NULL ,
[name11] [varchar] (20) NULL ,
[name12] [varchar] (20) NULL ,
[name13] [varchar] (20) NULL ,
[name14] [varchar] (20) NULL ,
[name15] [varchar] (20) NULL ,
[CertSys] [int] NULL ,
[RevAmt] [money] DEFAULT 0 ,
[ContractAdjAmt] [money] DEFAULT 0 ,
[ARAmt] [money] DEFAULT 0 ,
[CoinsARAmt] [money] DEFAULT 0 ,
[OtherAmt] [money] DEFAULT 0 ,
[Units] [money] DEFAULT 0 ,
[PayerTypeDesc] [varchar] (72) NULL ,
[ServiceID] [Varchar] (32) DEFAULT ' ' ,
[ServiceDesc] [varchar] (72) DEFAULT ' ' ,
[PayerType] [varchar] (8) NULL ,
[PlanID] [varchar] (32) NULL ,
[PlanDesc] [varchar] (72) NULL ,
[ServiceTypeDesc] [varchar] (72) DEFAULT ' ' ,
[AdmissionID] [varchar] (32) NULL ,
[CertBegin] [datetime] NULL ,
[CertEnd] [datetime] NULL
) ON [PRIMARY]
/*
SELECT Organizations.*, (SELECT COUNT(*) FROM OrganizationLevels (NOLOCK)
WHERE OrganizationLevels.OrgLevel >= Organizations.OrgLevel
AND OrganizationLevels.OrgSet = 1) AS LevelNbr
FROM Organizations (NOLOCK) WHERE Organizations.OrgLevel = 60
AND Organizations.OrgSet = 1 */
EXEC hmxafo_testcr..prcInsertRevTransReportData 'dbo.#tmpRevTransRpt','(@Ent4 = Ent4)','1','','','''RO''','02/01/2012',
'@TranDate','','''CR'',''CP''','@EntryDate','@EntryDate','4','',1
CREATE NONCLUSTERED INDEX [Idx_TranSysTranComponentPlanSys]
ON [dbo].[#tmpRevTransRpt]([TranSys],[TranComponent],[PlanSys])
EXEC hmxafo_testcr..prcUpdRevTransReportServiceData '#tmpRevTransRpt', ''
EXEC hmxafo_testcr..prcUpdRevTransReportRevContraUnitAmountData '#tmpRevTransRpt','02/01/2012','@TranDate','@EntryDate',
'@EntryDate ','',1
EXEC hmxafo_testcr..prcUpdRevTransReportARCoinsOthAmountData '#tmpRevTransRpt','02/01/2012','@TranDate','@EntryDate',
'@EntryDate ','',1
EXEC hmxafo_testcr..prcUpdRevTransReportResidentData '#tmpRevTransRpt'
EXEC hmxafo_testcr..prcUpdRevTransReportPayerData '#tmpRevTransRpt'
EXEC hmxafo_testcr..prcUpdRevTransReportAdmissionData '#tmpRevTransRpt'
EXEC hmxafo_testcr..prcRTGetRevTransReportData '#tmpRevTransRpt','4',' Name15, Name14, Name13, Name12, Name11, Name10,
Name9, Name8, Name7, Name6, Name5, Name4,ServiceID, year(trandate), month(trandate) '
I get the following error:
Msg 8144, Level 16, State 2, Procedure CRC_RevTransactionRPT, Line 0
Procedure or function CRC_RevTransactionRPT has too many arguments specified.
