Contents
What is a stored procedure?
First of all, let’s say you always have to do the same query and you are tired of doing over and over the same thing.
Luckily for you, almost every SQL server has a solution.
The solution is called stored procedures, which are routines that are saved in the database. The way we do this and the way we execute the routines might vary from SQL to SQL interpreter (For instance, some of them will use EXEC like SQL Server, and others will use CALL to execute the routine you have saved).
Preparing our database for testing
Ok, enough of chip-chat, let’s put our hands to work. First of all, you will need your environment created. We have already talked about this in this post. You will also need this post in order to execute commands on your new and beautiful SQL server installation.
After doing this we will use SSMS SQL Server and create a new database (Clicking on databases – New Databases)
I create mine with the name “test”, but you can put whatever you want.
The database will appear in your left panel, now you should click on it and create a table
My table has three columns, name (varchar (30)), surname (varchar (40)) and number (int)
Once you have created your table (In my case is also named test), we can continue with the tutorial.
Creating our first Stored Procedure – Inserting new registries
Now let’s create our first stored procedure, right click on your database and click “new query”
Let’ start by adding people to this table we created with a stored procedure.
CREATE PROCEDURE dbo.add_people (
@firstname varchar(30),
@lastname varchar(40),
@number int)
AS
SET NOCOUNT ON;
INSERT INTO dbo.test (name,surname,number)
VALUES (@firstname,@lastname,@number);
Ok, let’s see what we have done here.
First of all, we create our procedure called dbo.add_people with three parameters. These parameters will be consistent with our table.
SET NOCOUNT ON It’s a line that will prevent the procedure from counting the rows.
After this, we will do our query like we would do it outside from our procedure, but the difference will be that we will put our parameters as the values that we will insert.
Now to run it:
EXEC dbo.add_people 'John', 'Doe',0;This will add people to our table using a stored procedure.
Another example – Updating with a stored procedure
Now let’s say you have to change the number value of a user.
Let’s do it with another procedure!
CREATE PROCEDURE dbo.change_number (
@firstname varchar(30),
@lastname varchar(40),
@number int)
AS
SET NOCOUNT ON;
UPDATE dbo.test set number=@number
WHERE name=@firstname and surname=@lastname;And to run it
EXEC dbo.change_number 'John', 'Doe', 10;Listing people with a stored procedure
By now you might see some similarities, now let’s list every registry that start’s with J and the last name starts with D
CREATE PROCEDURE dbo.list_by_like (
@firstname varchar(30),
@lastname varchar(40))
AS
SET NOCOUNT ON;
SELECT * FROM dbo.test WHERE name LIKE '%'+@firstname+'%' and surname LIKE '%'+@lastname+'%';
And let’s run it:
EXEC dbo.list_by_like 'J', 'D';More complex – WHILE inside stored procedure and creating our query inside the stored procedure
Ok, by now we have seen how to make a simple stored procedure BUT what about a more complex one? Let’s say we have only one parameter, ”J,D’ and we want to split it and find the registries that match (Using J for the name and D for the last name)
CREATE PROCEDURE dbo.listing_with_one_parameter (
@param varchar(30))
AS
SET NOCOUNT ON;
BEGIN
DECLARE @sql_string nvarchar(200);
DECLARE @n int;
DECLARE @p int;
DECLARE @gname nvarchar(10);
DECLARE @glastname nvarchar(10);
SET @n=(SELECT COUNT(*) FROM STRING_SPLIT(@param,','));
SET @p=0;
WHILE @p < @n
BEGIN
IF @p=0
BEGIN
SET @gname = '%'+SUBSTRING(@param, 0, CHARINDEX(',',@param,0))+'%';
END;
IF @p=1
BEGIN
SET @glastname='%'+SUBSTRING(@param, CHARINDEX(',',@param,0)+1, LEN(@param))+'%';
END;
SET @p=@p+1;
END;
SET @sql_string = 'SELECT * FROM dbo.test WHERE name LIKE @name AND surname LIKE @surname';
EXECUTE sp_executesql @sql_string, N'@name nvarchar(10),@surname nvarchar(10)', @gname, @glastname;
END;Ok, let’s see what we did here…
First we declare our variables inside our procedure, we will use some of them for counting and some of them to make our query.
As we are using WHILE and IF in our procedure, we should establish the limits of every section of it using BEGIN and END;
We will set the variable @n as a query over our param (Ok, this is kind of complicated, but to make a long story short, we are counting in how many parts will the param be divided if we use ‘,’ as a delimiter).
Then we will enter on a while loop using @p as a counter and @n as our top number.
If @p is 0 then we will use the first part of the parameter as the name, we will wrap it with ‘%’ before and after the part of the parameter (This is because we will use a LIKE at the last query).
If @p is 1 then we will use the second part of the parameter as the last name, we will also wrap it with ‘%’.
At last, we will make our query and we will use a trick, a built-in procedure of SQL Server that makes a string into a SQL statement. This built-in procedure is called sp_executesql and in it, we must declare our query as the first parameter, and we will have to declare our variables indicating which of our variables are each of them (Every parameter of the procedure must be a nvarchar, we can declare any type of variables inside it).
Now let’s run it!
EXEC dbo.listing_with_one_parameter 'J,D'
This would be our result.
Conclusion
We have now seen in more detail how to use stored procedures in Microsoft SQL Server.
You can find more information about this in Microsoft SQL Server Docs.
I have also done another post about stored procedures where I compare them with PLSQL stored procedures, you can check it out here!
See you in the next post!
