SQL toggle variable in database
I have a procedure for a table in my database, but it doesn't seem to do what I want it to. Could someone check if I am doing it wrong? I am new to SQL all together.
This is my procedure. It is supposed to change admin status from 1 to 0 and from 0 to 1 when I tell it to. But for some reason it changes it and doesn't change it back :(
ALTER PROCEDURE [sesuser].[Login_Admin_Toggle] ( @loginID INT ) AS BEGIN DECLARE @tmpId INTEGER; SET @tmpId = (SELECT SESAdmin FROM [ses_users].[sesuser].[SESLogin] WHERE SESLoginID = @loginID); IF (@tmpId = 1) BEGIN SET @tmpId = 0 RETURN END ELSE SET @tmpId = 1 UPDATE [ses_users].[sesuser].[SESLogin] SET SESAdmin = @tmpId WHERE SESLoginID = @loginID END
Alex K is completely right. You set your variable to 0 then RETURN, meaning that you never get to the UPDATE statement from there.
But, on a slight tangent, you can actually do all of that in one statement...
BEGIN UPDATE [ses_users].[sesuser].[SESLogin] SET SESAdmin = 1 - SESAdmin WHERE SESLoginID = @loginID END
It won't toggle back because you RETURN and so halt execution if @tmpId = 1, so its never updated to 0.