TSQL IF/ELSE or CASE (UPSERT)

Question

Not sure if IF/ELSE is the right way to go for the following. It always returns ELSE so it seems its not working correctly.

IF ((SELECT COUNT(CAST(StudentuserID AS int)) FROM StudentAttendance WHERE StudentUserID=1)>0)
PRINT 'Yes'
ELSE 
PRINT 'No'

This test should result in yes as the data is 8>0
I will be replacing PRINT with an UPDATE ELSE INSERT statement.

IF ((SELECT COUNT(CAST(StudentuserID AS int)) FROM StudentAttendance WHERE StudentUserID=1)>0)
UPDATE StudentAttendance
SET
CID = CAST('[querystring:CID]' AS int),
CalendarEventID = CAST('[querystring:CEID]' AS int),
StudentUserID = CAST('[StudentUserID]' AS int),
Attendance = '[Attendance]'
ELSE
INSERT INTO StudentAttendance
(CID,CalendarEventID,StudentUserID,Attendance)
VALUES
(CAST('[querystring:CID]' AS int), CAST('[querystring:CEID]' AS int), CAST('[StudentsUserID]' AS int),'[Attendance]')

[`MERGE`](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql) can upsert. — Alex K., Dec 07 '17 at 17:01
https://stackoverflow.com/questions/2479488/syntax-for-single-row-merge-upsert-in-sql-server — Alex K., Dec 07 '17 at 17:04
@AlexK. possible to use this with join on two tables for the source? — UserSN, Dec 07 '17 at 17:24
LMGTFY: [`Merge`](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql). — HABO, Dec 07 '17 at 18:12

score 1 · Accepted Answer · answered Dec 07 '17 at 21:49

It looks like your IF/ELSE would work fine (it looks like you're doing this for one record in a stored procedure or something?). If it's currently returning 'No' and you don't think it should be, I'd perhaps do a more basic check on your table, e.g.:

SELECT *
FROM StudentAttendance
WHERE StudentUserID = 1

You can also use a MERGE statement for this, and you can use multiple source tables by joining them within the USING part. Here is a basic example of that:

DECLARE @A table (Aid int, value int)
DECLARE @B table (Aid int, Cid int)
DECLARE @C table (Cid int, value int)

INSERT INTO @A VALUES (1, 1)
INSERT INTO @B VALUES (1, 2)
INSERT INTO @B VALUES (2, 3)
INSERT INTO @C VALUES (2, 4)
INSERT INTO @C VALUES (3, 6)
;

SELECT *
FROM @A
;

MERGE INTO @A tgt
USING (SELECT B.Aid, B.Cid, C.value FROM @B B JOIN @C C ON B.Cid = C.Cid) src
ON tgt.Aid = src.Aid

WHEN MATCHED THEN UPDATE
SET tgt.value = src.value

WHEN NOT MATCHED THEN
INSERT
(
    Aid
    , value
)

VALUES
(
    src.Aid
    , src.value
)
;

SELECT *
FROM @A
;

thank you very much for your example. I had created a second question for this issue using `MERGE` as you've described. I did have an issue with inserts regardless of the conditions but I will review this answer and see how far i can get. Thank you. https://stackoverflow.com/questions/47700748/tsql-merge-with-multiple-sources/47701401?noredirect=1#comment82365269_47701401 — UserSN, Dec 07 '17 at 23:55

TSQL IF/ELSE or CASE (UPSERT)

1 Answers1