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