SQL Variables in Scripts, Functions, Stored Procedures, SQLCMD and More

By: Daniel Calbimonte   |   Updated: 2022-03-18   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > TSQL

Problem

Just like with any development language, the use of variables to store and hold values for code is also very helpful when working with the Microsoft SQL Server DBMS. In this tutorial, we will look at various ways to use variables when writing T-SQL, using SSIS, scripts and more.

Solution

In this tutorial, we will cover the following topics related to Transact-SQL variables in SQL Server:

• What are variables?
• What are the types of variables?
• How to declare a variable?
• How to set a variable?
• Why use variables?
• How to use variables in stored procedures?
• How to use variables in functions?
• How to use variables with SQLCMD?
• How to use variables in scripts?
• How to use variables with SSIS?

What are SQL variables?

A variable is a value that can change dynamically in a program. Variables can have different data types and are used in a SQL database to provide more flexibility in the SQL statements instead of hard coding values that are always changing.

What are the types of variables?

Variables can be used for any of the following:

• Numeric (int, smallint, money, decimal, bigint, numeric, bit, smallmoney, tinyint, float, real)
• Date and/or time (date, datetime2, datetime, datetimeoffset, smalldatetime, time)
• Character (char, varchar, text, nchar, nvarchar and ntext)
• Binary like image data types
• XML
• sql_variant which can support different data types
• Uniqueidentifiers
• Spatial geometry and spatial geography variables
• Cursors
• WHERE clause
• and also table variables

How to declare a SQL variable?

The basic variable declaration syntax is the following:

DECLARE @variablename datatype

To declare a variable, we need to use the word DECLARE and then specify the name with the @ prefix and then the data type.

The following example shows how to declare a variable of type smallint.

DECLARE @myvariable smallint

The following example shows how to declare and set the value of the variable with a SELECT statement.

DECLARE @myvariable smallint = 2

SELECT CONCAT('The variable value is', SPACE(1), @myvariable) as myvariable

The result set is:

The following example declares the variable @mytext of type varchar and assigns the value of Hello.

DECLARE @mytext nvarchar = 'Hello'
SELECT @mytext message

Note that the result displayed is just the first letter of the word Hello. This is because by default value of the nvarchar or varchar data type only shows 1 single character. If you need more characters, you need to specify the length. The following example uses a length of 10 characters.

DECLARE @mytext nvarchar(10) = 'Hello'
SELECT @mytext message

The following example shows how to declare a variable of date type and assign a value. We then display the date dd-MM-yyyy format.

DECLARE @mydate datetime = '2020-01-01 5:00:00'

SELECT FORMAT(@mydate, 'dd-MM-yyyy') message

How to set a SQL variable?

You can declare the variable and then assign the value like we did above.

Here is another approach of how to set the variable using two lines of code and the third line is used to display the value.

DECLARE @mydecimal decimal(10,5)
SELECT @mydecimal = 89.9899

SELECT @mydecimal message

This second example assigns the value directly in the DECLARE statement like the examples above did.

DECLARE @mydecimal decimal(10,5) = 89.9899

SELECT @mydecimal message

The third example, shows how to declare the variable and then assign the value to the variable using the SET statement in the second line of code.

DECLARE @mydecimal decimal(10,5)
SET @mydecimal = 89.9899

SELECT @mydecimal message

The results for all the 3 examples will be the same.

Geography Variables

We also have an example to set values for geography variables. SQL Server has geospatial data types and the following example will declare a value named location and assign values.

DECLARE @Location GEOGRAPHY 
SET @Location = geography::STGeomFromText('LINESTRING(47.653 -89.358, 48.1 -89.320, 49.0 -88.28)', 4326)

SELECT @Location

The results are the following.

For more information about Geography and Geometry data types you can read this article: SQL Server Geography and Geometry Data Types.

Table Variables

Another variable type is the table variable. It is like a table, but it is a variable. You use the @ prefix and then it is similar to a static table.

