Stored procedures

Question

I have a stored procedure that inserts a user into a table but I want an output value equals to the new inserted UserID in the table but I don't know how to do it can you guys help me?

I have this

ALTER PROCEDURE dbo.st_Insert_User
(
@Nombre varchar(200),
@Usuario varchar(100),
@Password varchar(100),
@Administrador bit,
@Resultado int output
)
AS
INSERT INTO tbl_Users(Nombre, Usuario, Password, Administrador)
VALUES(@Nombre, @Usuario, @Password, @Administrador)

    SELECT @resultado = UserID

I also tried

SELECT @resultado = UserID FROM tbl_Users WHERE Usuario = @Usuario

Answer 1

For SQL Server, you want to use the OUTPUT clause. See information and examples in Books Online here. It does cover your case-- as it mentions "The OUTPUT clause may be useful to retrieve the value of identity or computed columns after an INSERT or UPDATE operation."

(If this is for real world purposes, you do of course have security concerns in storing passwords that you should address.)

Answer 2

Add at the end

select @@Identity

Answer 3

SELECT SCOPE_IDENTITY()

will give you the identity of the row