Transact SQL (T-SQL) has two main wait of passing data, variables, and parameters. A variable is an object in T-SQL batches and scripts that can old a value. After you define a variable, you can SET the variable or get the value from a variable at any point after the declaration.

Useless trivia: You can only have 10,000 variables defined in a single batch.

If you’re using that many, seriously, you’re doing something wrong.

A parameter is an object like a variable, except it’s used to pass data t or from a stored PROCEDURE.

Let’s dig in a little further.

Variables

Like I mentioned before, before you can use the variable, you have to DECLARE it. Delcaring a variable does three things, it assigns a name to the variable, a data type (it needs to be one that your version T-SQL recognizes), and sets the initial value to NULL. Check out the following example:

DECLARE @variableName int

New to Microsoft SQL Server 2008, you can now declare multiple variables at once. About time, right?

DECLARE @firstName VARCHAR(50), @middleInitial VARCHAR(1), @lastName VARCHAR(50)

Variable Scope

Scope refers the the range of your code where something can reference (or “see”) something. In our case we’re talking about the scope of your variable. The variable will remain in scope as long as you stay in the same code, using the same connection, and don’t interrupt your code with a “GO” statement. Using GO “ends” your current script, and “begins” a new script or batch. If this doesn’t make sense, try running the following code on your machine.

DECLARE @testVariable INT
SET @testVariable = 1
GO
SELECT @testVariable

Running the command should result in

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@testVariable".

That’s because the variable, @testVariable, is not longer within the scope of the script. After we ran the GO statement, the variable is gone.

Setting a variable’s value

There are two ways to SET a value for a variable.

DECLARE @testVariable INT
SET @testVariable = 1

You can simply call the SET command, and set the value with = someValue. Or you can use SELECT to get a value from a table.

USE AdventureWorks
GO
DECLARE @EmpIDVariable int

SELECT @EmpIDVariable = MAX(EmployeeID)
FROM HumanResources.Employee

Be careful using the SELECT method. Your query could return more than one value, or it could return NO values. Either way you wouldn’t get the value you expect. Make sure you test your code well, or use error handling techniques when using SELECT to set a variable you will be using.

Parameters

Now that you know a bit more about variables, let’s dive into Parameters. If you’ve used any stored procedures, you already should understand input parameters. You use them to pass data to the PROCEDURE. What you may not be aware of is you can also create parameters to get data out of that PROCEDURE. Take a look at the example below.

CREATE PROCEDURE addition @input1 INT, @input2 INT, @result INT OUTPUT
AS
	SET @result = @input1 + @input2

After that procedure is created, we can test it out using the following:

DECLARE @testResult int
EXEC addition 1, 1, @testResult OUTPUT
SELECT @testResult	as testResult

Please notice the OUPUT is declared at EXEC too, without it, you’ll get NULL in your results.

Now you know a bit more about variables. Do you have any questions? Let me know!