Mark
I think Jon is looking for CASE expression
UPDATE Table
SET col =CASE WHEN ID = @ID THEN col ELSE other value END
WHERE ........


[quoted text, click to view]

SQL Server doesn't have a Boolean datatype. I'll take a guess that your
column is a BIT or other numeric type that contains only the values 1 and 0.
It seems that you want an UPDATE statement rather than SELECT, assuming you
actually want to change the value rather than return it.

UPDATE table
SET col = 1 - col
WHERE id = @id

--
David Portas
SQL Server MVP
--

I'm a novice to SQL programming and both solutions are confusing to me.
Please be patient.

I can scan an ID card and retrieve a KEY value. I want to pass this KEY
value from my web page to a stored procedure in my database.

In QueryAnalyzer the following does what I want the sproc to do:

UPDATE myTable
SET myFieldBitValue = ~ myFieldBitValue
WHERE UniqueID = thePassedUniqueIDValue

Jon


[quoted text, click to view]

[quoted text, click to view]

Hi Jon,

If this works and you want to encapsulate it in a stored procedure, use
the following code:

CREATE PROC myProc
@UniqueID int -- Change this to datatype of UniqueID
AS
UPDATE myTable
SET myFieldBitValue = ~ myFieldBitValue
WHERE UniqueID = @UniqueID
go

You can now execute this procedure with the following statement:

EXEC myProc 12345

where 12345 should be replaced by the value that was scanned from the ID
card - and if it's character data, put it between 'single quotes'.


Final piece of advice: Using bit values is often not the best solution in
a relational database. I generally prefer to store actual human-readable
values. I'm not sure what your myFieldBitValue represents. If you want to
record if an employee is in or out, why not use a CHAR(1) column named
"IsIn" and valued either 'Y' or 'N'? Or you could go for a design that can
be enhanced later: label the column "Presence" (or something like that,
English is not my native language), make it CHAR(3) and allow only values
"In" and "Out" for now.

The CREATE TABLE statement for the last suggestion:
CREATE TABLE myTable (UniqueID int NOT NULL PRIMARY KEY,
Presence char(3) NOT NULL
CHECK (Presence IN ('In', 'Out')),
.....)

And the update statement in the stored procedure:
CREATE PROC myProc
@UniqueID int -- Change this to datatype of UniqueID
AS
UPDATE myTable
SET Presence = CASE
WHEN Presence = 'In'
THEN 'Out'
ELSE 'In
END
WHERE UniqueID = @UniqueID
go

The CASE expression will return 'Out' when the value of Presence before
update was 'In'; it will return 'In' otherwise. This will cause the vaule
of Presence for a specific row in myTable to flip between In and Out if
the procedure is called repeatedly.

Best, Hugo
--