If query empty then = 1 - 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

Transact-SQL (2000)

If query empty then = 1

Author

Topic

Ats
Starting Member

32 Posts

Posted - 2009-06-01 : 10:34:32

I don't know how best to title my problem, basically I have a stored procedure which is an else if statement which checks to see if the current year has any data, if not use the previous year etc. Where it falls over if none of the years have any data. In which case I would want it to say = 1 so it has a number in there but I ma not sure how to do it. Am I supposed to use the return value.

ALTER procedure [dbo].[allowance]
(
@companyid int = null
)
as
IF (SELECT SUM(compliance.allowanceDistributed) FROM dbo.compliance WHERE Company_ID = @companyid AND year1 = (SELECT YEAR(GETDATE()))) > 0
Begin
SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM dbo.compliance
WHERE Company_ID = @companyid AND year1 = (SELECT YEAR(GETDATE()))
End
else
IF (SELECT SUM(compliance.allowanceDistributed) FROM dbo.compliance WHERE Company_ID = @companyid AND year1 = (SELECT YEAR(GETDATE()))-1) > 0
Begin
SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM dbo.compliance
WHERE Company_ID = @companyid AND year1 = (SELECT YEAR(GETDATE())-1)
End
IF (SELECT SUM(compliance.allowanceDistributed) FROM dbo.compliance WHERE Company_ID = @companyid AND year1 = (SELECT YEAR(GETDATE())-2)) > 0
Begin
SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM dbo.compliance
WHERE Company_ID = @companyid AND year1 = (SELECT YEAR(GETDATE())-2)
end
IF (SELECT SUM(compliance.allowanceDistributed) FROM dbo.compliance WHERE Company_ID = @companyid AND year1 = (SELECT YEAR(GETDATE())-2)) > 0
Begin
SELECT SUM(compliance.allowanceDistributed) AS allowanceDistributed
FROM dbo.compliance
WHEn allowancedistrbuted = 0 then = 1
WHERE Company_ID = @companyid AND year1 = (SELECT YEAR(GETDATE())-2)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-01 : 10:58:59

[code]ALTER PROCEDURE dbo.uspAllowance
(
@CompanyID INT = NULL
)
AS

SET NOCOUNT ON

DECLARE @allowanceDistributed INT

SELECT @allowanceDistributed = SUM(allowanceDistributed)
FROM dbo.Compliance
WHERE Company_ID = @CompanyID
AND Year1 >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
AND Year1 < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)

IF @allowanceDistributed IS NULL
SELECT @allowanceDistributed = SUM(allowanceDistributed)
FROM dbo.Compliance
WHERE Company_ID = @CompanyID
AND Year1 >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)
AND Year1 < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)

IF @allowanceDistributed IS NULL
SELECT @allowanceDistributed = SUM(allowanceDistributed)
FROM dbo.Compliance
WHERE Company_ID = @CompanyID
AND Year1 >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 2, 0)
AND Year1 < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 2, 0)

SELECT @allowanceDistributed
GO[/code]

E 12°55'05.63"
N 56°04'39.26"

yonabout
Posting Yak Master

112 Posts

Posted - 2009-06-01 : 11:00:45

How about sticking an extra else after you've tested all the other years?

so

else
begin
select 1 as allowanceDistributed
end

?

Cheers,

Yonabout

Ats
Starting Member

32 Posts

Posted - 2009-06-01 : 11:49:39

Thanks guys I took the easy option of adding else select 1 as allowanceDistributed, this worked fine and will do for now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources