How to Use SQL Variables in Queries

Learning how to use SQL Variable in queries is a step in the right direction towards making your queries more flexible for you and others to use.

Why “hardcode” when you can parameterize?

In this video and the following article, we’ll take you step-by-step through the process of changing a query’s filter criteria into a SQL variable, then ultimately into a stored procedure.

Edited Transcript:

If you’re just been writing stored procedures or using variables in your scripts to drive your queries, then you’re going to want to watch this video.

Hi, I’m Kris from essentialSQL and today we’re going to look at how we can use SQL variables in queries and then I’m going to ultimately take a query that we put together and then transform it into a stored procedure.  So let’s get started!

What I’m going to do here is open a query tool. I’m going to use Azure Data Studio and I’m going to do a really simple select  from the person table and adventure works database.

Simple Query – No SQL Variables

We’ll select all people from the Person.Person table whose LastName is like Ral:

And here are the results:

select *
from Person.Person
Where LastName like 'Ral%'

Add SQL Variable in Query

What I want to do next is declare a variable to do this same exact thing, but using a variable. I’m going to declare a variable for the LastNamePattern. We’ll make that a varchar, make it 20, and then we will set it equal to ‘Ral%’, which is what we just ran for our query.

All right, and now here’s the cool thing is, I can take this variable and I can plop this right into my query. Right?

--Select with variable in Query
declare @LastNamePattern as varchar(40);
set @LastNamePattern = 'Ral%'

select *
from Person.Person
Where LastName like @LastNamePattern

And what’s going to happen now is when I run my query, LastNamePattern’s going to get set to ‘Ral%’. And then when we run the query, it will use that value in the query itself.

So let’s run this. And you can see it comes back with LastNames starting with Ral.

If I was to change this to like a K here and run it, you see it comes back with a person’s name that starts with Kal. Now our query doesn’t need to change, right? I can just change the variable and make this happen.

You might be asking, “Well, that’s cool, I guess you can use this for a script.” And you can just like this, but what’s even better, is you can make a stored procedure then pass parameters in and do the same thing and make it into a stored procedure.

Creating a Stored Procedure

Let’s now create a stored procedure based on our script.  I’m going to call my procedure PersonSearchLastName, and we will bring in as a parameter, our Last Name pattern.

Because the stored procedure accepts the LastNameParameter we no longer need to declare this as a variable and set its value.

Tip: To get a great introduction, read Ultimate Guide to Stored Procedures

Up to this point we declaring the stored procedure using standard pattern.

Now I will add my query, which is right here. It still uses the sql variable in the query, but now it comes from the stored procedure parameter.

It already references the @LastNamePattern, so there isn’t much to change.

Here is the final stored procedure ready to go!

–Make it a stored procedure and run it!

create procedure PersonSearchLastName(@LastNamePattern as varchar(20))
AS
begin
    select *
    from Person.Person
    Where LastName like @LastNamePattern
end

exec dbo.PersonSearchLastName 'Ral%'

Let me run this command to create the stored procedure object within the database.

Now I can execute it using:

EXEC PersonSearchLastName 'Ral%'

This passes in the last name pattern. The following diagram show how this works:

We  could even do cool things like searching for ‘%ert%’.  Let’s see if there’s anybody that has like ert in the middle of their name.   We can run

EXEC PersonSearchLastName '%ert%'

and you’ll see here where it’s coming back with people like Lockert, Roberts. So ert is in the middle. I have a pattern.

Hopefully, you now see now that you can use this one stored procedure and then just pass in many different types of patterns to it, to find names back.

Conclusion

Adding variable to your queries isn’t difficult.  If you followed our evolution, you saw how we took a simple query, tested its results, and then step-by-step “changed” it into a query within a stored procedure: