Stored Procedure Variables

[jheaton]

I am running SQL Server 2000. I need a stored procedure that defaults to the current period when no period is entered. The following select statement works correctly...

select *
from journal
where entityid='AIRLIN' AND (PERIOD=CAST(DatePart(YYYY,GetDate())AS varchar) + '02')

...but when I try to incorporated it into a stored procedure,(below) it doesn't work. Can anyone see the mistake in my logic?

ALTER Procedure sp_viiGetCashFlowAccounts
@BuildingID char(6) = 'AIRLIN',
@StartDate char(6) = (CAST(DatePart(YYYY,GetDate())AS varchar) + '01'),
@EndDate char(6) = CAST(DatePart(YYYY,GetDate())AS varchar) + '12')
AS
SELECT PERIOD,ACCTNUM,AMT,BASIS,STATUS
FROM journal
WHERE entityid=@BuildingID AND (Period>=@StartDate AND Period <= @EndDate)

GO


Thank You

 

[zhavic]

As default value for parameters, you can't use expressions.
Try to do something like this:

ALTER Procedure sp_viiGetCashFlowAccounts
@BuildingID char(6) = 'AIRLIN',
@StartDate char(6) = NULL,
@EndDate char(6) = NULL
AS

/* set default value, if nothing was passed in */
IF @StartDate IS NULL
SET @StartDate = CAST(DatePart(YYYY,GetDate())AS varchar) + '01'

/* set default value, if nothing was passed in */
IF @StartDate IS NULL
SET @EndDate = CAST(DatePart(YYYY,GetDate())AS varchar) + '12'

SELECT PERIOD,ACCTNUM,AMT,BASIS,STATUS
FROM journal
WHERE entityid=@BuildingID AND (Period>=@StartDate AND Period <= @EndDate)

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[jheaton]

SQL server accepts that as valid statement, and when I run it with variables it works fine. However, when I run the statement without any variables, it returns no data.

 

[JamesLean]

What values are you passing in that work?
The default values would give you '200301' and '200312'. Have you tried explicitly passing those in?

--James

 

[jheaton]

When I explicitly pass the variables, like the following it displays 2294 records.

EXEC sp_viiGetCashFlowAccounts 'AIRLIN', '200301', '200312'

However, the procedure does not display any records when executed as below.

EXEC sp_viiGetCashFlowAccounts

Thanks,

Josh

 

[JamesLean]

Spot the bug! In zhavic's code, it tests for whether @startdate is null both times, which means @enddate never gets assigned. You should be tesing for @enddate being null the second time.

--James

 

[jheaton]

Ofcourse! I completely overlooked that! Thank you guys *SO* much for your help. It runs perfectly now.

--Josh