Return value in SQL Server stored procedure

Question

I have a stored procedure that has an if statement in it. If the number of rows counted is greater than 0 then it should set the only output parameter @UserId to 0

However it only returns a value in the second part of the query.

@EmailAddress varchar(200),
@NickName varchar(100),
@Password varchar(150),
@Sex varchar(50),
@Age int,
@EmailUpdates int,
@UserId int OUTPUT
IF 
    (SELECT COUNT(UserId) FROM RegUsers WHERE EmailAddress = @EmailAddress) > 0
    BEGIN
        SET @UserId = 0
    END
ELSE
    BEGIN
        INSERT INTO RegUsers (EmailAddress,NickName,PassWord,Sex,Age,EmailUpdates) VALUES (@EmailAddress,@NickName,@Password,@Sex,@Age,@EmailUpdates)
        SELECT SCOPE_IDENTITY()
    END

END

Answer 1

You can either do 1 of the following:

Change:

SET @UserId = 0 to SELECT @UserId

This will return the value in the same way your 2nd part of the IF statement is.

---

Or, seeing as @UserId is set as an Output, change:

SELECT SCOPE_IDENTITY() to SET @UserId = SCOPE_IDENTITY()

---

It depends on how you want to access the data afterwards. If you want the value to be in your result set, use SELECT. If you want to access the new value of the @UserId parameter afterwards, then use SET @UserId

---

Seeing as you're accepting the 2nd condition as correct, the query you could write (without having to change anything outside of this query) is:

@EmailAddress varchar(200),
@NickName varchar(100),
@Password varchar(150),
@Sex varchar(50),
@Age int,
@EmailUpdates int,
@UserId int OUTPUT
IF 
    (SELECT COUNT(UserId) FROM RegUsers WHERE EmailAddress = @EmailAddress) > 0
    BEGIN
        SELECT 0
    END
ELSE
    BEGIN
        INSERT INTO RegUsers (EmailAddress,NickName,PassWord,Sex,Age,EmailUpdates) VALUES (@EmailAddress,@NickName,@Password,@Sex,@Age,@EmailUpdates)
        SELECT SCOPE_IDENTITY()
    END

END