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();
      }