Get Next number from Stored Procedure

Hi,

I am working on the following Store Procedure in MSSQL to INSERT, UPDATE, SELECT & DELETE from a table.  I currently have 2 stored procedures which I think I could merge into one, but can't see how to go about it.  To make things simpler and concise, how would I add the next WO number (which I generate in the second stored procedure) into the first Stored Procedure and make it available as an OUTPUT variable to pass back to my form?  

ALTER PROCEDURE [dbo].[p_CreateWO]

(

    @WOID         INTEGER,
	@CustomerID  INTEGER,
	@DeptID	INTEGER,
	@SiteID INTEGER,
	@WODate DATE,
	@WODueDate DATE,
	@WONumber nvarchar(255),
	@WORequestedByID INTEGER,
	@WOPreparedByID INTEGER,
	@WONotes nvarchar(MAX),
	@WOTotalVAT INTEGER,
	@WOTotalPrice INTEGER,
	@WOSignedForID INTEGER,
	@WODeliveryDate Date,
	@WOCompletedDate Date,
	@StatementType nvarchar(20) = '' 
  )

 

AS

BEGIN

IF @StatementType =  'Insert'

BEGIN

insert into tblWorkOrderHead (CustomerID, DeptID, SiteID, WODate, WODueDate, WONumber, WORequestedByID, WOPreparedByID, WONotes, WOTotalVAT, WOTotalPrice, WOSignedForID, WODeliveryDate, WOCompletedDate) values( @CustomerID, @DeptID, @SiteID, @WODate, @WODueDate, @NextWO, @WORequestedByID, @WOPreparedByID, @WONotes, @WOTotalVAT, @WOTotalPrice, @WOSignedForID, @WODeliveryDate, @WOCompletedDate)    

END 

 

IF @StatementType =  'Select' 

BEGIN

select * from tblWorkOrderHead WHERE WOID = @WOID

END  

 

IF @StatementType =  'Update' 

BEGIN

UPDATE tblWorkOrderHead SET

            DeptID = @DeptID, SiteID = @SiteID, WODate = @WODate, WODueDate = @WODueDate, WORequestedByID = @WORequestedByID, WOPreparedByID = @WOPreparedByID, WONotes = @WONotes, WOTotalVAT = @WOTotalVAT, WOTotalPrice = @WOTotalPrice, WOSignedForID = @WOSignedForID, WODeliveryDate = @WODeliveryDate, WOCompletedDate = @WOCompletedDate

      WHERE WOID = @WOID

END 

 

else IF @StatementType = 'Delete'

BEGIN

DELETE FROM tblWorkOrderHead WHERE WOID = @WOID

END

 end

Open in new window


My Stored Procedure to get the Next WO number is:

ALTER Procedure [dbo].[p_GetNewWONumber]

(@NextWO INT OUT
)
AS
BEGIN

SET NOCOUNT ON;

SELECT @NextWO = COALESCE(MAX(WONumber), 0) +1
FROM tblWorkOrderHead

RETURN @NextWO

END

Open in new window

LVL 1
anthonytrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
If don't need to return @NextWO since it's an OUTPUT parameter.
In the main SP you just need to declare the variable and call the SP to get the next value:
DECLARE @NextWO INT

exec [dbo].[p_GetNewWONumber] @NextWO

Open in new window

Now you have the next value stored in the variable and you can use it.
0
anthonytrAuthor Commented:
Thanks, when the new record is added, I do need to pass the @NextWO back to the user and add it to the form.

A
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can return values from a stored procedure:
ALTER PROCEDURE [dbo].[p_CreateWO]

(

    @WOID         INTEGER,
	@CustomerID  INTEGER,
	@DeptID	INTEGER,
	@SiteID INTEGER,
	@WODate DATE,
	@WODueDate DATE,
	@WONumber nvarchar(255),
	@WORequestedByID INTEGER,
	@WOPreparedByID INTEGER,
	@WONotes nvarchar(MAX),
	@WOTotalVAT INTEGER,
	@WOTotalPrice INTEGER,
	@WOSignedForID INTEGER,
	@WODeliveryDate Date,
	@WOCompletedDate Date,
	@StatementType nvarchar(20) = '' 
  )

 RETURNS INT

AS

    DECLARE @NextWO INT

    exec [dbo].[p_GetNewWONumber] @NextWO

    IF @StatementType =  'Insert'
    BEGIN
        insert into tblWorkOrderHead (CustomerID, DeptID, SiteID, WODate, WODueDate, WONumber, WORequestedByID, WOPreparedByID, WONotes, WOTotalVAT, WOTotalPrice, WOSignedForID, WODeliveryDate, WOCompletedDate) 
        values( @CustomerID, @DeptID, @SiteID, @WODate, @WODueDate, @NextWO, @WORequestedByID, @WOPreparedByID, @WONotes, @WOTotalVAT, @WOTotalPrice, @WOSignedForID, @WODeliveryDate, @WOCompletedDate)    
       
        RETURN @NextWO
    END 

