I need to check to see if a record exists to determine whether to insert a
record or not.

If I have the following:

SELECT *
FROM Image
WHERE nUserID = @UserID AND
nEventID = @EventID AND
nDocumentID = @DocumentID

if @@ROWCOUNT = 0
INSERT ...

The problem is that one or 2 of the parameters can be null. @UserID cannot
be null.

If @UserID = 1234, @EventID = 1111, @DocumentID = NULL

I want it to return the record where @UserID is 1234, @EventID is 1111 and
@DocumentID is NULL.

If @UserID = 1234, @EventID = 1111, @DocumentID = 2222

I want it to return the record where @UserID is 1234, @EventID is 1111 and
@DocumentID is 2222

I thought about changing it to:

SELECT *
FROM Image
WHERE nUserID = @UserID AND
((@EventID is NULL AND nEventID is null) OR (@EventID IS
NOT NULL AND nEventID = @EventID)) AND
((@DocumentID IS NULL AND nDocumentID is null) OR
(@DocumentID IS NOT NULL AND nDocumentID = @DocumentID))

if @@ROWCOUNT = 0
INSERT ...

Is this the best way to do this?

Thanks,

Tom

On Fri, 12 Nov 2010 21:13:49 -0800, "tshad" <tfs (AT) dslextreme (DOT) com>
wrote:

(snip)
Hi Tom,

Almost. You can slightly simplify the test. More important: the
seperation of SELECT * and INSERT leaves room for problems if a
concurrent connection inserts the same row at almost the same time.

My suggestion:

INSERT INTO Image (nUserID, nEventID, nDocumentID, ...)
SELECT @UserID, @EventID, @DocumentID, ...
WHERE NOT EXISTS
(SELECT *
FROM Image
WHERE nUserID = @UserID
AND ( (nEventID IS NULL AND @EventID IS NULL)
OR nEventID = @nEventID)
AND ( (nDocumentID IS NULL AND @DocumentID IS NULL)
OR nDocumentID = @nDocumentID));
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

I do like your simplification better.

But I still need to do the select first because I need the PK for another
insert. The key is a FK to another table. Otherwise yours is perfect.
ImageID is an identity column (don't tell CELKO )

To handle the concurrent connection I would wrap the whole transaction in a
transaction.

DECLARE @ImageID bigint

BEGIN TRY
BEGIN TRAN
SELECT @ImageID = ImageID
FROM Image
WHERE nUserID = @UserID
AND ( (nEventID IS NULL AND @EventID IS NULL)
OR nEventID = @nEventID)
AND ( (nDocumentID IS NULL AND @DocumentID IS NULL)
OR nDocumentID = @nDocumentID);

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO Image (nUserID, nEventID, nDocumentID, ...)
SELECT @UserID, EventID,DocumentID,...)

SELECT @ImageID = SCOPE_IDENTITY()
END

INSERT ...
SELECT @ImageID,...

INSERT another table...

COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
END TRY

END

What do you think?

Also, isn't there a time between the insert/select where someone could get
in or does it lock the record until the whole insert/select is done? Wasn't
sure on that. I know the change would be remote.

Thanks,

Tom

"Hugo Kornelis" <hugo (AT) perFact (DOT) REMOVETHIS.info.INVALID> wrote

tshad wrote:
find out .... but yes, the row would be locked until the transaction is
committed (or rolled back)

Hi Tom,

Bob's answer is not completely correct. If you insert the row, the
lock is kept until the end of the transaction. But without locking
hints and under the default transaction isolation level, the lock used
by the SELECT will be released immediately. So your code does have the
risk of getting vioaltions of the unique constraint on (nUserID,
nEventID, nDocumentID).

Try this instead:

DECLARE @OutTab TABLE(ImageID int);
DECLARE @ImageID int;
BEGIN TRANSACTION;
BEGIN TRY;
INSERT INTO Image (columns)
OUTPUT ImageID INTO @OutTab
SELECT (columns)
WHERE NOT EXISTS (see previous post);
IF @@ROWCOUNT = 0
BEGIN;
SELECT @ImageID = ImageID
FROM Image
WHERE you know what comes here;
END;
ELSE
BEGIN;
SELECT @ImageID = ImageID
FROM @OutTab;
END;
-- Other INSERT statements, using @ImageID.
COMMIT TRAN;
END TRY
BEGIN CATCH;
ROLLBACK TRAN;
RAISERROR ('Descriptive error message', 16, 1);
END;

Yet another possibility is to not use the table variable and the
output clause, and to make the SELECT @ImageID = ... unconditional;
this will either read the just-inserted row, or the row that already
existed and caused the insert to do nothing.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Of course you are correct. I failed to read it closely enough.

Hugo Kornelis wrote:
--
HTH,
Bob Barrows