mercredi 2 janvier 2019

Sending SQL a boolean and update a table as 1 or -1 and how to "update" an empty row for the initial values

I have a Songs table with a likes column that holds the number of likes users sent .
Each user sends a boolean (1 or 0) through the C Sharp which adds to the likes column.

About my procedure:
1) A. I want to know if there is more an efficient and short way of writing the part 1 of the function?

B. I had to manually insert '0' instead of the NULL for the first time for the function to work. It wasn't working because the initial value for Likes column is NULL. Is there a way to affect the row for the first time when it has NULL in it?

2) For part 2 of the function with [Users_Likes_Songs] table, I want to update if the user send a like ( true = 1) or removed it (false = 0) (!) How can I update this table for the first time when its rows are completely empty?

I thank you very much if you can help me.
The proc:

    CREATE PROC Songs_Likes
    @User_ID int,
    @SongID int,
    @Song_like bit
    AS
    BEGIN

  --- part 1 of the function

    IF (@Song_like = 1)
    BEGIN
    UPDATE [Songs] SET [Likes] = [Likes] + @Song_like
    WHERE [Song_ID] = @SongID
    END
    IF (@Song_like = 0)
    BEGIN
    UPDATE [Songs] SET [Likes] = [Likes] - 1
    WHERE [Song_ID] = @SongID
    END

--- part 2 of the function with the second table

    UPDATE [Users_Likes_Songs]
    SET [LikeSong] = @Song_like WHERE ([UserID] = @User_ID) AND ([SongID] = @SongID)
    END

    GO

Aucun commentaire:

Enregistrer un commentaire