INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Select and Update in One Proc, Maybe a CTE?

Select and Update in One Proc, Maybe a CTE?

(OP)
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.

CODE

--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

RE: Select and Update in One Proc, Maybe a CTE?

try this way

CODE

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Select and Update in One Proc, Maybe a CTE?

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

Auguy
Sylvania/Toledo Ohio

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close