phattrienweb

Posted by Max on Wednesday, September 01, 2010 6:50 AM
If you need to get the identity value from a query, you can use SCOPE_IDENTITY().

In your stored procedure add:

SET @id=SCOPE_IDENTITY()
RETURN @id

and when you create the procedure, declare:

@id int output

-Example-

In this example we create a Stored Procedure to add a new category (idcategory,categoryname). The procedure return the identity value, in this case the id of the category:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[anm_InsertCategory] (
@categoryname nvarchar(256),
@idcategory int output
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [anm_Categories] ([category]) VALUES (@categoryname)
SET @idcategory=SCOPE_IDENTITY()
RETURN @idcategory
END

In this way we use the stored procedure in C#:

string strConn = ConfigurationManager.ConnectionStrings["csname"].ToString();
SqlConnection conn = new SqlConnection(strConn);
SqlCommand command = new SqlCommand("anm_InsertCategory", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@categoryname", SqlDbType.NVarChar).Value = cat_name;
command.Parameters.Add("@idcategory", SqlDbType.Int).Direction = ParameterDirection.Output;
conn.Open();
command.ExecuteNonQuery();
string idcat = command.Parameters["@idcategory"].Value.ToString();

Now the value of the string idcat is the identity value of the new category just added and you can use this value in another query.