Procedure won't accept Variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Procedure won't accept Variables

Author

Topic

bconner
Starting Member

48 Posts

Posted - 2011-01-05 : 10:58:18

Below is a procedure I created that accepts 2 variables


create procedure test

as

DECLARE @BillingArea varchar(150)
DECLARE @Date varchar (5)



SELECT [GRP]
      ,[BillingArea]
      ,[DIVISION]
      ,[REF_LOC]
      ,[ReportingCategory3]
      ,[INVOICE]
      ,[POST_DATE]
      ,[SVC_DATE]
      ,[LAG_DAYS]
      ,[TXN_CODE]
      ,[CLOSE_DATE]
  FROM [AdHoc].[dbo].[LAG_CALCUALTION_TO_GET_BACK_UP_DATA]
  
  WHERE [BillingArea]= @BillingArea AND
          [CLOSE_DATE]= @date

GROUP BY[GRP]
      ,[BillingArea]
      ,[DIVISION]
      ,[REF_LOC]
      ,[ReportingCategory3]
      ,[INVOICE]
      ,[POST_DATE]
      ,[SVC_DATE]
      ,[LAG_DAYS]
      ,[TXN_CODE]
      ,[CLOSE_DATE]

When I try to Execute the procedure like this

Execute test 'AMERIPATH NORTHEAST NON HOSPIT','DEC10'

I get the following error:
'Msg 8146, Level 16, State 2, Procedure test, Line 0
Procedure test has no parameters and arguments were supplied.'

Any help is greatly appreciated.

Brian

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-01-05 : 11:14:27

The way you are defining the variable is incorrect.

It should be:

Create procedure test
@BillingArea varchar(150),
@Date varchar (5)
As
Begin

SELECT [GRP]
,[BillingArea]
,[DIVISION]
,[REF_LOC]
,[ReportingCategory3]
,[INVOICE]
,[POST_DATE]
,[SVC_DATE]
,[LAG_DAYS]
,[TXN_CODE]
,[CLOSE_DATE]
FROM [AdHoc].[dbo].[LAG_CALCUALTION_TO_GET_BACK_UP_DATA]

WHERE [BillingArea]= @BillingArea AND
[CLOSE_DATE]= @date

GROUP BY[GRP]
,[BillingArea]
,[DIVISION]
,[REF_LOC]
,[ReportingCategory3]
,[INVOICE]
,[POST_DATE]
,[SVC_DATE]
,[LAG_DAYS]
,[TXN_CODE]
,[CLOSE_DATE]
End --Begin and End are optional but good to specify for easy reading.

Have a look at BOL

bconner
Starting Member

48 Posts

Posted - 2011-01-05 : 15:33:10

Thanks PK Bohra! It worked perfectly....

Brian

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-12 : 05:52:40

Better to use braces

Create procedure test
(
@BillingArea varchar(150),
@Date varchar (5)
)
As
Begin

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources