DECLARE variable SQL Declare table MySQL

How do you DECLARE a temp variable in SQL?

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:

What is a Variable in SQL Server?
Variable Types in SQL: Local, Global
How to DECLARE a variable in SQL
Assigning a value to SQL Variable
During variable declaration using DECLARE keyword
Using SQL SET VARIABLE
Using SQL SELECT VARIABLE
Other SQL Variable Examples
Interesting Facts About SQL Server Variables!

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.

Below figure explain two types of variable available in MS SQL server.

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:

DECLARE @Local_Variable SET @Local_Variable =

Query:

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

Example: Assign a value to multiple variables using SET.

Syntax:

DECLARE @Local_Variable _1 , @Local_Variable_2 , SET @Local_Variable_1 = SET @Local_Variable_2 =

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:

DECLARE @Local_Variable_1 , @Local_Variable_2 ,SET @Local_Variable_1 = [SELECT from where ]

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:

DECLARE @LOCAL_VARIABLE SELECT @LOCAL_VARIABLE =

Query:

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

Example: Assigning a value to multiple variable using SELECT

Syntax:

DECLARE @Local_Variable _1 , @Local_Variable _2 ,SELECT @Local_Variable _1 = , @Local_Variable _2 =

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:

DECLARE @Local_Variable_1 , @Local_Variable _2 ,SELECT @Local_Variable _1 = [SELECT from where ]

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:

DECLARE @Local_Variable _1 , @Local_Variable _2 ,SELECT @Local_Variable _1 = from where

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

How do you DECLARE a temp?

The Syntax to create a Temporary Table is given below:.

To Create Temporary Table: CREATE TABLE #EmpDetails [id INT, name VARCHAR[25]].

To Insert Values Into Temporary Table: INSERT INTO #EmpDetails VALUES [01, 'Lalit'], [02, 'Atharva'].

To Select Values from Temporary Table: SELECT * FROM #EmpDetails..

Result: id. name. Lalit..

How do I DECLARE a variable in a SQL table?

To declare a table variable, you use the DECLARE statement as follows:.

DECLARE @table_variable_name TABLE [ column_list ]; ... .

DECLARE @product_table TABLE [ product_name VARCHAR[MAX] NOT NULL, brand_id INT NOT NULL, list_price DEC[11,2] NOT NULL ];.

What is temp table and temp variable in SQL?

Difference Between Temp Table and Table Variable.

How do I create a temp table in SQL query?

To create a temporary table, you must have the CREATE TEMPORARY TABLES privilege. After a session has created a temporary table, the server performs no further privilege checks on the table. The creating session can perform any operation on the table, such as DROP TABLE , INSERT , UPDATE , or SELECT .

What is a Variable in SQL Server?

Variable Types in SQL: Local, Global

Local variable:

Global variable:

How to DECLARE a variable in SQL

Examples of Declaring a variable:

Assigning a value to SQL Variable

During variable declaration using DECLARE keyword

Using SQL SET VARIABLE

Using SQL SELECT VARIABLE

Other SQL Variable Examples

Interesting Facts About SQL Server Variables!

Summary:

How do you DECLARE a temp?

How do I DECLARE a variable in a SQL table?

What is temp table and temp variable in SQL?

How do I create a temp table in SQL query?

Bài Viết Liên Quan

Toplist mới

Bài mới nhất

Chủ Đề