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

Answers


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.


Need Your Help

Inorder tree traversal with parentheses

tree tree-traversal

I've decided to try to teach myself how to program and am working my way through the Python version of "How to think like a computer scientist". Have tried to hold off asking questions about the

How to group dates by week?

c# linq grouping

I am writing an Excel exporter for a bespoke application I am creating, and I have a question about LINQ grouping in C#.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.