What is the Difference Between Variable and Parameter in SQL

The main difference between variable and parameter in SQL is that the variable helps to hold a single data value of a specific type while the parameter helps to exchange data among stored procedures and functions.

SQL (Structured Query Language) is a computer language for storing, managing and retrieving data in relational databases. RDBMS such as MSSQL uses variables and parameters, and we can reference these anywhere in an SQL procedure statement.

Key Areas Covered

1. What is Variable in SQL
     – Definition, Functionality
2. What is Parameter in SQL
     – Definition, Functionality
3. What is the Difference Between Variable and Parameter in SQL
     – Comparison of Key Differences

Key Terms

Variable, Parameter, SQL

What is Variable in SQL

Variable in SQL is an object that can hold a single data value of a specific type. Furthermore, it is possible to use it as a counter to count the number of iterations in a loop control structure. It also helps to hold a data value to be tested by control of flow statement. Moreover, it helps to save a data value that returns from a stored procedure return code or function return value.

The DECLARE statement helps to initialize a variable. The variable should have a name to identify it. Thus, it starts with a single @ as the first character. Then the programmer has to assign a system-supplied or user-defined data type and a length to that variable. Generally, a variable is used in stored procedures in which it is declared.

An example is as follows. The MyResultis a variable of type int.

DECLARE @MyResultint;

It is also possible to declare multiple variables as follows. It declares three variables: FirstName, LastName and Age.

DECLARE @FirstName varchar (20), @LastName varchar (20), @Age int;

When declaring the variable for the first time, its value is set to NULL. Moreover, to assign a value to a variable, the programmer can use the SET statement.

Use StudentDB;

Go

DECLARE @FirstNamevarchar(20), @Age int;

SET @FirstName = “Ann”;

SET @Age = 20;

SELECT FirstName, LastName, City, Age from StudentDetails

WHERE FirstName = @FirstName or Age = @Age;

GO

The above SQL script declares two variables: FirstName and Age. The SET statements assign values to them. Finally, the select statement helps to obtain the values from the StudentDetails table using those variables.

What is Parameter in SQL

A parameter in SQL helps to exchange data among stored procedures and functions. With the help of input parameters, the caller can pass a data value to the stored procedure or function. While, with the help of output parameters, the stored procedure can pass a data value back to the caller. Moreover, each stored procedure returns an integer return code to the caller. However, if the stored procedure does not explicitly set a value for the return code, it will return 0.

CREAT PROCEDURE MyProcedure @EmployeeID INT, @MaxTotal INT OUTPUT

AS

DECLARE @ErrorSave INT

Set @ErrorSave = 0

Select FirstName, LastName, City FROM HR.table1

WHERE EmployeeID = @EmplyeeID

IF (@@ERROR <>0)

            SET @ErrorSave == @@ERROR

SELECT @MaxTotal = MAX(TotalDue)

FROM Sales.table2;

IF (@@ERROR <>0)

SET @ErrorSave = @@ERROR

RETURN @ErrorSave

GO

The MySample is a procedure that takes one input parameter and returns one output parameter. The select statement is executed using the input parameter @EmployeeID. Then, any non-zero value saves to @ErrorSave. Next, the output parameter @MaxTotal assigns with the MAX(TotalDue). Finally, the 0 will return if SELECT statement has an error. If not, the last error will return.

Difference Between Variable and Parameter in SQL

Definition

A variable in SQL is an object that can hold a single data value of a specific type. In contrast, a parameter in SQL is an object that can exchange data between stored procedures and functions. These definitions explain the fundamental difference between variable and parameter in SQL.

Usage

The main difference between variable and parameter in SQL is their usage. A variable in SQL helps to hold a single data value of a specific type while a parameter in SQL helps to exchange data among functions and stored procedures.

Conclusion

• In conclusion, the main difference between variable and parameter in SQL is that variable helps to hold a single data value of a specific type while parameter helps to exchange data among stored procedures and functions.

Reference:

1. “Variables (Transact-SQL).” Microsoft Docs, Available here.
2. “Parameters.” Microsoft Docs, Available here.

Image Courtesy:

1. “156948” (CC0) via Pixabay