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
- Using Variables in stored procedures
For the examples we will create the following table named "SomeTable" with an
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
SET we can easily declare a new variable and assign a value to it. We can then use the variable as part of a
DECLARE @Id INT; SET @Id = 1; SELECT * FROM SomeTable WHERE Id = @Id;
Instead of using two lines of code we can merge the
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 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!