Stored Procedures SQL

Hi there! Are you newbie in procedures? It’s okay, in this article I am going to meet you with this powerful tool. We will use T-SQL to write procedures. Okay, let’s get started.

So, what is the procedure?

Stored procedures represent a set of instructions that are executed as a unit. Thus, stored procedures allow you to simplify complex operations and bring them into a single object. You can write procedure once and then reuse it over and over again. In addition, you can pass parameters to the procedure and return values.

Stored procedures also allow you to restrict access to data in tables and thereby reduce the likelihood of deliberate or unconscious unwanted actions in relation to this data.

And another important aspect is performance. Stored procedures are usually faster than regular SQL statements. This is because the procedure code is compiled once the first time it is run, and then saved in the compiled form.

This is a really powerful thing, so everyone should at least know about its existence

How to declare and execute procedure?

I use SQL Server Management Studio and Microsoft SQL Server.

First, we will create a table Products for which we will write procedures.

CREATE TABLE Products
(
 Id INT IDENTITY PRIMARY KEY,
 ProductName NVARCHAR(30) NOT NULL,
 Manufacturer NVARCHAR(20) NOT NULL,
 ProductCount INT DEFAULT 0,
 Price MONEY NOT NULL
);

And insert some data, e.g:

INSERT INTO Products VALUES ('IPhone X', 'Apple', 10000, 999);
INSERT INTO Products VALUES ('IPhone 13', 'Apple', 5000, 1999);
INSERT INTO Products VALUES ('Galaxy S20', 'Samsung', 3000, 500);
INSERT INTO Products VALUES ('Mi 10S', 'Xiaomi', 20000, 299);
INSERT INTO Products VALUES ('Nokia X10', 'Nokia', 7500, 899);

Now we can select data with the following command:

SELECT * FROM Products

Let’s make a procedure out of this:

USE productdb; 
GO 
CREATE PROCEDURE ProductSummary AS 
SELECT * FROM Products 

The CREATE PROCEDURE and CREATE PROC commands are used to create the stored procedure.

Since the CREATE PROCEDURE command must be called in a separate package, the GOcommand is used after the USEcommand that installs the current database to define a new package.

The procedure name must be followed by the ASkeyword.

To separate the body of the procedure from the rest of the script, the procedure code is often placed in a BEGIN… ENDblock:

USE productdb;
GO
CREATE PROCEDURE ProductSummary
AS
BEGIN
 SELECT * FROM Products
END;

After adding the procedure, we can see in the SQL Server Management Studio database node under the Programmability -> Stored Procedures subnode:

Finally, we can run the procedure with a command EXEC or EXECUTEand see the result.

EXEC ProductSummary

To delete the procedure use the following command: DROP PROCEDURE ProductSummary

Creating a SQL Stored Procedure with Parameters

Procedures can take parameters. Parameters can be input — with their help, you can pass some values to the procedure. And also parameters can be output — they allow you to return some value from the procedure.

Let’s create a procedure that will take input parameters and add a new record to the table Products.

USE productdb;
GO
CREATE PROCEDURE AddNewProduct 
 @name NVARCHAR(30), 
 @manufacturer NVARCHAR(20), 
 @productCount INT, 
 @price MONEYAS
BEGIN 
 INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price) VALUES(@name, @manufacturer, @productCount, @price)
END;

After the name of the procedure, there is a list of input parameters, which are defined in the same way as variables — the name begins with the @ symbol, and after the name comes the type of the variable.

The next step is to use this procedure:

USE productdb;
DECLARE @productName NVARCHAR(30), @company NVARCHAR(20);
DECLARE @productCount INT, @price MONEY
SET @productName = 'IPhone 7'
SET @company = 'Apple'
SET @price = 99
SET @productCount = 150
EXEC AddNewProduct @productName, @company, @productCount, @price
SELECT * FROM Products

For another example, consider a procedure that will take one input parameter to filter by manufacturer and output those records:

USE productdb;
GO
CREATE PROCEDURE SelectProductsByManufacturer 
 @manufacturer NVARCHAR(20)
AS
BEGIN 
 SELECT * FROM Products 
 WHERE Manufacturer = @manufacturer
END;
--! Also we can pass parameters directly like below:
EXEC SelectProductsByManufacturer 'Apple'
--! You can also pass values to procedure parameters by name:
EXEC SelectProductsByManufacturer @manufacturer = 'Apple'

What happens if I forget to pass parameters? You are right — error :)
To solve this problem, you can mark the parameter as optional and give it a default value.
Let’s create a procedure with one optional parameter.

USE productdb;
GO
CREATE PROCEDURE AddNewProductWithOptionalCount 
 @name NVARCHAR(20), 
 @manufacturer NVARCHAR(20), 
 @price MONEY, 
 @count INT = 1 --! default value
AS
BEGIN 
 INSERT INTO Products(ProductName, Manufacturer, Price, ProductCount) VALUES(@name, @manufacturer, @price, @count)
END

And execute it:

EXEC AddNewProductWithOptionalCount 'Lenovo K12', 'Lenovo', 399
SELECT * FROM Products

For now for the @count parameter, you do not need to pass a value to the procedure, the default value will be 1.

It is better to place optional parameters at the end of the list of procedure parameters.

Do you still remember that parameters can be output? Output parameters allow you to return some result from the procedure. Output parameters are specified using the OUTPUTkeyword.

Example:

USE productdb;
GO
CREATE PROCEDURE GetMinMaxProductPrice 
 @minPrice MONEY OUTPUT, 
 @maxPrice MONEY OUTPUT
AS
BEGIN
 SELECT @minPrice = MIN(Price), @maxPrice = MAX(Price) 
 FROM Products
END;

When calling the procedure for the output parameters, variables are passed with the OUTPUTkeyword:

USE productdb;
DECLARE @minPrice MONEY, @maxPrice MONEY
EXEC GetMinMaxProductPrice @minPrice OUTPUT, @maxPrice OUTPUT
PRINT 'Min price ' + CONVERT(VARCHAR, @minPrice)
PRINT 'Max price ' + CONVERT(VARCHAR, @maxPrice)

Remember, that you can combine input and output parameters in procedure.

One more feature — change existing procedure via ALTER PROCEDURE

Let’s change this one procedure.

USE productdb;
GO
CREATE PROCEDURE SelectProductsByManufacturer 
 @manufacturer NVARCHAR(20)
AS
BEGIN 
 SELECT * FROM Products 
 WHERE Manufacturer = @manufacturer
END;

To

USE productdb;
GO
ALTER PROCEDURE SelectProductsByManufacturer 
 @manufacturer nvarchar(20)
AS
BEGIN 
 SELECT * FROM Products 
 WHERE Manufacturer LIKE @manufacturer + '%' --! Here we added LIKE operator insted of '='
END;

That’s all, but remember that you can use in procedures next statements:

1. IF ELSE

2. WHILE

3. CONTINUE

4. BREAK

5. TRY CATCH

Thanks for reading this article. Hopefully afterwards you will get along better with the procedures. Happy coding!