Get the primary key as output or return when you performing an Update query?

Question

I'm trying to get the output parameter of primary key which is ID. When I do the update query I get Null. Can you please suggest a way to do this?

CREATE PROCEDURE sp_InsertTax
    (@ID int output, 
     @TaxAuthorityID int, 
     @TaxClassificationID int, 
     @EntityID int, 
     @AppliesTo_TaxEntityTypeID int)
AS
    IF EXISTS (SELECT * FROM Tax 
               WHERE TaxAuthorityID = @TaxAuthorityID  
                 AND TaxClassificationID = @TaxClassificationID 
                 AND EntityID = @EntityID 
                 AND AppliesTo_TaxEntityTypeID = @AppliesTo_TaxEntityTypeID)
    BEGIN
        UPDATE Tax 
        SET TaxAuthorityID = @TaxAuthorityID, 
            TaxClassificationID = @TaxClassificationID, 
            EntityID = @EntityID, 
            AppliesTo_TaxEntityTypeID = @AppliesTo_TaxEntityTypeID
        WHERE ID = @ID 
    END
    ELSE 
    BEGIN
        IF @ID IS NULL
        BEGIN
            INSERT INTO Tax(TaxAuthorityID, TaxClassificationID, EntityID, AppliesTo_TaxEntityTypeID)
            VALUES (@TaxAuthorityID, @TaxClassificationID, @EntityID, @AppliesTo_TaxEntityTypeID)

            SET @ID = Scope_Identity()
        END
    END
GO

The below is my ADO.NET code to call the update stored procedure:

public int InsertFederalTax(int ClassificID, int appliesTo)       
{
    int tax_id = 0;
    Sqlconn.Open();

    SqlCommand cmd = new SqlCommand("sp_InsertTax", Sqlconn);
    cmd.CommandType = CommandType.StoredProcedure;

    var returnparameter = cmd.Parameters.AddWithValue("ID", SqlDbType.Int);
    returnparameter.Direction = ParameterDirection.Output;

    cmd.Parameters.Add("@TaxAuthorityID", SqlDbType.Int).Value = 1;
    cmd.Parameters.Add("@TaxClassificationID", SqlDbType.Int).Value = ClassificID;
    cmd.Parameters.Add("@EntityID", SqlDbType.Int).Value = 0;
    cmd.Parameters.Add("@AppliesTo_TaxEntityTypeID", SqlDbType.Int).Value = appliesTo;

    cmd.ExecuteNonQuery();

    if (!(returnparameter.Value is DBNull))
        tax_id = Convert.ToInt32(returnparameter.Value);
                                 
    Sqlconn.Close();
    return tax_id;
}

