Avatar of anthonytr
anthonytr
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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:

1 + Nz(DMax("batch_number", "tbl_batches", "customer_id = '" & Me.customer_id & "'"), 0)

Open in new window


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)
Microsoft SQL Server.NET ProgrammingSQL

Avatar of undefined
Last Comment
anthonytr

8/22/2022 - Mon
Anthony Perkins

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
ste5an

E.g. as SELECT

SELECT  COALESCE(MAX(batch_number), 0) + 1
FROM    tbl_batches
WHERE   customer_id = 123;

Open in new window


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;

Open in new window

ASKER CERTIFIED SOLUTION
Anthony Perkins

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ganapathi

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>

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
anthonytr

ASKER
Hi,

Thanks for the super quick response.  Stupid question, how would I use the returned batch number on my form in vb.net?
Anthony Perkins

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

ASKER
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 :(
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
anthonytr

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

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

Open in new window

anthonytr

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

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

Open in new window


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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SOLUTION
anthonytr

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Anthony Perkins

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.
Anthony Perkins

>>  It hasn't fixed the fact that I still have to pass a value for @NextBatch when I run the SP<<
You don't have to pass the value (and certainly not with a string), just setup the parameter, much the same way you do in .NET.
anthonytr

ASKER
Thank you.  I have changed to ExecuteNonQuery() as you suggested.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
anthonytr

ASKER
.