Can I use output variables to get a value?

I have a stored procedure where I'm checking if username exists and if it does not exists insert into user table then gets userid for the same username. I was trying to use an output variable to get the userid. Am I going about this wrong?

alter Procedure Check_User_Name
(
    @username varchar(25),
    @password varchar(100),
    @role_id integer,
    @idn nvarchar output
)
As
Begin
    IF NOT EXISTS(SELECT idn=@idn
        FROM [user] WHERE username = @username)
    BEGIN
        INSERT INTO [user] (username,[password],role_id) VALUES
        (@username, @password,@role_id)
        --select @idn=idn from [user]
        Print 'UserName inserted successfully'
    End
    Else IF EXISTS(SELECT * FROM [user] WHERE username = @username)
    Begin
        Print 'UserName already exists'
    End
END

You had it close -- After replacing your column names to match with what is in my user table, this worked for me. (I think I replaced everything back).

On your Else, you're basically doing a second lookup by username - which is unnecessary in this case.

ALTER Procedure Check_User_Name
  (
@username varchar(25),
@password varchar(100),
 @role_id integer,
 @idn nvarchar(20) output
 )
 As
Begin
        IF NOT EXISTS(SELECT idn
         FROM [user] WHERE username = @username)
            BEGIN
                INSERT INTO [user] (username,[password], role_id) VALUES
                 (@username, @password)
                select @idn=idn from [user] WHERE username = @username
                Print 'UserName inserted successfully'
            End
            Else
               Begin
                    Print 'UserName already exists'
                    SELECT @idn = idn FROM [user]
                    WHERE username = @username
               End 

 END
 GO

Execute with:

 DECLARE @idnOut nVarChar(20)
 exec Check_User_Name @username = 'user2127184', @password = 'asdf', @role_id = 0,  @idn = @idnOut OUTPUT
 SELECT @idnOut

Related Articles