Select and Update in One Proc, Maybe a CTE?

[Auguy]

I think there is an easier way to get this done with a CTE, but I can't seem to get it right. I'm trying to return the next invoice number from this table and also increment the invoice number in one procedure. I would also like to return the editcount column. My problem with the CTE was once i used it in the SELECT @NextInvoiceNbr, then I couldn't use it in the update. Can somebody give some advice as to how I can accomplish this, with or without a CTE? Here's the current proc.

	--Parameters
        @CompanyMasterFK int,
	@NextInvoiceNbr	int output,
	@MyError int output

AS
BEGIN	SELECT @NextInvoiceNbr = (Select NextNumber From dbo.CompanyInvoiceNumber Where CompanyMasterFK = @CompanyMasterFK)

	SELECT @MyError = @@ERROR

	IF @MyError = 0
		BEGIN
			Update dbo.CompanyInvoiceNumber
				Set NextNumber = @NextInvoiceNbr + 1,
				EditCount = EditCount + 1

				Where CompanyMasterFK = @CompanyMasterFK And NextNumber = @NextInvoiceNbr

			SELECT @MyError = @@ERROR
		END
END

Auguy
Sylvania/Toledo Ohio

 

[fredericofonseca]

try this way

if object_id('getnextinvoicenumber', 'P') is not null
    drop procedure getnextinvoicenumber;

if object_id('CompanyInvoiceNumber') is not null
    drop table CompanyInvoiceNumber;

create table CompanyInvoiceNumber
(CompanyMasterFK int
,NextNumber int
,EditCount int
);



insert into CompanyInvoiceNumber
    values (1, 1, 1
    );

go



create procedure getnextinvoicenumber
    --Parameters
    @CompanyMasterFK int
   ,@NextInvoiceNbr int output
   ,@EditCount int output
   ,@MyError int output

as
begin
    set nocount on;
    
    declare @t table
    (NextNumber int
    ,EditCount int
    );

    update cin
        set cin.EditCount = cin.EditCount + 1
           ,cin.NextNumber = cin.NextNumber + 1
        output inserted.EditCount
        , inserted.NextNumber
        into @t
    from CompanyInvoiceNumber cin
    where cin.CompanyMasterFK = @CompanyMasterFK;

    select @EditCount = EditCount
          ,@NextInvoiceNbr = NextNumber
    from @t;


    select @MyError = @@error;
end

go

declare @CompanyMasterFKin int = 1
       ,@NextInvoiceNbrout int
       ,@EditCountout int
       ,@MyErrorout int
       ;
exec getnextinvoicenumber @CompanyMasterFK = @CompanyMasterFKin
                         ,@NextInvoiceNbr = @NextInvoiceNbrout output
                         ,@EditCount = @EditCountout output
                         ,@MyError = @MyErrorout output
                         ;

select @CompanyMasterFKin
      ,@NextInvoiceNbrout
      ,@EditCountout
      ,@MyErrorout
      ;

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Auguy]

Thank you very much, I will review when I get a few minutes.

Auguy
Sylvania/Toledo Ohio