MSSQL - How to use variables in SQL statements

on MSSQL

This post will demonstrate how to SELECT from a MSSQL table using a variable. There will be three examples:

  • Simple select statement
  • Setting a variable from another SELECT statement
  • Using Variables in stored procedures

For the examples we will create the following table named "SomeTable" with an INT as PRIMARY KEY and a VARCHAR(MAX) as the only other column:

CREATE TABLE SomeTable(
  Id INT IDENTITY(1,1) PRIMARY KEY (ID) NOT NULL,
  SomeText VARCHAR(MAX) NOT NULL
)

We will insert a single row which will get the Id 1 due to the IDENTITY key word used on the Id column:

INSERT INTO [dbo].[SomeTable] ([SomeText]) VALUES ('Some text');

Let us get started!

Creating a simple query with a variable

Using DECLARE and SET we can easily declare a new variable and assign a value to it. We can then use the variable as part of a SELECT statement:

DECLARE @Id INT;
SET @Id = 1;

SELECT * FROM SomeTable WHERE Id = @Id;

Instead of using two lines of code we can merge the DECLARE and SET together in the following way:

DECLARE @Id INT = 1;
SELECT * FROM SomeTable WHERE Id = @Id;

The above is much more concise.

Setting a Variable to the output of a SELECT statement

Sometimes you want to move a value from a table cell into a variable, you can easily do this by just selecting it:

DECLARE @Id INT = (SELECT TOP 1 Id FROM SomeTable);
SELECT * FROM SomeTable WHERE Id = @Id;

In the above we declare the variable @Id and assign it a value from SomeTable. Note: the above is a silly example as there is only one row in the table!

Using parameters (variables) for Stored procedures

You can use parameters when creating stored procedures. In the below I have wrapped one of the previous queries in a stored procedure where the @Id is given as an input:

GO
CREATE PROCEDURE GetFromSomeTableById
(
   @Id INT
)
AS
BEGIN
SET NOCOUNT ON
 
SELECT * FROM SomeTable WHERE Id = @Id;
 
END

This can then be invoked using the EXEC keyword:

EXEC GetFromSomeTableById @Id = 1

That is all

I hope you found this helpful as a quick look up, let me know in the comments down below what you think!