Stored Procedures

  • I have created three tables in my database and for each table I have created three stored procedures, one to add, one to edit and one to delete. Is there any way to create a 'class' for the stored procedures so that instead of having three separate stored procedures per table I could add them to a single one and call whatever action I want to perform when I call the main stored procedure?

    I hope this makes sense!!

  • There isn't a "class" in T-SQL but you could create one SP and just pass in an action field that states whether you are to add, edit or delete?

    He shoots! He misses?

  • Hi Rayola,

    Could you provide an example of the SP you are describing please?

  • ok how about

    CREATE PROCEDURE dbo.pUpdateStock

    @StockID int = NULL,

    @StockName varchar(1000) = NULL,

    @Action = 0

    AS

    IF @Action = 0 --Insert

    INSERT INTO STOCK (stock_name) VALUES (@StockName)

    ELSE IF @Action = 1 AND @StockID IS NOT NULL --edit

    UPDATE STOCK SET stock_name = @StockName WHERE stock_id = @StockID

    ELSE IF @Action = 2 AND @StockID IS NOT NULL

    --delete

    DELETE FROM STOCK WHERE stock_id = @StockID

    would this be any good?

    He shoots! He misses?

  • For maintainable purpose, you can separate the action to three "class", such as insert, update and delete called by a "super-class". For example,

    CREATE PROCEDURE dbo.pUpdateStock

    @StockID int = NULL,

    @StockName varchar(1000) = NULL,

    @Action = 0

    AS

    IF @Action = 0 --Insert

    EXEC dbo.insertStock @StockID, @StockName

    ELSE IF @Action = 1 AND @StockID IS NOT NULL --edit

    EXEC dbo.editStock @StockID, @StockName

    ELSE IF @Action = 2 AND @StockID IS NOT NULL

    --delete

    EXEC dbo.deleteStock @StockID, @StockName

     

     



    Regards,
    kokyan

  • That's perfect! Thank you both for your help!

  • Typically I only combine the insert and update into a single sp, since the parameter list for the delete usually differs greatly.  e.g. passing zero or a valid RecordID to insert or update respectively, and returning output value greater than zero to indicate success.

    CREATE PROCEDURE [usp_UpdateCustomer]

    @RecordID int,

    @MRPNumber   nvarchar(50),

    @CompanyName  nvarchar(50),

    @ContactName  nvarchar(50),

    @ContactTitle  nvarchar(50),

    @Address  nvarchar(50) ,

    @City   nvarchar(50),

    @Region  nvarchar(50),

    @PostalCode  nvarchar(10),

    @Country  nvarchar(50),

    @Phone  nvarchar(15),

    @Fax   nvarchar(15),

    @Email   nvarchar(50),

    @CreditTerms  nvarchar(50),

    @CreditLimit  nvarchar(50),

    @CreditNotes  ntext,

    @RepName  nvarchar(50),

    @RepID  nvarchar(50),

    @RepPercent  nvarchar(10),

    @retval   int OUTPUT

    AS

    DECLARE @user-id nvarchar(50)

    DECLARE @ReturnValue int

     

    SET @user-id  = (SELECT SYSTEM_USER)

    IF EXISTS(SELECT RecordID FROM Customers WHERE RecordID = @RecordID)

     BEGIN

       UPDATE Customers SET MRPNumber = @MRPNumber, CompanyName = @CompanyName,

        ContactName = @ContactName, ContactTitle = @ContactTitle, Address = @Address,

        City = @City, Region = @Region, PostalCode = @PostalCode, Country = @Country,

        Phone = @Phone, Fax = @Fax, Email = @Email, CreditTerms = @CreditTerms,

        CreditLimit = @CreditLimit, CreditNotes = @CreditNotes, RepName = @RepName,

        RepID = @RepID, RepPercent = @RepPercent, LastUserID = @user-id,

        LastAccessed = GETDATE()  WHERE RecordID = @RecordID

      

      SELECT @ReturnValue = @@ROWCOUNT

      IF (@@Error <> 0)

         SELECT @retval = 0

       ELSE

         SELECT @retval =  @ReturnValue

     END

    ELSE

     BEGIN

       INSERT INTO Customers (MRPNumber, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode,

        Country, Phone, Fax, Email, CreditTerms, CreditLimit, CreditNotes, RepName, RepID, RepPercent, LastUserID,    LastAccessed)  VALUES (@MRPNumber, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region,

         @PostalCode, @Country, @Phone, @Fax, @Email, @CreditTerms, @CreditLimit, @CreditNotes,

         @RepName, @RepID, @RepPercent, @user-id, GETDATE())

       

      SELECT @ReturnValue = SCOPE_IDENTITY()  

       IF (@@Error <> 0)

         SELECT @retval = 0

       ELSE

         SELECT @retval =  @ReturnValue

     END

    GO

  • statements like this :

     SELECT @ReturnValue = @@ROWCOUNT

      IF (@@Error <> 0)

     will ALWAYS exectute the else part of the IF because is just checking the previous statement instead of the DML

    the correct way is:

     

    Select @ReturnValue = @@ROWCOUNT, @Err = @@Error

    IF @Err <> 0

     ....

     

    HTH


    * Noel

  • Yes.

    I believe this will fix the problem?

     

    CREATE PROCEDURE [usp_UpdateCustomer]

    @RecordID int,

    @MRPNumber   nvarchar(50),

    @CompanyName  nvarchar(50),

    @ContactName  nvarchar(50),

    @ContactTitle  nvarchar(50),

    @Address  nvarchar(50) ,

    @City   nvarchar(50),

    @Region  nvarchar(50),

    @PostalCode  nvarchar(10),

    @Country  nvarchar(50),

    @Phone  nvarchar(15),

    @Fax   nvarchar(15),

    @Email   nvarchar(50),

    @CreditTerms  nvarchar(50),

    @CreditLimit  nvarchar(50),

    @CreditNotes  ntext,

    @RepName  nvarchar(50),

    @RepID  nvarchar(50),

    @RepPercent  nvarchar(10),

    @retval   int OUTPUT

    AS

    DECLARE @user-id nvarchar(50)

     

    SET @user-id  = (SELECT SYSTEM_USER)

    IF EXISTS(SELECT RecordID FROM Customers WHERE RecordID = @RecordID)

     BEGIN

       UPDATE Customers SET MRPNumber = @MRPNumber, CompanyName = @CompanyName,

        ContactName = @ContactName, ContactTitle = @ContactTitle, Address = @Address,

        City = @City, Region = @Region, PostalCode = @PostalCode, Country = @Country,

        Phone = @Phone, Fax = @Fax, Email = @Email, CreditTerms = @CreditTerms,

        CreditLimit = @CreditLimit, CreditNotes = @CreditNotes, RepName = @RepName,

        RepID = @RepID, RepPercent = @RepPercent, LastUserID = @user-id,

        LastAccessed = GETDATE()  WHERE RecordID = @RecordID

     

      IF (@@Error <> 0)

         SELECT @retval = 0

       ELSE

         SELECT @retval =  @@ROWCOUNT

     END

    ELSE

     BEGIN

       INSERT INTO Customers (MRPNumber, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode,

        Country, Phone, Fax, Email, CreditTerms, CreditLimit, CreditNotes, RepName, RepID, RepPercent, LastUserID,    LastAccessed)  VALUES (@MRPNumber, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region,

         @PostalCode, @Country, @Phone, @Fax, @Email, @CreditTerms, @CreditLimit, @CreditNotes,

         @RepName, @RepID, @RepPercent, @user-id, GETDATE())

      

       IF (@@Error <> 0)

         SELECT @retval = 0

       ELSE

         SELECT @retval =  SCOPE_IDENTITY()  

     END

    GO

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply