CODEWITHSUNDEEP
sql-serverstored-procedures
uzzum07
uzzum07

Reputation: 101

using if exists or not exists in sql

Hi all I am writing a stored procedure in SQL for a list, like for example I have a list of items and I want to add another one, so I get the MAX(columName) and I add +1 which results in the last option. The only thing is I am filtering by person. Meaning I can see someone else's list as well and add things to it. Well my stored procedure is not working when someone doesn't have an item yet. How can I check if it exists first?

DECLARE @MAXID INT
SET @MAXID = (SELECT MAX(priority)+1
                FROM    Chores
                WHERE   userID = @user) 

UPDATE Chores
    SET userID= @user,
    priority = @MAXID
FROM
    Chores WITH (NOLOCK)
WHERE choreID= @iD

Results I am getting when I run this I get Null on the priority number for the item. I'm assuming because it did not find the MAX() of anything

As you can see I am adding a +1 to the list of items by given individual but I have a null value if the user doesn't have something already on their list. I believe I can do an If Exists() but how would that work if i am declaring and setting the value from the get go. Also the other parameters are being passed in which is why I am not declaring them.

Upvotes: 1

Views: 98

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I think you want:

UPDATE Chores
    SET userID= @user,
    priority = COALESCE(max_priority, 1)
FROM (SELECT c.*, MAX(CASE WHEN userId = @user THEN priority END) OVER () as max_priority
      FROM Chores c
     ) c
WHERE choreID = @iD ;

Upvotes: 2

Related Questions