You're looking for [SCOPE_IDENTITY](https://learn.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-ver15). Return this from your Stored Procedure, and use ExecuteScalar instead of ExecuteNonQuery. — Robert Harvey, Mar 01 '21 at 20:04
`returnparameter` you are never adding this parameter to your `cmd.Parameters` collection. Also, your using `@ID` as part of a `where` clause, so that parameter direction needs to be `InputOutput`. — Trevor, Mar 01 '21 at 20:04
You shouldn't add that parameter with a value that is a SqlDbType. — Crowcoder, Mar 01 '21 at 20:04
@RobertHarvey it's perfectly fine to select the `SCOPE_IDENTITY` to an output parameter and theres no need to do a `ExecuteScalar`; `ExecuteNonQuery` is fine here. — Trevor, Mar 01 '21 at 20:05
On another note, the way you're writing your database code is *very old.* Consider using a more modern approach like Dapper. — Robert Harvey, Mar 01 '21 at 20:05
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Mar 01 '21 at 20:14
One more suggestion, look at doing the update first, don't worry about the exists to be honest (there wont be an update if it doesn't exist). You can check `@@ROWCOUNT` and then do the insert if needed. The way you're doing it now *could* lead to deadlocks as it would be scanning all rows for those ids. If it does exist, then you have to find that row again, don't make multiple trips when you don't need to. — Trevor, Mar 01 '21 at 20:14
@Codexer I tried using InputOutput.But, Now it's triggering out the error with you can't have the duplicate values because I have a unique index. That means it's not updating according to the stored procedure. — Nick_H, Mar 01 '21 at 20:36
Does this answer your question? [How do I use an INSERT statement's OUTPUT clause to get the identity value?](https://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value) — derpirscher, Mar 01 '21 at 21:21
@derpirscher I need to return or get the output value when I'm doing the update query not when I'm doing the insert. — Nick_H, Mar 01 '21 at 21:33
@Nick_H, please, share with us the O.S. version, MSSQL version and .NET Framework version. I´ll generate Virtual Machine to make real simulate case. — Antonio Leonardo, Mar 02 '21 at 00:40
How would your update work, if you don't know the ID, as it is part of the where condition? For update maybe this can help. https://stackoverflow.com/questions/16847297/update-output-into-a-variable — derpirscher, Mar 02 '21 at 07:58

Dale K · Accepted Answer · 2021-03-02T18:50:12.460

0

I think you intended to capture the ID of an existing duplicate record, which you would do as follows. I've also added best practice template items for a SP. Also note the comment from marc_c about not prefixing your SP with sp_.

CREATE PROCEDURE InsertTax
(
   @ID int output 
   , @TaxAuthorityID int 
   , @TaxClassificationID int 
   , @EntityID int 
   , @AppliesTo_TaxEntityTypeID int
)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;

    -- This assumes that none of the parameters can ever be null
    -- And from your comments we know that no duplicates can exist
    SELECT @ID = ID
    FROM Tax 
    WHERE TaxAuthorityID = @TaxAuthorityID  
    AND TaxClassificationID = @TaxClassificationID 
    AND EntityID = @EntityID 
    AND AppliesTo_TaxEntityTypeID = @AppliesTo_TaxEntityTypeID;

    IF @ID IS NOT NULL BEGIN
        UPDATE Tax 
        SET TaxAuthorityID = @TaxAuthorityID, 
            TaxClassificationID = @TaxClassificationID, 
            EntityID = @EntityID, 
            AppliesTo_TaxEntityTypeID = @AppliesTo_TaxEntityTypeID
        WHERE ID = @ID;
    END; ELSE BEGIN
        INSERT INTO Tax (TaxAuthorityID, TaxClassificationID, EntityID, AppliesTo_TaxEntityTypeID)
            VALUES (@TaxAuthorityID, @TaxClassificationID, @EntityID, @AppliesTo_TaxEntityTypeID);

        SET @ID = SCOPE_IDENTITY();
    END;

    RETURN 0;
END;
GO

And I recommend declaring your return parameter as:

var returnparameter = new SqlParameter("@ID", SqlDbType.Int)
{
    Direction = ParameterDirection.InputOutput
};

cmd.Parameters.Add(returnparameter);

edited Mar 02 '21 at 18:50

answered Mar 01 '21 at 22:09

Dale K

25,246
15
42
71

I have tried as you suggested. I got the return parameter. Still I have some issues with the update query. – Nick_H Mar 02 '21 at 05:02
I asked twice for the list of versions of the Operating System, SQL Server and .NET Framework because I want to set up a Virtual Machine to find out and understand more what is happening, but I haven't had an answer yet. Doesn't that sound strange? – Antonio Leonardo Mar 02 '21 at 05:03
@AntonioLeonardo thats not how the site works. That said use any version, the code used in the question is pretty generic. – Dale K Mar 02 '21 at 05:20
@DaleK, I only want to help to resolve this question, using a scenario, but not any scenario – Antonio Leonardo Mar 02 '21 at 05:23
@DaleK I'm importing data from a CSV file. If they already exist I want to update if not insert. Though my update query is right still it imports duplicate values. That means it's not updating the values. – Nick_H Mar 02 '21 at 17:29
@Nick_H that means that some values are either null (which won't match an = compare) or similar but not equal. – Dale K Mar 02 '21 at 18:51

Antonio Leonardo · Answer 2 · 2021-03-01T21:34:47.170

-1

Please, may you try to change your C# code with this updates bellow, and give us feed-back:

public int InsertFederalTax(int ClassificID, int appliesTo)       
{
    int tax_id = 0;
    Sqlconn.Open();

    SqlCommand cmd = new SqlCommand("sp_InsertTax", Sqlconn);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@ID", SqlDbType.Int);  
    cmd.Parameters["@ID"].Direction = ParameterDirection.Output; 

    cmd.Parameters.AddWithValue("@TaxAuthorityID", 1);
    cmd.Parameters.AddWithValue("@TaxClassificationID", ClassificID);
    cmd.Parameters.AddWithValue("@EntityID", 0);
    cmd.Parameters.AddWithValue("@AppliesTo_TaxEntityTypeID", appliesTo);

    cmd.ExecuteNonQuery();

    if(!(cmd.Parameters["@ID"].Value is DBNull))
    {
       tax_id = Convert.ToInt32(cmd.Parameters["@ID"].Value);
    }
                                 
    Sqlconn.Close();
    return tax_id;
}

edited Mar 01 '21 at 21:34

answered Mar 01 '21 at 21:01

Antonio Leonardo

1,805
1
8
18

Nope didn't Work Antonio. – Nick_H Mar 01 '21 at 21:15
@Nick_H, please, may you test again? I put conversion type error on output variable value – Antonio Leonardo Mar 01 '21 at 21:36
I already changed the conversion type and then I tried the mentioned code. Still didn't work. Thanks. – Nick_H Mar 01 '21 at 21:44
@Nick_H, please and again, share with us the O.S. version, MSSQL version and .NET Framework version. I´ll generate Virtual Machine to make real simulate case. – Antonio Leonardo Mar 02 '21 at 04:49

Get the primary key as output or return when you performing an Update query?

2 Answers2