How do/can I run a select SQL statement inside an if?

Question

I'm having trouble finding the syntax for a statement (or whether or not it's even possible).

We're running SQL Server 2005, and I'd like to do something like:

IF ((SELECT count(*) FROM mytable WHERE userid = 'myid' AND value = 3) < 1) then INSERT INTO mytable VALUES ('myid', 3)

Any suggestions? Thanks

Answer 1

IF ((SELECT count(*) FROM mytable WHERE userid = 'myid' AND value = 3) < 1)
BEGIN
    INSERT INTO mytable VALUES ('myid', 3)
END

IF/ELSE reference

or you could use NOT EXISTS

IF NOT EXISTS (SELECT * FROM mytable WHERE userid = 'myid' AND value = 3)

Answer 2

DECLARE @COUNT int

SET @COUNT=(SELECT count(*) FROM mytable WHERE userid = 'myid' AND value = 3)
IF (@COUNT < 1)
BEGIN
    INSERT INTO mytable VALUES ('myid', 3)
END

Answer 3

You could try this to do it in a single statement:

INSERT INTO mytable
SELECT 'myid', 3
WHERE (SELECT count(*) FROM mytable WHERE userid = 'myid' AND value = 3) < 1