x

Need Help creating stored procedure passing parameters

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.
more ▼

asked May 04, 2012 at 10:14 PM in Default

tombiernacki gravatar image

tombiernacki
338 18 20 23

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Three parameters, all that are in the code, is not too many, so something else is going wrong here. I would suggest stepping back and rebuilding the query slowly to determine what has gone wrong. Just set it up with the parameter list to start with to ensure you have the syntax correct there. Without having your entire set up, I can't validate the procedure myself. The top looks ok, so I'd just break it down and add lines back in a couple at a time til you identify the issue.

By the way, passing dates as strings instead of as dates could lead to all sorts of performance troubles.
more ▼

answered May 05, 2012 at 11:23 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.9k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x408

asked: May 04, 2012 at 10:14 PM

Seen: 730 times

Last Updated: May 05, 2012 at 11:23 AM