Trending October 2023 # Sql Variables: Sql Server Declare, Set And Select Variable # Suggested November 2023 # Top 10 Popular

You are reading the article Sql Variables: Sql Server Declare, Set And Select Variable updated in October 2023 on the website Vibergotobrazil.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 Sql Variables: Sql Server Declare, Set And Select Variable

What is a Variable in SQL Server?

In MS SQL, variables are the object which acts as a placeholder to a memory location. Variable hold single data value.

In this tutorial you will learn:

Variable Types in SQL: Local, Global

MS SQL has two types of variables:

Local variable

Global variable.

However, the user can only create a local variable.

Type of Variables in SQL Server

Local variable:

A user declares the local variable.

By default, a local variable starts with @.

Every local variable scope has the restriction to the current batch or procedure within any given session.

Global variable:

The system maintains the global variable. A user cannot declare them.

The global variable starts with @@

It stores session related information.

How to DECLARE a variable in SQL

Before using any variable in batch or procedure, you need to declare the variable.

DECLARE command is used to DECLARE variable which acts as a placeholder for the memory location.

Only once the declaration is made, a variable can be used in the subsequent part of batch or procedure.

TSQL Syntax:

DECLARE { @LOCAL_VARIABLE[AS] data_type [ = value ] }

Rules:

Initialization is an optional thing while declaring.

By default, DECLARE initializes variable to NULL.

Using the keyword ‘AS’ is optional.

To declare more than one local variable, use a comma after the first local variable definition, and then define the next local variable name and data type.

Examples of Declaring a variable:

Query: With ‘AS’

DECLARE @COURSE_ID AS INT;

Query: Without ‘AS’

DECLARE @COURSE_NAME VARCHAR (10);

Query: DECLARE two variables

DECLARE @COURSE_ID AS INT, @COURSE_NAME VARCHAR (10); Assigning a value to SQL Variable

You can assign a value to a variable in the following three ways:

During variable declaration using DECLARE keyword.

Using SET

Using SELECT

Let’s have a look at all three ways in detail:

During variable declaration using DECLARE keyword

T-SQL Syntax:

DECLARE { @Local_Variable [AS] Datatype [ = value ] }

Here, after datatype we can use ‘=’ followed by value to be assigned

Query:

DECLARE @COURSE_ID AS INT = 5 PRINT @COURSE_ID

Using SQL SET VARIABLE

Sometimes we want to keep declaration and initialization separate. SET can be used to assign values to the variable, post declaring a variable.Below are the different ways to assign values using SET:

Example: Assigning a value to a variable using SET

Syntax:

Query:

DECLARE @COURSE_ID AS INT SET @COURSE_ID = 5 PRINT @COURSE_ID

Example: Assign a value to multiple variables using SET.

Syntax:

Rule: One SET Keyword can be used to assign a value to only one variable.

Query:

DECLARE @COURSE_ID as INT, @COURSE_NAME AS VARCHAR(5) SET @COURSE_ID = 5 SET @COURSE_NAME = 'UNIX' PRINT @COURSE_ID PRINT @COURSE_NAME

Example: Assigning a value to a variable with a Scalar Subquery using SET

Syntax:

Rules:

Enclose the query in parenthesis.

The query should be a scalar query. A scalar query is a query with results as just one row and one column. Otherwise, the query will throw an error.

If the query returns zero rows, then the variable is set to EMPTY, i.e., NULL.

Assumption: Assume that we have the table as ‘Guru99’ with two columns as displayed below:

We will use ‘Guru99’ table in the further tutorials

Example 1: When subquery return one row as a result.

DECLARE @COURSE_NAME VARCHAR (10) SET @COURSE_NAME = (select Tutorial_name from Guru99 where Tutorial_ID = 3) PRINT @COURSE_NAME

Example 2: When subquery returns zero row as a result

DECLARE @COURSE_NAME VARCHAR (10) SET @COURSE_NAME = (select Tutorial_name from Guru99 where Tutorial_ID = 5) PRINT @COURSE_NAME

In this particular case, the variable value is EMPTY, i.e., NULL.

Using SQL SELECT VARIABLE

Just like SET, we can also use SELECT to assign values to the variables, post declaring a variable using DECLARE. Below are different ways to assign a value using SELECT:

Example: Assigning a value to a variable using SELECT

Syntax:

Query:

DECLARE @COURSE_ID INT SELECT @COURSE_ID = 5 PRINT @COURSE_ID

Example: Assigning a value to multiple variable using SELECT

Syntax:

Rules: Unlike SET, SELECT can be used to assign a value to multiple variables separated by the comma.

DECLARE @COURSE_ID as INT, @COURSE_NAME AS VARCHAR(5) SELECT @COURSE_ID = 5, @COURSE_NAME = 'UNIX' PRINT @COURSE_ID PRINT @COURSE_NAME

Example: Assigning the value to a variable with a Subquery using SELECT

Syntax:

Rules:

Enclose the query in Parenthesis.

The query should be a scalar query. The scalar query is the query with the result as one row and one column. Otherwise, the query will throw an error.

If the query returns zero rows, then the variable is EMPTY, i.e., NULL.

Reconsider our ‘Guru99’ table

Example 1: When subquery return one row as a result.

DECLARE @COURSE_NAME VARCHAR (10) SELECT @COURSE_NAME = (select Tutorial_name from Guru99 where Tutorial_ID = 1) PRINT @COURSE_NAME

Example 2: When subquery return zero row as a result

DECLARE @COURSE_NAME VARCHAR (10) SELECT @COURSE_NAME = (select Tutorial_name from Guru99 where Tutorial_ID = 5) PRINT @COURSE_NAME

In this particular case, the variable is to EMPTY, i.e., NULL.

Example 3: Assign a value to a variable with a regular SELECT statement.

Syntax:

Rules:

Unlike SET, if the query results in multiple rows then the variable value is set to the value of the last row.

If the query returns zero rows, then the variable is set to EMPTY, i.e., NULL.

Query 1: The query returns one row.

DECLARE @COURSE_NAME VARCHAR (10) SELECT @COURSE_NAME = Tutorial_name from Guru99 where Tutorial_ID = 3 PRINT @COURSE_NAME

Query 2: The query returns multiple rows.

DECLARE @COURSE_NAME VARCHAR (10) SELECT @COURSE_NAME = Tutorial_name from Guru99 PRINT @COURSE_NAME

In this special case, variable value is set to the value of the last row.

Query 3: The query returns zero rows.

DECLARE @COURSE_NAME VARCHAR (10) SELECT @COURSE_NAME = Tutorial_name from Guru99 where Tutorial_ID = 5 PRINT @COURSE_NAME

In this particular case, the variable is EMPTY, i.e., NULL.

Other SQL Variable Examples

Using variable in the query

Query:

DECLARE @COURSE_ID Int = 1 SELECT * from Guru99 where Tutorial_id = @COURSE_ID

Interesting Facts About SQL Server Variables!

A local variable can be displayed using PRINT as well as SELECT COMMAND

Table Data type doesn’t allow the use of ‘AS’ during declaration.

SET complies with ANSI standards whereas SELECT does not.

Creating a local variable with the name as @ is also allowed. We can declare it as, for example: 'DECLARE @@ as VARCHAR (10)'

Summary:

Variables are the object which acts as a placeholder.

Two types of Variable exist: Local and Global

We can assign the variable in the following three ways: While using 1) DECLARE 2) Using SET 3) USING SELECT

You're reading Sql Variables: Sql Server Declare, Set And Select Variable

Update the detailed information about Sql Variables: Sql Server Declare, Set And Select Variable on the Vibergotobrazil.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!