How do i retrieve a value output from a stored procedure using: OUTPUT INSERTED.BrandDrugDetailsID

I have a stored procedure:

CREATE PROCEDURE [dbo].[BrandDrugDetailsInsert]
    @BrandDrugDetailsID uniqueidentifier OUTPUT,
    @BrandDrugID uniqueidentifier
AS
BEGIN
    INSERT INTO Pharmacy_BrandDrugDetails(BrandDrugID) OUTPUT INSERTED.BrandDrugDetailsID
    VALUES (@BrandDrugID)
END

Anytime I try to retieve the value "@BrandDrugDetailsID" using:

param[0] = new SqlParameter("@BrandDrugDetailsID", SqlDbType.UniqueIdentifier);
param[0].Direction = ParameterDirection.Output;
.
.
.

identity = new Guid(param[0].Value.ToString());

I get a null value.

If I try to execute the stored procedure in SQL Server itself, three values are returned:

• BrandDrugDetialsID = "471D08BA-382B-4F83-BECC-F96FEF84B5A5"
• @BrandDrugDetialsID = NULL
• Return Value = 0

I can't figure开发者_如何学C out what im doing wrong. Please help me.

I believe that when you use the OUTPUT clause in a INSERT statement, the rows come back as a resultset. So instead of using an OUTPUT parameter in the stored procedure, just run YourSqlCommand.ExecuteScalar(), and BrandDrugDetailsID will come out.

You're not doing anything with the INSERTED.BrandDrugDetailsID bit that would actually place it in the @BrandDrugDetailsID variable. You'll either have to OUTPUT into a table variable and then write the value manually to @BrandDrugDetailsID, or simply use ExecuteScalar to access the single value your procedure currently returns.

Are you sure your stored proc is working. How is the value of INSERTED.BrandDrugDetailsID making it into your output parameter @BrandDrugDetailsID?

DECLARE @TableVar Table (@NewBrandDrugDetailsID uniqueidentifier)

INSERT INTO Pharmacy_BrandDrugDetails(BrandDrugID) OUTPUT INSERTED.BrandDrugDetailsID INTO @TableVar
    VALUES (@BrandDrugID)

SELECT @BrandDrugDetailsID = @NewBrandDrugDetailsID FROM @TableVar

** Disclaimer, I've not tested this ! **

For those who are using Entityframework 6 or below, all you need to do is this:

   using (var db = new AppEntity) 
      {
         var BrandDrugDetailsID = db.BrandDrugDetailsInsert(BrandDrugID).FirstOrDefault();
      }