(...)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I am working on the following Store Procedure in MSSQL to INSERT, UPDATE, SELECT & DELETE from a table
Ok, I'll bite.  Why's that?  
  • It is not considered best practice to have different return sets based on passed parameters,
  • If you're going to DELETE then all that is needed to pass is the primary key column @WOID, not any of the others.
  • In the future you may wish to separate security of who can only SELECT vs. those that can do all.
  • Naming an SP with 'create' and it having the ability to do more than that is misleading.  Change the name.

Also do your company a favor and add some meaningful code comments, as nothing's more humbling that looking at code six months after it was created and wondering 'What the eff was that person thinking??'

>exec [dbo].[p_GetNewWONumber] @NextWO
What's the purpose of this?  If it's to get the max PK and then add one, that would better be handled using an IDENTITY field.
0
anthonytrAuthor Commented:
Hi,

I'm doing something wrong here, it's not passing the new @WONumber in.

ALTER PROCEDURE [dbo].[p_CreateWO]

(

    @WOID         INTEGER,
	@CustomerID  INTEGER,
	@DeptID	INTEGER,
	@SiteID INTEGER,
	@WODate DATE,
	@WODueDate DATE,
	@WONumber nvarchar(255),
	@WORequestedByID INTEGER,
	@WOPreparedByID INTEGER,
	@WONotes nvarchar(MAX),
	@WOTotalVAT INTEGER,
	@WOTotalPrice INTEGER,
	@WOSignedForID INTEGER,
	@WODeliveryDate Date,
	@WOCompletedDate Date,
	@StatementType nvarchar(20) = ''
	
  )

AS

    DECLARE @NextWO INT

    exec [dbo].[p_GetNewWONumber] @NextWO

    IF @StatementType =  'Insert'
    
	BEGIN

insert into tblWorkOrderHead (CustomerID, DeptID, SiteID, WODate, WODueDate, WONumber, WORequestedByID, WOPreparedByID, WONotes, WOTotalVAT, WOTotalPrice, WOSignedForID, WODeliveryDate, WOCompletedDate) values( @CustomerID, @DeptID, @SiteID, @WODate, @WODueDate, @NextWO, @WORequestedByID, @WOPreparedByID, @WONotes, @WOTotalVAT, @WOTotalPrice, @WOSignedForID, @WODeliveryDate, @WOCompletedDate)    

END 

 

IF @StatementType =  'Select' 

BEGIN

select * from tblWorkOrderHead WHERE WOID = @WOID

END  

 

IF @StatementType =  'Update' 

BEGIN

UPDATE tblWorkOrderHead SET

            DeptID = @DeptID, SiteID = @SiteID, WODate = @WODate, WODueDate = @WODueDate, WORequestedByID = @WORequestedByID, WOPreparedByID = @WOPreparedByID, WONotes = @WONotes, WOTotalVAT = @WOTotalVAT, WOTotalPrice = @WOTotalPrice, WOSignedForID = @WOSignedForID, WODeliveryDate = @WODeliveryDate, WOCompletedDate = @WOCompletedDate

      WHERE WOID = @WOID

END 

 

else IF @StatementType = 'Delete'

BEGIN

DELETE FROM tblWorkOrderHead WHERE WOID = @WOID

END

Open in new window

0
anthonytrAuthor Commented:
Hi Jim

I'm new to StoredProcedures.

It is not considered best practice to have different return sets based on passed parameters

Don't really know what you mean by this sorry

If you're going to DELETE then all that is needed to pass is the primary key column @WOID, not any of the others.


I thought I was only asking for the @WOID in the delete statement.  Is this not what's happening?

In the future you may wish to separate security of who can only SELECT vs. those that can do all
How would I control this in the FE?  Currently I would manage who can DELETE/UPDATE/VIEW in the FE with VBA.

Do your company a favor and add some meaningful code comments, as nothing's more humbling that looking at code six months after it was created and wondering 'What the eff was that person thinking??'

Opps, I thought I was being good with the (IF @StatementType =  'Insert')
0
anthonytrAuthor Commented:
Hi Jim,

I missed one of your questions - sorry:

>exec [dbo].[p_GetNewWONumber] @NextWO
What's the purpose of this?  If it's to get the max PK and then add one, that would better be handled using an IDENTITY field.

NextWO is not a PK, WOID is the PK.  As WONumber is what the users see, I didn't think is was best practice to use the PK as a user field (its just used to facilitate PK/FK relationships).
0
anthonytrAuthor Commented:
Great - thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.