Getting the sum of two values - 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

General SQL Server Forums

New to SQL Server Programming

Getting the sum of two values

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2009-03-26 : 11:16:48

I have two variables that hold values, I want to get the SUM of these two variables values and then depending on what it is do something else.
Here is the code I am using:

DECLARE @countMot int, @countRecords int, @status int
SET @countMot = 0
SET @countRecords = 0
SET @status = 0

IF SUM(@countRecords + @countMots) >2 SET @status = 1

The above does not SET @status = 1 even the conditions are correct, does anyone have any ideas where I am going wrong?

Thanks,
macca

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-26 : 11:20:15

IF @countRecords + @countMots > 2 SET @status = 1

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

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-26 : 11:20:56

SUM is an aggregate function. What you want is proably this,

DECLARE @countMot int, @countRecords int, @status int
SET @countMot = 0
SET @countRecords = 0
SET @status = 0

IF(@countRecords + @countMot) >2
SET @status = 1

macca
Posting Yak Master

146 Posts

Posted - 2009-03-27 : 10:41:57

Hi guys, the above works if @countrecords > 2 or if @countMot is > 2 but not if it is say I have 1 @countrecords and 2 @countMot. It doesn't seem to realize that the combined total is > 2 !!

Here is my entire stored procedure:

CREATE PROCEDURE sproc_CheckQuota

(
@typeID int,
@meetingID int,
@recordType int,
@checkquota int OUTPUT
)

AS

BEGIN

DECLARE @countMot int, @countRecords int, @status int
SET @countMot = 0
SET @countRecords = 0
SET @status = 0

IF @recordType = 1

SELECT @countRecords = COUNT(recordID)

FROM Record INNER JOIN @recordType ON Record.@recordType ID = @recordType.typeID

WHERE Record.meetingID = @meetingID AND Record.receivedFromID = typeID AND RecordType.typeName='Record'

ELSE
SELECT @countMot = COUNT(recordID)

FROM Record INNER JOIN RecordType ON Record.recordTypeID = recordType.typeID

WHERE Record.meetingID = @meetingID AND Record.receivedFromID = typeID AND RecordType.typeName='Mot'
END

IF (@countRecords + @countMot) >2
SET @status = 1

SELECT @checkquota = @status
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-27 : 11:01:07

IF coalesce(@countRecords, 0) + coalesce(@countMot, 0) > 2

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

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-27 : 11:04:59

DECLARE @countMot int, @countRecords int,@status int
SET @countMot = 0
SET @countRecords = 0
SET @status = 0

SELECT
@countMot = COUNT( CASE WHEN RecordType.typeName='Record' THEN recordID ELSE NULL END) ,
@countRecords = COUNT( CASE WHEN RecordType.typeName='Mot' THEN recordID ELSE NULL END)
FROM Record INNER JOIN @recordType ON Record.@recordType ID = @recordType.typeID
WHERE Record.meetingID = @meetingID AND Record.receivedFromID = typeID

IF (@countRecords + @countMot) >2
SET @status = 1

Select @countMot,@countRecords,@status

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-28 : 03:59:52

quote:
Originally posted by darkdusky

DECLARE @countMot int, @countRecords int,@status int
SET @countMot = 0
SET @countRecords = 0
SET @status = 0

SELECT
@countMot = COUNT( CASE WHEN RecordType.typeName='Record' THEN recordID ELSE NULL END) ,
@countRecords = COUNT( CASE WHEN RecordType.typeName='Mot' THEN recordID ELSE NULL END)
FROM Record INNER JOIN @recordType rt ON Record.@recordType ID = ~~@recordType~~rt.typeID
WHERE Record.meetingID = @meetingID AND Record.receivedFromID = typeID

IF (@countRecords + @countMot) >2
SET @status = 1

Select @countMot,@countRecords,@status

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources