CODEWITHSUNDEEP
sqlsql-serverstored-procedures
RockOn
RockOn

Reputation: 197

Concatenation within stored procedure

I want to have something like this in my stored procedure:

InvoiceNumber =  EventCode + EventInstance + EventCount

I am not quite sure how to code this into my setup (see below). I have tried many ideas but no luck.

ALTER PROCEDURE [dbo].[spInvoiceNumber]
   @EventCode nvarchar(10),
   @EventInstance nvarchar(10)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Payment (EventCode, EventInstance, EventCount) 
    OUTPUT INSERTED.EventCode, INSERTED.EventInstance, INSERTED.EventCount
        SELECT 
            @EventCode, @EventInstance, ISNULL(MAX(EventCount), -1) + 1
        FROM 
            Payment 
        WHERE 
            (EventCode = @EventCode AND
             EventInstance = @EventInstance)
END

Upvotes: 1

Views: 38

Answers (2)

Squirrel
Squirrel

Reputation: 24763

INSERT INTO Payment (EventCode, EventInstance, EventCount, InvoiceNumber) 
    OUTPUT INSERTED.EventCode, INSERTED.EventInstance, 
           INSERTED.EventCount, INSERTED.InvoiceNumber
    SELECT @EventCode, @EventInstance, 
           isnull(max(EventCount),-1) + 1,
           EventCode + EventInstance + CONVERT(VARCHAR(10), isnull(max(EventCount),-1) + 1)
    FROM   Payment 
    WHERE (EventCode = @EventCode AND
        EventInstance = @EventInstance)

Upvotes: 1

Amit Sukralia
Amit Sukralia

Reputation: 950

You can try something like this:

ALTER PROCEDURE [dbo].[spInvoiceNumber]
        @EventCode nvarchar(10),
        @EventInstance nvarchar(10)
AS

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @InvoiceNumber nvarchar(30) 

    declare @EventCount INT = 
    (select isnull(max(EventCount),-1) + 1
      FROM Payment 
      WHERE (EventCode = @EventCode AND
        EventInstance = @EventInstance)
    )

    SELECT @InvoiceNumber = @EventCode + @EventInstance + convert(nvarchar(10),@EventCount)

    INSERT INTO Payment (EventCode, EventInstance, EventCount, InvoiceNumber) 
    SELECT @EventCode, @EventInstance, @EventCount, @InvoiceNumber

END

Upvotes: 1

Related Questions