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 @userid nvarchar(50)
DECLARE @ReturnValue int
SET @userid = (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 = @userid,
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, @userid, 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 @userid nvarchar(50)
SET @userid = (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 = @userid,
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, @userid, 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