Home TSQL IF/ELSE or CASE (UPSERT)
Reply: 1

TSQL IF/ELSE or CASE (UPSERT)

UserSN
1#
UserSN Published in 2017-12-07 16:57:52Z

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]')
Chris Mack
2#
Chris Mack Reply to 2017-12-07 21:49:01Z

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
;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.301174 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO