Z Code
T-SQL | COMBINE UPDATE AND INSERT STATEMENTS
Listing 5. This procedure demonstrates a technique for combining an Insert statement and an Update statement into a single stored procedure.
CREATE PROCEDURE STDA.CustomersUpdate
@CustomerID NCHAR(5),
@CompanyName NVARCHAR(40),
@ContactName NVARCHAR(30),
@ContactTitle NVARCHAR(30),
@Address NVARCHAR(60),
@City NVARCHAR(15),
@Region NVARCHAR(15),
@PostalCode NVARCHAR(10),
@Country NVARCHAR(15),
@Phone NVARCHAR(24),
@Fax NVARCHAR(24)
AS
IF EXISTS (SELECT * FROM Customers
WHERE CustomerID = @CustomerID)
BEGIN
UPDATE Customers
SET
CompanyName = @CompanyName,
ContactName = @ContactName,
ContactTitle = @ContactTitle,
Address = @Address,
City = @City,
Region = @Region,
PostalCode = @PostalCode,
Country = @Country,
Phone = @Phone,
Fax = @Fax
WHERE CustomerID = @CustomerID
END ELSE BEGIN
INSERT INTO Customers
(
CustomerID, CompanyName, ContactTitle,
Address, City, Region,
PostalCode, Country, Phone,
Fax
) VALUES (
@CustomerID, @CompanyName, @ContactTitle,
@Address, @City, @Region,
@PostalCode, @Country, @Phone,
@Fax
)
END
RETURN @@ROWCOUNT
GO
test of miscellaneous