The following example creates a table variable named @TestTabe and then inserts values and the does a select.

DECLARE @TestTable TABLE
(
   ID INT,
   Name NVARCHAR(40)
)

INSERT INTO @TestTable values(1,'John')
 
SELECT * from @TestTable 

For more information about table variables refer to this article: Exploring SQL Server Table Variables.

Why use SQL variables?

Variables in T-SQL allow creating intelligent and dynamic code. It provides more power to the user to get the information in a smart way and to automate tasks and reuse code. All programming languages use variables and T-SQL is not the exception.

How to use variables in stored procedures?

Stored procedures are very popular in SQL Server. The use is more frequent than functions in general because they are very flexible and easy to use.

The following example explains how to create a stored procedure named dbo.uspGetAddress. The input parameter will be @City and the stored procedure shows all the columns of the Person.address table where the city is equal to the city provided. Execute the code in your AdventureWorks database.

CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SELECT * 
FROM Person.Address
WHERE City = @City
GO

In this example, the input parameter value will be New York.

EXEC dbo.uspGetAddress @City = 'New York'

For more information about stored procedure refer to the SQL Server Stored Procedure Tutorial.

How to use variables in SQL functions?

You can also use variables with functions. The following example shows how to create a function that receives the amount of money in USD and receives the exchange rate and calculates the equivalent in euros.

CREATE FUNCTION dbo.euros(@amount decimal(10,5), @rate decimal(10,5))
RETURNS decimal(10,5)
AS
BEGIN
   RETURN @amount*@rate 
END

The following example shows how to invoke function.

SELECT dbo.euros(500,1.13) as euros

For more examples of functions in T-SQL with variables, please refer to SQL Server User Defined Function Example.

How to use variables with SQLCMD?

SQLCMD is the SQL Server command line. If you love to automate tasks using batch files, SQLCMD is the answer. If you are not familiar with SQLCMD, but you want to learn more, the following article will help Introduction to SQL Server's sqlcmd utility.

In a command prompt window, run this command to connect to SQL using your windows credentials.

sqlcmd -E

In the sqlcmd, go to the AdventureWorks database as follows.

1> use adventureworks2019
2> go

The following example will set a variable named tablename with the value of humanresources.department and then will do a select * to that variable.

1> :setvar tablename humanresources.department
1> select * from $(tablename)
2> go

If everything is OK, you will be able to see the data in that table.

How to use variables in scripts?

You can also send variables to SQL scripts. The following example will create a variable named columnname1. We will assign the value of DepartmentID.

For this example, we will create a script named myquery.sql and store it in the sqlcmd folder.

USE AdventureWorks2019
GO
 
SELECT
$(columnname1)
from
[HumanResources].[Department]

Exit sqlcmd if you are still there and in the Windows command prompt run this command.

sqlcmd -v columnname1 =DepartmentID -i c:\sqlcmd\myquery.sql

The command line will run "select departmentid from humanresources.department".

How to use variables with SSIS?

SSIS variables are completely different than the T-SQL variables. SSIS is a UI environment used to integrate data and has its own variables. To see the variables in an SSIS Project, go to Extensions > SSIS >Variables or for earlier VS versions SSIS > Variables.

You can add, remove variables with the variables pane.

There are different types of variables in SSIS like Int32, int64, Object and Sbyte.

The use of the SSIS variables is out of the scope of this article, however here are articles on this topic:

• SSIS Variable and Expression Example - Part 1
• Getting Started with SQL Server Integration Services SSIS Parameters vs. Variables
• Setup Environment Variables in SQL Server Integration Services

Next Steps

If you want to learn more about variables, please refer to the following:

• Dynamic SQL Server Integration Services Workflow Based on Variables
• SQL Server CTE vs Temp Table vs Table Variable Performance Test
• Returning SQL Server stored procedure parameter values to a calling stored procedure
• SQL Variables for Queries and Stored Procedures in SQL Server, Oracle and PostgreSQL
• Exploring SQL Server Table Variables

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips