anthonytr
asked on
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?
My Stored Procedure to get the Next WO number is:
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
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
ASKER
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
A
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>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?
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.
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.
ASKER
Hi,
I'm doing something wrong here, it's not passing the new @WONumber in.
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
ASKER
Hi Jim
I'm new to StoredProcedures.
Don't really know what you mean by this sorry
I thought I was only asking for the @WOID in the delete statement. Is this not what's happening?
Opps, I thought I was being good with the (IF @StatementType = 'Insert')
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 allHow 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')
ASKER
Hi Jim,
I missed one of your questions - sorry:
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).
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).
ASKER
Great - thank you!
In the main SP you just need to declare the variable and call the SP to get the next value:
Open in new window
Now you have the next value stored in the variable and you can use it.