views:

60

answers:

2

im doing a gym software. i have a signin signout system.when members sign in this data is put in a table leaving the signout colomn empty.then when member sign out the signout cell is updated.up to here is fine. but if the member signed in and out twice i dont want the sigout time of the first time to be changed.i want only the null cell to be updated or where the signin time is max. i have the following stored procedure with no errors but when running nothin is happening to the signout cell!

ALTER proc [dbo].[updateSignOutMem]
@ID nvarchar(50), @signOut nvarchar(50),@date nvarchar(50)
as
update [DateTable]
set SignOut  = @signOut 

where ID = @ID AND [Date] = @date and SignOut = null ;
+3  A: 

Solution: Replace SignOut = null with SignOut IS NULL.

Why: Comparisons with NULL always return NULL, and a WHERE clause that evaluates to NULL is treated as FALSE. For example SELECT * FROM myTable WHERE NULL = NULL will return zero records rather than the whole table.

Explanation: A NULL value is an "unknown" value, so the result of comparing two unknown values is also unknown. A description of this three-valued logic (true, false, unknown) can be found on Wikipedia.


PS: SQL Server provides much more data types than just nvarchar(50). Use them! :-)

Heinzi
+1  A: 

Try writing

SignOut IS null
Li0liQ