Creating a local variable with the name as is also allowed.SET complies with ANSI standards whereas SELECT does not.Table Data type doesn’t allow the use of ‘AS’ during declaration.A local variable can be displayed using PRINT as well as SELECT COMMAND.SELECT * from Guru99 where Tutorial_id = Facts About SQL Server Variables! PRINT this particular case, the variable is EMPTY, i.e., NULL. SELECT = Tutorial_name from Guru99 where Tutorial_ID = 5 PRINT this special case, variable value is set to the value of the last row.

PRINT 2: The query returns multiple rows. SELECT = Tutorial_name from Guru99 where Tutorial_ID = 3 Unlike SET, if the query results in multiple rows then the variable value is set to the value of the last row.Syntax: DECLARE _1, _2 ,SELECT _1 = from where PRINT this particular case, the variable is to EMPTY, i.e., NULL.Įxample 3: Assign a value to a variable with a regular SELECT statement. SELECT = (select Tutorial_name from Guru99 where Tutorial_ID = 5) PRINT 2: When subquery return zero row as a result DECLARE VARCHAR (10) SELECT = (select Tutorial_name from Guru99 where Tutorial_ID = 1) If the query returns zero rows, then the variable is EMPTY, i.e., NULL.Otherwise, the query will throw an error. The scalar query is the query with the result as one row and one column. Syntax: DECLARE, _2 ,SELECT _1 = (SELECT from where ) PRINT Assigning the value to a variable with a Subquery using SELECT Rules: Unlike SET, SELECT can be used to assign a value to multiple variables separated by the comma. PRINT Assigning a value to multiple variable using SELECT Below are different ways to assign a value using SELECT:Įxample: Assigning a value to a variable using SELECT Just like SET, we can also use SELECT to assign values to the variables, post declaring a variable using DECLARE. PRINT this particular case, the variable value is EMPTY, i.e., NULL. SET = (select Tutorial_name from Guru99 where Tutorial_ID = 5) PRINT 2: When subquery returns zero row as a result DECLARE VARCHAR (10) SET = (select Tutorial_name from Guru99 where Tutorial_ID = 3) We will use ‘Guru99’ table in the further tutorialsĮxample 1: When subquery return one row as a result. If the query returns zero rows, then the variable is set to EMPTY, i.e., NULL.Īssumption: Assume that we have the table as ‘Guru99’ with two columns as displayed below:.A scalar query is a query with results as just one row and one column. Syntax: DECLARE, ,SET = (SELECT from where ) PRINT Assigning a value to a variable with a Scalar Subquery using SET Rule: One SET Keyword can be used to assign a value to only one variable. PRINT Assign a value to multiple variables using SET. SET can be used to assign values to the variable, post declaring a variable.Below are the different ways to assign values using SET:Įxample: Assigning a value to a variable using SET Sometimes we want to keep declaration and initialization separate. Here, after datatype we can use ‘=’ followed by value to be assigned Let’s have a look at all three ways in detail: During variable declaration using DECLARE keyword During variable declaration using DECLARE keyword.You can assign a value to a variable in the following three ways : Query: DECLARE two variables DECLARE AS INT, VARCHAR (10) Assigning a value to SQL Variable Query: Without ‘AS’ DECLARE VARCHAR (10)

Set variable mediainfo