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

Question

I have a storeprocedure:

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 executing the storeprocedure from sqlserver itself, three values are returned:

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

I can't figure out what im doing wrong. Please help me.

Answer 1

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.

Answer 2

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.

Answer 3

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 ! **