SELECT @Action = CASE WHEN MyPK_ID IS NULL THEN 'Add' ELSE 'Update' END,
       @MyPK_ID = CASE WHEN MyPK_ID IS NULL THEN -1 ELSE MyPK_ID END
FROM
(
    SELECT 1
) AS X
    LEFT OUTER JOIN MyTable
        ON MyPK_ID = @MyPK_ID

SELECT @Action = 'Update',
       @MyPK_ID = MyPK_ID
FROM  MyTable
WHERE MyPK_ID = @MyPK_ID
IF @@ROWCOUNT = 0
BEGIN
    SELECT @Action = 'Add',
           @MyPK_ID = -1
END

select 
     case 
         when count(*)>=1 then 'update' 
     else 
         'add' 
     end 
from MyTable
where MyPK_ID =@MyPK_ID

quote:

---

Originally posted by Kristen

I like the COUNT(*), thanks Madhi.

I also need some columns from MyTable if it exists. Can I do that somehow?

Perhaps using ROW_NUMBER() or OUTPUT (in some crafty way)?

---

SELECT 
		@Action = case when counting>=1 then 'Update' else 'Add',
		@MyPK_ID = case when counting>=1 then @MyPK_ID else -1,
.
.
.

(
select count(*) as counting 
FROM  MyTable
WHERE MyPK_ID = @MyPK_ID
) as t

SELECT	@UpsertFlag = 1,	-- Use UpSert to save
	@TrackID = NULL,	-- Do NOT change existing value (during save)
	@VoteID = VoteID,
	@SongID = SongID,
	@ErrorNo = CASE WHEN ErrorNo = 0 THEN @ErrorNo ELSE ErrorNo END,
	@strErrMsg = @strErrMsg
		+ CASE WHEN ErrorNo = 0 THEN '' ELSE 'Error in uspMySproc[' + CONVERT(varchar(20), ErrorNo) + '] ' END
FROM
(
	SELECT	VoteID,
		[ErrorNo] = CASE WHEN (@SongID IS NOT NULL AND SongID <> @SongID)
			OR (@VoteID IS NOT NULL AND VoteID <> @VoteID)
			THEN 1
			ELSE 0
			END
	FROM	dbo.VoteLog
	WHERE	    (SongID = @SongID OR VoteID = @VoteID)
		AND UserID = @UserID
) AS X
IF @@ROWCOUNT = 0
BEGIN
	SELECT	@UpsertFlag = 0,	-- Insert new record
		@VoteID = NULL
	IF @SongID IS NULL
	BEGIN
		SELECT	@ErrorNo = -3,
			@strErrMsg = @strErrMsg + 'SQL Error(3) @SongID=[' + CONVERT(varchar(20), @SongID) + ']. '
	END
END

SELECT	@UpsertFlag = CASE WHEN VoteID IS NULL Counting=0 THEN 0 ELSE 1 END,	-- 0=Insert, 1=UpSert
	@TrackID = CASE WHEN VoteID IS NULL Counting=0 THEN @TrackID ELSE NULL END,	-- Do NOT change existing value (during save)
	@VoteID = CASE WHEN VoteID IS NULL Counting=0 THEN @VoteID ELSE VoteID END,
	@SongID = CASE WHEN VoteID IS NULL Counting=0 THEN @SongID ELSE SongID END,
	@ErrorNo = @ErrorNo
		+ CASE WHEN VoteID IS NULL Counting=0 THEN '' 
			ELSE
				CASE WHEN (@SongID IS NOT NULL AND SongID <> @SongID)
						OR (@VoteID IS NOT NULL AND VoteID <> @VoteID)
					THEN 1
					ELSE 0
					END
			END,
	@strErrMsg = ... CASE WHEN [counting] > 1 THEN 'Multiple records found' ELSE '' END ...
FROM
(
	SELECT	[MAX_VoteID] = MAX(VoteID),
		[counting] = COUNT(*)
	FROM	dbo.VoteLog
	WHERE	    (SongID = @SongID OR VoteID = @VoteID)
		AND UserID = @UserID
) AS X
	LEFT OUTER JOIN dbo.VoteLog
		 ON VoteID = MAX_VoteID

quote:

---

Originally posted by Kristen

"Also note that [counting] > 1 should be [counting] >= 1"

Thanks. Actually that's just looking for an error. Should be 0 or 1 rows only, if it gets more than 1 then someone has found a way to hack the system!! (i.e. the PK and a separate Unique Index have been dropped )

Right, I'm sorted with this now I think. I like the solution, thanks for your help.

---