SQL Server – How to Concatenate into Field in Update

concatsql serverstored-proceduresupdate

I am trying to write a Stored Procedure which updates a quantity data column in one of my tables, and then takes a field set for random notes and concatenates the update information.

This is what I have for the Procedure thus far

CREATE PROCEDURE UpdatePartQuantity(
    @OrderNum VARCHAR(20),
    @PartNum  VARCHAR(100),
    @Quantity INT
) AS
BEGIN
    DECLARE @OrderID int, @PartID int

    IF(EXISTS(SELECT order_id FROM purchase_orders WHERE order_number = @OrderNum))
    BEGIN
        SELECT @OrderID = order_id
          FROM purchase_orders
         WHERE order_number = @OrderNum
    END
    ELSE
        RETURN 1

    IF(EXISTS(SELECT part_id FROM parts WHERE part_number = @PartNum))
    BEGIN
        SELECT @PartID = part_id
          FROM parts
         WHERE part_number = @PartNum
    END
    ELSE
        RETURN 1

    IF(EXISTS(SELECT part_id, order_id FROM order_parts WHERE order_id = @OrderID AND part_id = @PartID))
    BEGIN
        UPDATE order_parts
        SET ordered = @Quantity, notes = CONCAT(notes, 'string goes here')
        WHERE order_id = @OrderID AND part_id = @PartID
        RETURN 0
    END
    ELSE
        EXEC AddPartToOrder @OrderNum, @PartNum, @Quantity
END

So here is how the procedure is suppose to run:

Check if the passed order number exists within the database, if it does get the order id associated with it.
Check if the passed part number exists within the database, if it does get the part id associated with it.
Check if an entry has even been made for this part in this order. If it hasn't create the entry by called the AddPartToOrder Procedure
If the entry does exists, change the ordered value to the value of @Quantity and concatenate onto the notes column: "Quantity updated: [Current Date]Old-[Old Value],New-@Quantity"
If any If statements return False, exit Procedure returning 1

My issue is I am not sure how to set up my CONCAT statement. How would I put a GETDATE call inside of it? How would I reference the value of @Quantity? I am not exactly sure how to approach this.

Best Answer

You can concatenate text with text, so you need to CAST(). For example,

notes = CAST(GETDATE() AS varchar(max)) + 'Old-' + CAST(ISNULL(notes, '') AS varchar(max)) + 'New-' + CAST(@Quantity AS varchar(max))

Note that concatenate null with something will give you null, so to implement CONCAT() function's functionality you need ISNULL().

Related Solutions

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Sql-server – Advice on how to improve the efficiency of a stored procedure using lots of joins

This is a "kitchen sink" query, for which SQL Server MVP Aaron Bertrand has a good video on how to optimize using dynamic SQL and a detailed post here.

A few points to get you started on the performance of your query:

Use dynamic SQL to simplify the @Read criteria and subsequent lookup in tblReadStatus. I'm guessing that this is going to be your main performance gain.
tblMetaData would benefit from having an index on (ItemType, CreatedDTM, ItemID) INCLUDE (TariffName, ItemCategory, SupervisorName, CustomerLastName, ItemExpiryDTM, CustomerMemberID, CustomerFirstName, CustomerLastName, CustomerDateOfBirth). If this table is large, a good index makes a huge performance difference. A clustered index (as opposed to non-clustered), obviously, won't need INCLUDE columns, just the indexed ones.
If you haven't done so already, put clustered indexes on the key column(s) of your table-type variables (you can define PRIMARY KEY CLUSTERED when you're creating the table type).
Unique index on tblEntityPermissionLink (ItemID) INCLUDE (BusinessID, PIN, SubID, AccountNumber, GroupSubID)
Unique index on tblReadStatus (UserID, ItemID) INCLUDE (ReadIND)
Really consider if those WITH (NOLOCK) are any good to you. This is not neccessarily performance-related, rather a best-practices note because they can cause you a lot of problems with regards to dirty reads.

Best Answer

Related Solutions

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

Sql-server – Advice on how to improve the efficiency of a stored procedure using lots of joins

Related Question