Creating a stored procedure
Hi,
I'm new to vb.net (having moved from VBA) and I'm in the process of converting a simple database into vb.net using Visual Studio 2013. I have quickly realised that stored procedures are going to be the way forward regarding updating my SQL database and for performing queries on such data.
Currently when I create a new batch associated with a customer in the database I check the tbl_batches table to see if that customer has already had a batch created and if they do I look for the most recent batch created and add 1 to that number and return that to the user. If there are no batches in the the system for the customer then the batch numbering starts at 01.
The VBA code I had for this action was:
For the life of me I have no idea how this would convert into a stored procedure. So that my form field gets filled with either a 01 if there is not batch for the client in the database or returns the most recent addition and adds 1 to this number (advancing the batch number by 1)
I'm new to vb.net (having moved from VBA) and I'm in the process of converting a simple database into vb.net using Visual Studio 2013. I have quickly realised that stored procedures are going to be the way forward regarding updating my SQL database and for performing queries on such data.
Currently when I create a new batch associated with a customer in the database I check the tbl_batches table to see if that customer has already had a batch created and if they do I look for the most recent batch created and add 1 to that number and return that to the user. If there are no batches in the the system for the customer then the batch numbering starts at 01.
The VBA code I had for this action was:
1 + Nz(DMax("batch_number", "tbl_batches", "customer_id = '" & Me.customer_id & "'"), 0)
For the life of me I have no idea how this would convert into a stored procedure. So that my form field gets filled with either a 01 if there is not batch for the client in the database or returns the most recent addition and adds 1 to this number (advancing the batch number by 1)
This will return a result set:
CREATE PROCEDURE usp_GetLastBatchNumber
@customer_id integer -- Change data type as appropriate
AS
SET NOCOUNT ON
SELECT ISNULL(MAX(batch_number), 0) batch_number
FROM tbl_batches
WHERE customer_id = @customer_id
CREATE PROCEDURE usp_GetLastBatchNumber
@customer_id integer -- Change data type as appropriate
AS
SET NOCOUNT ON
SELECT ISNULL(MAX(batch_number), 0) batch_number
FROM tbl_batches
WHERE customer_id = @customer_id
E.g. as SELECT
as procedure:
SELECT COALESCE(MAX(batch_number), 0) + 1
FROM tbl_batches
WHERE customer_id = 123;
as procedure:
CREATE PROCEDURE dbo.p_GetNextBatchNumber @CustomerID INT
AS
SET NOCOUNT ON;
SELECT COALESCE(MAX(batch_number), 0) + 1
FROM tbl_batches
WHERE customer_id = @CustomerID;
RETURN 0;
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Here you go.
CREATE PROCEDURE dbo.SP_GET_USR_BATCH
(
@pCustomerID INT
)
AS
BEGIN
DECLARE @ReturnBatch INT
SELECT @ReturnBatch = 01
IF EXISTS(SELECT 1 FROM tbl_batches WHERE customer_id = @pCustomerID)
BEGIN
UPDATE
tbl_batches
SET
batch_number = batch_number + 1,
@ReturnBatch = batch_number + 1
WHERE
customer_id = @pCustomerID
END
RETURN @ReturnBatch
END
GO
EXEC SP_GET_USR_BATCH <pass_customer_id>
Hi,
Thanks for the super quick response. Stupid question, how would I use the returned batch number on my form in vb.net?
Thanks for the super quick response. Stupid question, how would I use the returned batch number on my form in vb.net?
It depends. With the SQLCommand class, if you use a result set then you use the ExecuteReader or ExecuteScalar method, if it is an OUTPUT parameter (that includes a RETURN) parameter you use the ExecuteNonQuery method.
I have implemented Ganapathi example and have executed it which returns a valid batch_number of either 1 or a +1.
I then need to use this result like...
me.batchnumber.text = (returned value from stored procedure)
This is where I'm falling down :(
I then need to use this result like...
me.batchnumber.text = (returned value from stored procedure)
This is where I'm falling down :(
Hi Ganapathi,
The stored procedure you suggested updates the batch_number in the tbl_batches table where the customerID matches. I don't want this record updated, I just require the next number to be generated and made available to use elsewhere. I have tried taking the UPDATE clause but I can't get the Stored Procedure to work accordingly. Can you help?
Thanks
The stored procedure you suggested updates the batch_number in the tbl_batches table where the customerID matches. I don't want this record updated, I just require the next number to be generated and made available to use elsewhere. I have tried taking the UPDATE clause but I can't get the Stored Procedure to work accordingly. Can you help?
Thanks
This procedure seems to working correctly, but there doesn't seem to be any returned variable I can use to pass the data back to my form. Can anyone help:
CREATE PROCEDURE dbo.p_GetNextBatchNumber @CustomerID Varchar(50)
AS
SET NOCOUNT ON;
SELECT COALESCE(MAX(batch_number), 0) + 1
FROM tbl_batches
WHERE customer_id = @CustomerID;
RETURN 0;
Looks like I'll be awarding points to my self :)
I have been looking more at the stored procedure and thought that this would have been the answer.
It does return the correct new @NextBatch number however, when I execute it it ask for both @CustomerID and @NextBatch as parameters. I thought I had declared @NextBatch as an OUT parameter so why is it asking for this to be used when it runs?
Also, I'm still not getting anywhere regarding getting the RETURNED @NextBatch number to my form.
I have been looking more at the stored procedure and thought that this would have been the answer.
CREATE PROCEDURE dbo.p_GetNextBatchNumber
(
@CustomerID Varchar(50),
@Nextbatch INT OUT
)
AS
Begin
SET NOCOUNT ON;
SELECT @Nextbatch = COALESCE(MAX(batch_number), 0) + 1
FROM tbl_batches
WHERE customer_id = @CustomerID;
return @NextBatch
end
It does return the correct new @NextBatch number however, when I execute it it ask for both @CustomerID and @NextBatch as parameters. I thought I had declared @NextBatch as an OUT parameter so why is it asking for this to be used when it runs?
Also, I'm still not getting anywhere regarding getting the RETURNED @NextBatch number to my form.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
I thought I had declared @NextBatch as an OUT parameter so why is it asking for this to be used when it runs?
As you have discovered, all parameters have to be declared including OUTPUT parameters.
Since you are not reading a result set, but rather retrieving an OUTPUT parameter there is no need to use SqlDataReader. You should be using the ExecuteNonQuery method.
As you have discovered, all parameters have to be declared including OUTPUT parameters.
Since you are not reading a result set, but rather retrieving an OUTPUT parameter there is no need to use SqlDataReader. You should be using the ExecuteNonQuery method.
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Join our community and discover your potential
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.