Return parameters in Strored procedures
-
I'm writing a new database driven application. In one of my stored procedures, I want to add user data and get back user id from table. The table has this structure:
ID, numeric, identity with identity seed =1,
username, char(25)
email, char(40)my stored procedure looks like this:
ALTER PROCEDURE dbo.InsertNewUser
(
@UserName char(25),
@email char(40),
@ID numeric OUTPUT
)
AS
INSERT INTO Users
(UserName, email)
VALUES (@UserName, @email);
RETURNHow I changed the stored procedure to return the new added user's ID? I code in C#. Best regards, A. Riazi
-
I'm writing a new database driven application. In one of my stored procedures, I want to add user data and get back user id from table. The table has this structure:
ID, numeric, identity with identity seed =1,
username, char(25)
email, char(40)my stored procedure looks like this:
ALTER PROCEDURE dbo.InsertNewUser
(
@UserName char(25),
@email char(40),
@ID numeric OUTPUT
)
AS
INSERT INTO Users
(UserName, email)
VALUES (@UserName, @email);
RETURNHow I changed the stored procedure to return the new added user's ID? I code in C#. Best regards, A. Riazi
If this is SQL Server 2000:
SET @ID = SCOPE_IDENTITY()
If SQL Server 7.0, you have to use the
@@IDENTITYvariable; however, this may produce incorrect results if a trigger fires which inserts rows. Stability. What an interesting concept. -- Chris Maunder -
If this is SQL Server 2000:
SET @ID = SCOPE_IDENTITY()
If SQL Server 7.0, you have to use the
@@IDENTITYvariable; however, this may produce incorrect results if a trigger fires which inserts rows. Stability. What an interesting concept. -- Chris MaunderSQL SERVER2000 SET @ID = @@IDENTITY you are welcome to visit my Blog