CODEWITHSUNDEEP
sqlsql-server-2005if-statement
atrljoe
atrljoe

Reputation: 8151

SQL If Statement Not Working Correctly

My if statement never seems to do anything. I am inputting @active from an ASP page, and @cactive is set through the selecting of CActive from the Cots Table. Do you guys see anything wrong with this statement? Why doesn't it ever execute? Im using SQL Server 2005

//Declare Variables Here (about 20)

AS
If @currentdate is null
SET @currentdate = GETDATE()

Update Cots
SET //UPDATE ALL FIELDS HERE
Where CoID = @CID

SET @cactive =
(
Select CActive
From Cots
Where CoID = @CID
)

If @cactive != @active
Begin
INSERT INTO Activity
        (CoID,ActivityDate, ActivityName, ActivityNote, ActivityMediaContact, ActivityOwner)
        Values (@CID, @currentdate, 'Co ' + @fname + ' ' + @lname + 'made inactive','Co made inactive on ' + CAST(@currentdate AS varchar(50)), 'User', 'User')
End

INSERT INTO Activity
        (CoID,ActivityDate, ActivityName, ActivityNote, ActivityMediaContact, ActivityOwner)
        Values (@CID, @currentdate, 'Updated ' + @fname + ' ' + @lname,'Updated on ' + CAST(@currentdate AS varchar(50)), 'User', 'User')

Upvotes: 1

Views: 1459

Answers (2)

SQLMenace
SQLMenace

Reputation: 134941

If either of these is null, they will never be equal or not equal

If @cactive != @active

Example

DECLARE @i int, @i2 int
IF @i <> @i2
PRINT 'not equal'

IF @i = @i2
PRINT 'equal'

Check for NULLS also by using ISNULL()/COALESCE() or IS NULL/IS NOT NULL

Upvotes: 3

tripdubroot
tripdubroot

Reputation: 1153

Using IS NULL is best for checking a condition, like in a WHERE clause.

You should use the ISNULL() function...

USE AdventureWorks2008R2;
GO
SELECT AVG(ISNULL(Weight, 50))
FROM Production.Product;
GO

Upvotes: 1

Related Questions