Stored procedure question. - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Stored procedure question.

Author

Topic

EOJRR1
Starting Member

4 Posts

Posted - 2003-04-02 : 11:13:18

I have a stored procedure that is verifying the existance of an account, if it is not found for the current fiscal year, I need to back off one year and look. If it is there then I need to return the value, otherwise I need to return nothing. I have this


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

ALTER  PROC VerifyAccountNumber
	@FiscalYear char(4),
	@AccountNumber char(10)
as
declare @FiscalYearMO Char(4)
declare @ORG_LONG_NAME Char(30)
set @FiscalYearMO = @FiscalYear - 1

SELECT @ORG_LONG_NAME = ORG_LONG_NAME
  FROM TFORGCTB
 WHERE FISC_YEAR = @FiscalYear
     AND ACCOUNT_NBR = @AccountNumber

IF @@ROWCOUNT = 0 BEGIN
SELECT @ORG_LONG_NAME = ORG_LONG_NAME
  FROM TFORGCTB
 WHERE FISC_YEAR = @FiscalYearMO
     AND ACCOUNT_NBR = @AccountNumber

SELECT @ORG_LONG_NAME AS ORG_LONG_NAME

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

The only problem with this is that it returns a NULL when the account is not valid. I was responding to the dataset that was returned having a row count of 0, but with the NULL it is not 0.

Any ideas on how to get a dataset back with a row count of 0 rather than a NULL?

TIA

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-02 : 11:54:40

How about

SELECT @ORG_LONG_NAME AS ORG_LONG_NAME
where @ORG_LONG_NAME is not null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

EOJRR1
Starting Member

4 Posts

Posted - 2003-04-02 : 11:56:57

works like a charm... thanks...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources