Je veux avoir quelque chose comme ça dans ma procédure stockée:
InvoiceNumber = EventCode + EventInstance + EventCount
Je ne sais pas trop comment coder ceci dans ma configuration (voir ci-dessous). J'ai essayé beaucoup d'idées mais pas de chance.
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
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)
Vous pouvez essayer quelque chose comme ceci:
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