Create stored procedure to add with auto increment as its primary field?

I am trying to insert Data into my Sql-Server database though C#. I’m Calling a stored procedure and then would like it to add. I’m not sure what changes to make, but ultimately i would like it done in the stored procedure.

My Stored procedure now:

  • JDBI query returning the wrong value for MSSQL datetimeoffset(4) column?
  • Query to match products to customers?
  • Questions about local database vs service-based database
  • Why might SQL execute more quickly on SQL Server 2000 when NOT using a stored procedure?
  • The multi-part identifier could not be bound sql for query spanning multiple tables
  • SQL Server Timezone Change
  • CREATE PROCEDURE [dbo].[InsertTagProcdure]
           @TagID int, 
           @Value nvarchar(200), 
           @TagCount nvarchar(200) 
    AS
    IF NOT EXISTS (SELECT NULL FROM Tag
                    WHERE @TagID = @TagID)
    BEGIN
        INSERT INTO 
            Tag 
            (TagID,Value,TagCount) 
            VALUES 
            (@TagID,@Value,@TagCount)
    END
    

    And my C# Code:

    int TagID = int.Parse(txtTagID.Text); //This should fall away so auto increment.
                String Value = txtValue.Text;
                int TagCount = int.Parse(txtCount.Text); 
    
                using (var conn = new SqlConnection(Properties.Settings.Default.DBConnectionString))
                using (var cmd = conn.CreateCommand())
                {
                        conn.Open();
                        cmd.CommandText = "InsertTagProcdure";
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@TagID", TagID);
                        cmd.Parameters.AddWithValue("@Value", Value);
                        cmd.Parameters.AddWithValue("@TagCount", TagCount);
                        cmd.ExecuteNonQuery();
                    }
    

    The Table Create i used: //Cant change this its what the boss gave me.

    CREATE TABLE [dbo].[Tag](
        [TagID] [int] IDENTITY(1,1) NOT NULL,
        [Value] [varchar](200) NOT NULL,
        [TagCount] [varchar](200) NULL,
     CONSTRAINT [PK_Tag] PRIMARY KEY CLUSTERED 
    (
        [TagID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    

  • Select Rows As Column in SELECT
  • What is the comparative speed of temporary tables to physical tables in SQL?
  • Editing xml using SQL
  • Listing Number Of Rows based on Quantity
  • How do I delete duplicate rows in SQL Server using the OVER clause?
  • How to get first and last date of current year
  • 4 Solutions collect form web for “Create stored procedure to add with auto increment as its primary field?”

    Ideally you would just make TagID an identity field by changing the table definition. If you can’t do that, next best would be:

    CREATE PROCEDURE [dbo].[InsertTagProcdure]
           @Value nvarchar(200), 
           @TagCount nvarchar(200) 
    AS
    BEGIN
        BEGIN TRANSACTION
            DECLARE @TagID int;
            SELECT @TagID = coalesce((select max(TagID) + 1 from Tag), 1)
        COMMIT      
        INSERT INTO 
            Tag 
            (TagID,Value,TagCount) 
            VALUES 
            (@TagID,@Value,@TagCount)
    END
    

    The transaction ensures that you don’t end up with unique TagIDs and the coalesce handles the special case where the table is empty and gives an initial value of 1.

    EDIT:

    Based on the change to your original question, the table already has an identity column so your stored procedure should be:

    CREATE PROCEDURE [dbo].[InsertTagProcdure]
           @Value nvarchar(200), 
           @TagCount nvarchar(200) 
    AS
    BEGIN
        INSERT INTO Tag (Value,TagCount) VALUES (@Value,@TagCount)
    END
    

    and your C# code should be

    int TagID = int.Parse(txtTagID.Text); //This should fall away so auto increment.
    String Value = txtValue.Text;
    int TagCount = int.Parse(txtCount.Text);

            using (var conn = new SqlConnection(Properties.Settings.Default.DBConnectionString))
            using (var cmd = conn.CreateCommand())
            {
                    conn.Open();
                    cmd.CommandText = "InsertTagProcdure";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Value", Value);
                    cmd.Parameters.AddWithValue("@TagCount", TagCount);
                    cmd.ExecuteNonQuery();
                }
    

    You want to set the tag table up so that it uses the identity property. See here: http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx.

    Then you can drop TagId from the procedure, the insert statement in the procedure and the c# code.

    It then becomes something like this:

    CREATE PROCEDURE [dbo].[InsertTagProcdure]
           @Value nvarchar(200), 
           @TagCount nvarchar(200) 
    AS
    BEGIN
        INSERT INTO 
            Tag 
            (Value,TagCount) 
            VALUES 
            (@Value,@TagCount)
    END
    

    C# Code:

                String Value = txtValue.Text;
                int TagCount = int.Parse(txtCount.Text); 
    
                using (var conn = new SqlConnection(Properties.Settings.Default.DBConnectionString))
                using (var cmd = conn.CreateCommand())
                {
                        conn.Open();
                        cmd.CommandText = "InsertTagProcdure";
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@Value", Value);
                        cmd.Parameters.AddWithValue("@TagCount", TagCount);
                        cmd.ExecuteNonQuery();
                    }
    

    the statement WHERE @TagID = @TagID will always be true, because your comparing the same values.
    I think your looking for this (assuming TagID is your AUTO-ID field);

    CREATE PROCEDURE [dbo].[InsertTagProcdure] 
           @TagID int,  
           @Value nvarchar(200),  
           @TagCount nvarchar(200)  
    AS 
    BEGIN
    IF NOT EXISTS (SELECT TagID FROM Tag WHERE TagID = @TagID) 
    BEGIN 
        INSERT INTO  
            Tag  
            (Value,TagCount)  
            VALUES  
            (@Value,@TagCount) 
      SET @TagID = @@IDENTITY
    END 
    ELSE
    BEGIN
      UPDATE Tag
        SET Value=@Value,
        TagCount=@TagCount
      WHERE TagID = @TagID
    END
    RETURN @TagID
    END
    

    You have to turn on ‘Identity specification’ in SQL Server on the appropriate column. Go to table design mode, select the column and in the bottom properties screen there is a property called ‘Identity specification’.

    After this, you can omit the TagId in your stored procedure. Sql Server will automatically assign an id to a new record.

    If you want to know what that newly inserted id is (which I assume is your next question), you can use the function SCOPE_IDENTITY() in your stored procedure, which returns the id of the newly inserted record.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.