t sql assignment

When to use SET vs SELECT when assigning values to variables in SQL Server

By: Atif Shehzad   |   Comments (13)   |   Related: > TSQL

SET and SELECT may be used to assign values to variables through T-SQL. Both fulfill the task, but in some scenarios unexpected results may be produced. In this tip I elaborate on the considerations for choosing between the SET and SELECT methods for assigning a value to variable.

In most cases SET and SELECT may be used alternatively without any effect.

Following are some scenarios when consideration is required in choosing between SET or SELECT. Scripts using the AdventureWorks database are provided for further clarification.

Part 1 and 2 are mentioned in the scripts below. It would be better if you run each part of the script separately so you can see the results for each method.

Returning values through a query

Whenever you are assigning a query returned value to a variable, SET will accept and assign a scalar (single) value from a query. While SELECT could accept multiple returned values. But after accepting multiple values through a SELECT command you have no way to track which value is present in the variable. The last value returned in the list will populate the variable. Because of this situation it may lead to un-expected results as there would be no error or warning generated if multiple values were returned when using SELECT. So, if multiple values could be expected use the SET option with proper implementation of error handling mechanisms.

To further clarify the concept please run script # 1 in two separate parts to see the results

Part 1 of the script should be successful. The variable is populated with a single value through SET. But in part 2 of the script the following error message will be produced and the SET statement will fail to populate the variable when more than one value is returned.

Error message generated for SET

Hence SET prevented assignment of an ambiguous value.

In case of SELECT, even if multiple values are returned by the query, no error will be generated and there will be no way to track that multiple values were returned and which value is present in the variable. This is demonstrated in the following script.

Both part 1 and 2 were executed successfully. In part 2, multiple values have been assigned and accepted, without knowing which value would actually populate the variable. So when retrieval of multiple values is expected then consider the behavioral differences between SET and SELECT and implement proper error handling for these circumstances.

Assigning multiple values to multiple variables

If you have to populate multiple variables, instead of using separate SET statements each time consider using SELECT for populating all variables in a single statement. This can be used for populating variables directly or by selecting values from database.

Consider the following script comparing the use of SELECT and SET.

If you are using SET then each variable would have to be assigned values individually through multiple statements as shown below.

Obviously SELECT is more efficient than SET while assigning values to multiple variables in terms of statements executed, code and network bytes.

What if variable is not populated successfully

If a variable is not successfully populated then behavior for SET and SELECT would be different. Failed assignment may be due to no result returned or any non-compatible value assigned to the variable. In this case, SELECT would preserve the previous value if any, where SET would assign NULL. Because of the difference functionality, both may lead to unexpected results and should be considered carefully.

This is shown in following script

We can see that part 1 generates NULL when no value is returned for populating variable. Where as part 2 produces the previous value that is preserved after failed assignment of the variable. This situation may lead to unexpected results and requires consideration.

Following the standards

Using SELECT may look like a better choice in specific scenarios, but be aware that using SELECT for assigning values to variables is not included in the ANSI standards. If you are following standards for code migration purposes, then avoid using SELECT and use SET instead.

Best practice suggests not to stick to one method. Depending on the scenario you may want to use both SET or SELECT.

Following are few scenarios for using SET

• If you are required to assign a single value directly to variable and no query is involved to fetch value
• NULL assignments are expected (NULL returned in result set)
• Standards are meant to be follow for any planned migration
• Non scalar results are expected and are required to be handled

Using SELECT is efficient and flexible in the following few cases.

• Multiple variables are being populated by assigning values directly
• Multiple variables are being populated by single source (table , view)
• Less coding for assigning multiple variables
• Use this if you need to get @@ROWCOUNT and @ERROR for last statement executed
• Click here to look at assigning and declaring variables through single statement along with other new T-SQL enhancements in SQL Server 2008.
• Click here to read more about @@ROWCOUNT
• Click here to read more about @@ERROR

About the author

Comments For This Article

Related Content

SQL Declare Variable to Define and Use Variables in SQL Server code

How to use @@ROWCOUNT in SQL Server

Using SQL Variables in SQL Server Code and Queries

SQL Variables in Scripts, Functions, Stored Procedures, SQLCMD and More

The Basics of SQL Server Variables

Nullability settings with select into and variables

SQL Server 2008 Inline variable initialization and Compound assignment

Free Learning Guides

Learn Power BI

What is SQL Server?

Download Links

Become a DBA

What is SSIS?

Related Categories

Change Data Capture

Common Table Expressions

Dynamic SQL

Error Handling

Stored Procedures

Transactions

Development

Date Functions

System Functions

JOIN Tables

SQL Server Management Studio

Database Administration

Performance

Performance Tuning

Locking and Blocking

Data Analytics \ ETL

Microsoft Fabric

Azure Data Factory

Integration Services

Popular Articles

Date and Time Conversions Using SQL Server

Format SQL Server Dates with FORMAT Function

SQL Server CROSS APPLY and OUTER APPLY

SQL Server Cursor Example

SQL CASE Statement in Where Clause to Filter Based on a Condition or Expression

DROP TABLE IF EXISTS Examples for SQL Server

SQL NOT IN Operator

SQL Convert Date to YYYYMMDD

Rolling up multiple rows into a single row and column for SQL Server data

Format numbers in SQL Server

Script to retrieve SQL Server database backup history and no backups

Resolving could not open a connection to SQL Server errors

How to install SQL Server 2022 step by step

SQL Server PIVOT and UNPIVOT Examples

How to monitor backup and restore progress in SQL Server

An Introduction to SQL Triggers

List SQL Server Login and User Permissions with fn_my_permissions

SQL Server Management Studio Dark Mode

Using MERGE in SQL Server to insert, update and delete at the same time

SQL Server Loop through Table Rows without Cursor

Assignment Operator in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Assignment Operator in SQL Server with Examples

What is an operator in sql server.

A n operator is a symbol that performs some specific operation on operands or expressions. These operators are classified as follows in SQL Server.

Understanding the Assignment Operator in SQL Server:

Please use the below script to create and populate the employee table with the required data., assignment operator:.

The assignment operator can also be used to establish the relationship between a column heading and the expression that defines the values for that column. The following example displays the column headings as FirstColumn and SecondColumn. The string ‘ abcd ‘ is displayed for all the rows in the FirstColumn column heading. Then, each Employee ID from the Employee table is listed in the SecondColumn column heading.

Compound Assignment Operators in SQL Server:

Example without using compound assignment operators.

The following example is without using Compound Assignment Operators.

Example using Compound Assignment Operators

Following are the list of available compound operators in sql server.

In the next article, I am going to discuss Arithmetic Operators in SQL Server. Here, in this article, I try to explain the Assignment Operator in SQL Server with Examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this article.

1 thought on “Assignment Operator in SQL Server”

Leave a Reply Cancel reply

• SQL Server training
• Write for us!

What to choose when assigning values to SQL Server variables: SET vs SELECT T-SQL statements

SQL Server provides us with two methods in T-SQL to assign a value to a previously created local SQL variable. The first method is the SET statement, the ANSI standard statement that is commonly used for variable value assignment. The second statement is the SELECT statement. In addition to its main usage to form the logic that is used to retrieve data from a database table or multiple tables in SQL Server, the SELECT statement can be used also to assign a value to a previously created local variable directly or from a variable, view or table.

Although both T-SQL statements fulfill the SQL variable value assignment task, there is a number of differences between the SET and SELECT statements that may lead you to choose one of them in specific circumstances, over the other. In this article, we will describe, in detail, when and why to choose between the SET and SELECT T-SQL statements while assigning a value to a variable.

We will start with creating a new table and fill it with few records for our demo. This can be achieved using the below script:

The inserted data can be checked using the following SELECT statement:

And the data will be shown as below:

If we manage to assign a scalar value for the SQL variable that is previously defined using the DECLARE statement, both the SET and SELECT statements will achieve the target in the same way. The below SET statement is used to assign the @EmpName1 variable with the scalar “Ali” value:

In the same way, the below SELECT statement can be used to assign the @EmpName2 variable with the scalar “Ali” value:

The assigned values for the variables in the previous queries will be printed in the Messages tab as shown below:

SQL Server allows us to assign value for a SQL variable from a database table or view. The below query is used to assign the @EmpName variable the Name column value of the third group members from the SetVsSelectDemo table using the SET statement:

The SELECT statement can be also used to perform the same assignment task in a different way as shown below:

The results of the previous two queries will be displayed in the Messages tab as shown below:

Until this point, you can see that both the SET and SELECT statements can perform the variable value assignment task in the same way and differ from the code side only.

Multiple SQL Variables

Assume that we need to assign values to multiple variables at one shot. The SET statement can assign value to one variable at a time; this means that, if we need to assign values for two variables, we need to write two SET statements. In the below example, each variable requires a separate SET statement to assign it scalar value, before printing it:

On the other hand, the SELECT statement can be used to assign values to the previously defined multiple SQL variables using one SELECT statement. The below SELECT statement can be easily used to assign scalar values to the two variables using one SELECT statement before printing it:

You can see from the printed result below, that both statements achieve the same task, with the SELECT statement better than the SET statement when trying to assign values to multiple variables due to code simplicity:

Again, if we try to assign values from database table to multiple variables, it requires us SET statements equal to the number of variables. In our example, we need two SET statements to assign values from the SetVsSelectDemo table to the @EmpName and @EmpGrade variables as shown in the script below:

On the other hand, only one SELECT statement can be used to assign values from the SetVsSelectDemo table to the @EmpName and @EmpGrade SQL variables, using simpler query as shown clearly below:

It is obvious from the previous two queries that the query that is using the SELECT statement is more efficient than the one using the SET statement when assigning values to multiple variables at the same time, due to the fact that, the SET statement can only assign one variable at a time. The similar results of the previous two queries that are printed in the Messages tab will be like the below in our case:

Multiple values

The second point, in which the difference between assigning values to the SQL variables using the SELECT or SET statements appears, is when the result set of the subquery query that is used to assign a value to the variable returns more than one value. In this case, the SET statement will return an error as it accepts only one scalar value from the subquery to assign it to the variable, while the SELECT statement accepts that situation, in which the subquery will return multiple values, without raising any error. You will not, though, have any control on which value will be assigned to the variable, where the last value returned from the subquery will be assigned to the variable.

Assume that we need to assign the Name value of the second group from the previously created SetVsSelectDemo table to the @EmpName SQL variable. Recall that the second group on that table contains two records in the result set as shown below:

The script that is used to assign the @EmpName variable value from the SetVsSelectDemo table using the SET and SELECT statements will be like:

Due to the fact that, the subquery statement returned two records, assigning value to the @EmpName SQL variable using the SET statement will fail, as the SET statement can assign only single value to the variables. This is not the case when assigning value to the @EmpName variable using the SELECT statement that will succeed with no error, assigning the name from the second returned record, which is “Zaid”, to the variable as shown in the result messages below:

We can learn from the previous result that, when you expect that the subquery will return more than one value, it is better to use the SET statement to assign value to the variable by implementing a proper error handling mechanism, rather than using the SELECT statement that will assign the last returned value to the SQL variable, with no error returned to warn us that the subquery returned multiple values.

Assign no value

Another difference between assigning values to the SQL variables using the SET and SELECT statements, is when the subquery that is used to assign a value to the variable return no value. If the previously declared variable has no initial value, both the SET and SELECT statement will act in the same way, assigning NULL value to that variable.

Assume that we need to assign the @EmpName variable, with no initial value, the Name of the fifth group from the SetVsSelectDemo table. Recall that this table has no records that belong to the fifth group as shown below:

The script that is used to assign the value to the @EmpName variable from the SetVsSelectDemo table will be like:

Having no initial value for the @EmpName variable, and no value returned from the subquery, a NULL value will be assigned to that variable in both cases as shown clearly in the result message below:

If the previously declared SQL variable has an initial value, and the subquery that is used to assign a value to the variable returns no value, the SET and SELECT statement will behave in different ways. In this case, the SET statement will override the initial value of the variable and return the NULL value. On the contrary, the SELECT statement will not override the initial value of the variable and will return it, if no value is returned from the assigning subquery.

If we arrange again to assign the @EmpName variable, the Name of the fifth group from the SetVsSelectDemo table, recalling that this table has no records that belong to the fifth group, but this time, after setting an initial value for the @EmpName SQL variable during the variable declaration, using the SET and SELECT statements, as shown in the script below:

Taking into consideration that the assigning subquery retuned no value, the query that used the SET statement to assign value to the SQL variable will override the initial value of the variable, returning NULL value, while the query that used the SELECT statement to assign value to the variable will keep the initial value with no change as no value is returned from the subquery, as shown clearly in the results below:

• If you manage to assign values to multiple variables directly or from a database table, it is better to use the SELECT statement, that requires one statement only, over the SET statement due to coding simplicity
• If you are following the ANSI standard for code migration purposes, use the SET statement for SQL variables values assignment, as the SELECT statement does not follow the ANSI standard
• If the assigning subquery returns multiple values, using the SET statement to assign value to a variable will raise an error as it only accepts a single value, where the SELECT statement will assign the last returned value from the subquery to the variable, with no control from your side
• If the assigning subquery returns no value, the SET statement will override the variable initial value to NULL, while the SELECT statement will not override its initial value
• Recent Posts

• Azure Data Factory Interview Questions and Answers - February 11, 2021
• How to monitor Azure Data Factory - January 15, 2021
• Using Source Control in Azure Data Factory - January 12, 2021

Related posts:

• Qué elegir al asignar valores a las variables de SQL Server: sentencias SET vs SELECT T-SQL
• Static Data Masking in SSMS 18
• SQL Server PRINT and SQL Server RAISERROR statements
• What is causing database slowdowns?
• SQL Variables: Basics and usage

• sp_whoisactive

T-SQL Variables: Multiple Value Assignment

Tony Rogerson brings us an interesting blog post about T-SQL variable assignment and SET vs. SELECT .  The issue?  With SELECT you can assign values to multiple variables simultaneously.  But with SET, you can set up your assignment such that you get an exception if more than one row is assigned to the variable.  Both are desirable qualities… But unfortunately, as Tony shows us, it’s difficult to achieve both multiple assignment and getting the exception thrown, at the same time.  Tony shows us a solution involving checking for the number of rows affected after the assignment. Creative and effective, but it still has an issue: Unlike with SET when it throws an exception, with Tony’s solution the variables will still have been affected by the assignment.

As I was reading Tony’s post, I couldn’t help but think that there must be another way.  And low and behold, there is — at least, in SQL Server 2005.  Thanks to the power of windowed aggregates we can have our multiple pieces of cake and eat them, all at the same time. Wonderful stuff.

So, here’s what you do: Set up a CTE that selects the columns you’d like to assign to your variables, and also get COUNT(*), partitioned by 1 (or some other arbitrary literal). By partitioning by a literal, we will end up with the row count for the entire set. In the outer query, express the assignments from the columns returned by the CTE, but add an additional WHERE clause that compares the value of the COUNT(*) column with a subquery against a table of numbers. In the following example which I’ve adapted from Tony’s blog, I’m using master..spt_values for the numbers, but you are encouraged to use a properly-indexed table of numbers, should you decide to use this technique:

As you’ll see if you run this on your end, an exception is thrown and the values of the variables are not affected.  This works because the subquery used in the WHERE clause will return more than one value if theCount is greater than 1, thereby violating the rule that subqueries must only return one value.

The price you’ll pay for this convenience?  Extremely complex code for a simple variable assignment, in addition to a slight performance penalty.  Is it worth it?  Probably not, at least for me.  To be honest, I seriously doubt I will ever use this — I’ve never been especially concerned with the chance of multiple rows screwing up my variable assignment, and those times that it has happened, I’ve remedied the situation other ways (e.g., defining a better primary key). That said, I think this was an interesting T-SQL challenge, and if anyone comes up with a more elegant solution than Tony’s or mine, I’d love to see it!

RELATED ARTICLES MORE FROM AUTHOR

Looking forward 100 months (t-sql tuesday #100): the roundup, t-sql tuesday #200 (v1.0): last of the dbas, invitation: t-sql tuesday #100 – looking forward 100 months.

Hi Adam, A similar effect can be obtained (with a more efficient plan and fewer keystrokes!) by using the ROW_NUMBER function in an ORDER BY clause on the assignment, as shown in this example: — Table representing some arbitrary result set DECLARE @T TABLE (id INT NOT NULL) — Simulate a query returning more than 1 row INSERT @T (id) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3; — The variable we will assign to DECLARE @id INTEGER; — Default value SET @id = -1; — The assignment statement — The ORDER BY clause will throw an exception — if row number 2 exists… SELECT @id = id FROM @T ORDER BY (1 / (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) – 2)); — Untouched value if more than one row was returned SELECT @id; … The "ORDER BY (1 / (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) – 2))" clause can be appended to any assignment statement (the ‘select 1’ part removes any dependency on the preceding query). If not saying for a moment that I’ll be using this routinely – but thanks for the puzzle! </Paul>

Good job, Paul!

Thanks Adam!

what about trick with tinyint (MS SQL 2008)? DECLARE    @reserved INT =-1,    @rowcnt INT=-1,    @used INT=-1,    @control TINYINT=0 SELECT    @control=255*COUNT(*) OVER(PARTITION BY 1) ,    @reserved =  reserved,    @rowcnt =  rowcnt,    @used = used   FROM sysobjects so    INNER JOIN sysindexes si ON si.id = so.id    WHERE        so.name = ‘sysrowsets’ SELECT @reserved, @rowcnt, @used, @control

Nice one, Limojoe!

This tweaks what has been said above? DECLARE    @reserved INT,    @rowcnt INT,    @used INT SELECT   @reserved =  reserved,   @rowcnt =  rowcnt,   @used = used  FROM sysobjects so   INNER JOIN sysindexes si ON si.id = so.id   WHERE       so.name = ‘sysrowsets’  order by  case when COUNT(*) OVER(PARTITION BY 1) > 1 then cast(cast(COUNT(*) OVER(PARTITION BY 1) as varchar ) + ‘ Rows returned, expected only one.’ as int) end     — Should return: Msg 245, Level 16, State 1, Line 8 Conversion failed when converting the varchar value ‘3 Rows returned, expected only one.’ to data type int.

Comments are closed.

Popular Posts

The career influence of usenet strangers (t-sql tuesday #096), playing the third-party recruiter game (t-sql tuesday #093), the sql hall of shame, the guru (t-sql tuesday #089), solving the net changes problem with temporal tables (t-sql tuesday #087).

SQL Tutorial

Sql database, sql references, sql examples, sql exercises.

You can test your SQL skills with W3Schools' Exercises.

We have gathered a variety of SQL exercises (with answers) for each SQL Chapter.

Try to solve an exercise by filling in the missing parts of a code. If you're stuck, hit the "Show Answer" button to see what you've done wrong.

Count Your Score

You will get 1 point for each correct answer. Your score and total score will always be displayed.

Start SQL Exercises

Start SQL Exercises ❯

If you don't know SQL, we suggest that you read our SQL Tutorial from scratch.

Kickstart your career

Get certified by completing the course

COLOR PICKER

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail: [email protected]

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail: [email protected]

Top Tutorials

Top references, top examples, get certified.

T-SQL Tutorial

T-sql variables - declare and set variable.

One of the key features of SQL Server that helps in managing and manipulating data efficiently is the use of variables. Variables in SQL Server are used to store data temporarily during the execution of code. They are essential in writing reusable, readable, and modular code. By using variables, developers can write more flexible and dynamic SQL queries, enhancing the capability to handle data dynamically during runtime. Understanding how to declare, initialize, and manipulate variables is fundamental for anyone looking to master SQL Server programming.

In SQL Server, variables are used to store and manipulate data within a T-SQL script or a stored procedure. Variables provide a way to store temporary values that can be used in various parts of a script or procedure. They can hold different data types, such as integers, strings, dates, or other SQL Server data types.

1. What is a Variable?

A Transact-SQL local variable is an database object that can store a single data value of a specific type. Variables are particularly useful for temporarily storing data, which can be manipulated or transferred as needed within batches, stored procedures, or scripts.

2. What are the types of variables?

The following are the two main types of SQL Server variables: Local variables . They are declared by the user and start with the '@' symbol. Local variables can be used within a procedure or batch. The scope of a local variable is limited to the batch or stored procedure in which it is declared. Once the batch or procedure ends, the variable is de-allocated and ceases to exist. Global variables . They are declared by the system beforehand and start with the '@@' symbol. Global variables can store session information. As these are system-defined, their lifecycle and scope are managed by SQL Server itself, and they persist across different batches and sessions.

3. Declare a Transact-SQL Variable

To declare a variable uses the keyword DECLARE , assign a variable name and a data type . The DECLARE statement of Transact-SQL will declare a variable as per the instruction given by the user.

The following are the rules behind the DECLARE statement: A name should be assigned by having '@' before itself. A data type and length should be assigned. The most used date types are: INT , DATE , VARCHAR . Initially, the value of the variable is set to null. Examples: DECLARE @EMP_ID INT; DECLARE @EMP_ID AS INT; DECLARE @EMP_NAME VARCHAR (50); DECLARE @EMP_ID AS INT, @EMP_NAME VARCHAR (50);

4. Set a Variable Value

Syntax for the SET statement to set multiple variables: DECLARE @Local_Variable_1 Data_Type, @Local_Variable_2 Data_Type SET @Local_Variable_1 = Value SET @Local_Variable_2 = Value Example: DECLARE @EMP_ID as INT, @EMP_NAME AS VARCHAR(50) SET @EMP_ID = 5 SET @EMP_NAME = 'STEVE' PRINT @EMP_ID PRINT @EMP_NAME

5. SELECT a Variable

The SELECT statement can be used to select the assigned values by certain criteria as per the requirement of the user. Syntax for the SELECT statement of one variable or multiple variables: DECLARE @Local_Variable Data_Type SET @Local_Variable = Value Example: DECLARE @EMP_ID as INT, @EMP_NAME AS VARCHAR(50) SELECT @EMP_ID = 5, @EMP_NAME = 'STEVE' PRINT @EMP_ID PRINT @EMP_NAME

6. Complex example

T-SQL certification

7. Conclusion

Variables are a fundamental aspect of SQL Server programming, enabling developers to write more efficient, dynamic, and readable SQL scripts. Understanding how to properly declare, initialize, and manage variables can significantly enhance your database operations. Practice is key to mastering their use, so experimenting with different scenarios and applications will build your proficiency and confidence in handling variables effectively in SQL Server.

8. More examples

10 Beginner SQL Practice Exercises With Solutions

• online practice
• sql practice

Table of Contents

The Dataset

Exercise 1: selecting all columns from a table, exercise 2: selecting a few columns from a table, exercise 3: selecting a few columns and filtering numeric data in where, exercise 4: selecting a few columns and filtering text data in where, exercise 5: selecting a few columns and filtering data using two conditions in where, exercise 6: filtering data using where and sorting the output, exercise 7: grouping data by one column, exercise 8: grouping data by multiple columns, exercise 9: filtering data after grouping, exercise 10: selecting columns from two tables, that was fun now, time to do sql practice on your own.

Solve these ten SQL practice problems and test where you stand with your SQL knowledge!

This article is all about SQL practice. It’s the best way to learn SQL. We show you ten SQL practice exercises where you need to apply essential SQL concepts. If you’re an SQL rookie, no need to worry – these examples are for beginners.

Use them as a practice or a way to learn new SQL concepts. For more theoretical background and (even more!) exercises, there’s our interactive SQL Basics course. It teaches you how to select data from one or more tables, aggregate and group data, write subqueries, and use set operations. The course comprises 129 interactive exercises so there is no lack of opportunities for SQL practice, especially if you add some of the 12 ways of learning SQL online to it.

Speaking of practice, let’s start with our exercises!

The question is always where to find data for practicing SQL. We’ll use our dataset for all exercises. No need to limit yourself to this, though – you can find other free online datasets for practicing SQL .

Our dataset consists of two tables.

The table distribution_companies lists movie distribution companies with the following columns:

• id – The ID of the distribution company. This is the primary key of the table.
• company_name – The name of the distribution company.

The table is shown below.

The second table is movies . These are the columns:

• id – The ID of the movie. This is the primary key of the table.
• movie_title – The movie title.
• imdb_rating – The movie rating on IMDb.
• year_released – The year the movie was released.
• budget – The budget for the movie in millions of dollars.
• box_office – The earnings of the movie in millions of dollars.
• distribution_company_id – The ID of the distribution company, referencing the table distribution_companies (foreign key).
• language – The language(s) spoken in the movie.

Exercise: Select all data from the table distribution_companies .

Solution explanation: Select the data using the SELECT statement. To select all the columns, use an asterisk ( * ). The table from which the data is selected is specified in the FROM clause.

Solution output:

Exercise: For each movie, select the movie title, the IMDb rating, and the year the movie was released.

Solution explanation: List all the columns needed ( movie_title , imdb_rating , and year_released ) in the SELECT statement, separated by the comma. Reference the table movies in the FROM clause.

Exercise: Select the columns movie_title and box_office from the table movies . Show only movies with earnings above $300 million.

Solution explanation: List the columns in SELECT and reference the table in FROM . Use a WHERE clause to filter the data – write the column box_office and use the ‘greater than’ operator ( > ) to show only values above $300 million.

Exercise: Select the columns movie_title , imdb_rating , and year_released from the table movies . Show movies that have the word ‘Godfather’ in the title.

Solution explanation: List the columns in SELECT and reference the table in the FROM clause. Use a WHERE clause to filter the data. After writing the column name, use the LIKE logical operator to look for ‘Godfather’ in the movie title, written in single quotes. To find the word anywhere in the movie title, place the wildcard character ( % ) before and after the word.

Exercise: Select the columns movie_title , imdb_rating , and year_released from the table movies . Show movies that were released before 2001 and had a rating above 9.

Solution explanation: List the columns in SELECT and reference the table in FROM . Set the first condition that the year released is before 2001 using the ‘less than’ ( < ) operator. To add another condition, use the AND logical operator. Use the same logic as the first condition, this time using the ‘greater than’ operator with the column imdb_rating .

Exercise: Select the columns movie_title , imdb_rating , and year_released from the table movies . Show movies released after 1991. Sort the output by the year released in ascending order.

Solution explanation: List the columns in SELECT and reference the table in FROM . Filter the data with WHERE by applying the ‘greater than’ operator to the column year_released . To sort the data, use an ORDER BY clause and write the column name by which you wish to sort. The type of sorting is specified by writing ASC (ascending) or DESC (descending). If the type is omitted, the output is sorted in ascending order by default.

Exercise: Show the count of movies per each language category.

Solution explanation: Select the column language from the table movies . To count the number of movies, use the aggregate function COUNT() . Use the asterisk ( * ) to count the rows, which equals the count of movies. To give this column a name, use the AS keyword followed by the desired name. To show the count by language, you need to group the data by it, so write the column language in the GROUP BY clause.

Exercise: Show the count of movies by year released and language. Sort results by the release date in ascending order.

Solution explanation: List the columns year_released and language from the table movies in SELECT . Use COUNT(*) to count the number of movies and give this column a name using the AS keyword. Specify the columns by which you want to group in the GROUP BY clause. Separate each column name with a comma. Sort the output using ORDER BY with the column year_released and the ASC keyword.

Exercise: Show the languages spoken and the average movie budget by language category. Show only the languages with an average budget above $50 million.

Solution explanation: Select the column language from the table movies . To compute the average budget, use the aggregate function AVG() with the column budget in parentheses. Name the column in the output by using the AS keyword. Group the data by rating using GROUP BY . To filter the data after grouping, use a HAVING clause. In it, use the same AVG() construct as in SELECT and set the values to be above 50 using the ‘greater than’ operator.

Exercise: Show movie titles from the table movies , each with the name of its distribution company.

Solution explanation: List the columns movie_title and company_name in SELECT . In the FROM clause, reference the table distribution_companies . Give it an alias dc to shorten its name for use later. The AS keyword is omitted here; you may use it if you wish. To access the data from the other table, use JOIN (it may also be written as INNER JOIN ) and write the table name after it. Give this table an alias also. The join used here is an inner type of join; it returns only the rows that match the joining condition specified in the ON clause. The tables are joined where the column id from the table distribution_companies is equal to the column distribution_company_id from the table movies . To specify which column is from which table, use the corresponding alias of each table.

These ten SQL practice exercises give you a taste of what practicing SQL looks like. Whether you are at the beginner, intermediate, or advanced level, it’s the same. What changes is the complexity of the problems you solve and of the code you write.

Look for more challenges in the SQL Basics course and the Monthly SQL Practice track. Both are excellent for your SQL practice online. This is true, especially if you do not have an opportunity to use SQL on a daily basis in your job.

So, don’t try to test how long it takes to forget what you once knew in SQL! Use every opportunity to solve as many SQL practice problems as possible.

You may also like

How Do You Write a SELECT Statement in SQL?

What Is a Foreign Key in SQL?

Enumerate and Explain All the Basic Elements of an SQL Query

• ▼SQL Exercises
• Introduction
• Retrieve data from tables
• Boolean and Relational Operators
• Wildcard and Special operators
• Aggregate Functions
• Formatting query output
• Query on Multiple Tables
• FILTERING and SORTING on HR Database
• SUBQUERIES on HR Database
• JOINS on HR Database
• SQL User Management
• ▼Movie Database
• BASIC QUERIES
• ▼Soccer Database
• ▼Hospital Database
• ▼Employee Database
• ▼AdventureWorks Database
• ▼SQL Challenges
• Challenges-1
• ..More to come..

SQL Exercises, Practice, Solution

What is sql.

SQL stands for Structured Query Language and it is an ANSI standard computer language for accessing and manipulating database systems. It is used for managing data in relational database management system which stores data in the form of tables and relationship between data is also stored in the form of tables. SQL statements are used to retrieve and update data in a database.

The best way we learn anything is by practice and exercise questions. We have started this section for those (beginner to intermediate) who are familiar with SQL . Hope, these exercises help you to improve your SQL skills. Currently following sections are available, we are working hard to add more exercises. Happy Coding!

You may read our SQL tutorial before solving the following exercises.

List of SQL Exercises

• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• BASIC, SUBQUERIES, and JOINS [41 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• AdventureWorks Database:
• AdventureWorks Database [200 Exercises]
• SQL Challenges-1:
• SQL Challenges-1 [77 Exercises]
• More to come!

Structure of inventory database :

Structure of HR database :

Structure of movie database :

Structure of soccer database :

Structure of employee database :

Structure of hospital database :

Syntax diagram of SQL SELECT statement

You may download the structure and data of the tables of database on which SQL Exercises are built.

Please note that PostgreSQL 9.4 is used and the file which you would download is generated using pg_dump

Follow us on Facebook and Twitter for latest update.

• Weekly Trends and Language Statistics

• SQL Cheat Sheet
• SQL Interview Questions
• MySQL Interview Questions
• PL/SQL Interview Questions
• Learn SQL and Database

SQL Exercises : SQL Practice with Solution for Beginners and Experienced

SQL ( Structured Query Language ) is a powerful tool used for managing and manipulating relational databases. Whether we are beginners or experienced professionals, practicing SQL exercises is important for improving your skills. Regular practice helps you get better at using SQL and boosts your confidence in handling different database tasks.

So, in this free SQL exercises page, we’ll cover a series of SQL practice exercises covering a wide range of topics suitable for beginners , intermediate , and advanced SQL learners. These exercises are designed to provide hands-on experience with common SQL tasks, from basic retrieval and filtering to more advanced concepts like joins window functions , and stored procedures.

Table of Content

SQL Exercises for Practice

Sql practice exercises for beginners, sql practice exercises for intermediate, sql practice exercises for advanced, more questions for practice.

Practice SQL questions to enhance our skills in database querying and manipulation. Each question covers a different aspect of SQL , providing a comprehensive learning experience.

We have covered a wide range of topics in the sections beginner , intermediate and advanced .

• Basic Retrieval
• Arithmetic Operations and Comparisons:
• Aggregation Functions
• Group By and Having
• Window Functions
• Conditional Statements
• DateTime Operations
• Creating and Aliasing
• Constraints
• Stored Procedures:
• Transactions

let’s create the table schemas and insert some sample data into them.

Create Sales table

Create Products table

This hands-on approach provides a practical environment for beginners to experiment with various SQL commands, gaining confidence through real-world scenarios. By working through these exercises, newcomers can solidify their understanding of fundamental concepts like data retrieval, filtering, and manipulation, laying a strong foundation for their SQL journey.

1. Retrieve all columns from the Sales table.

Explanation:

This SQL query selects all columns from the Sales table, denoted by the asterisk (*) wildcard. It retrieves every row and all associated columns from the Sales table.

2. Retrieve the product_name and unit_price from the Products table.

This SQL query selects the product_name and unit_price columns from the Products table. It retrieves every row but only the specified columns, which are product_name and unit_price.

3. Retrieve the sale_id and sale_date from the Sales table.

This SQL query selects the sale_id and sale_date columns from the Sales table. It retrieves every row but only the specified columns, which are sale_id and sale_date.

4. Filter the Sales table to show only sales with a total_price greater than $100.

This SQL query selects all columns from the Sales table but only returns rows where the total_price column is greater than 100. It filters out sales with a total_price less than or equal to $100.

5. Filter the Products table to show only products in the ‘Electronics’ category.

This SQL query selects all columns from the Products table but only returns rows where the category column equals ‘Electronics’. It filters out products that do not belong to the ‘Electronics’ category.

6. Retrieve the sale_id and total_price from the Sales table for sales made on January 3, 2024.

This SQL query selects the sale_id and total_price columns from the Sales table but only returns rows where the sale_date is equal to ‘2024-01-03’. It filters out sales made on any other date.

7. Retrieve the product_id and product_name from the Products table for products with a unit_price greater than $100.

This SQL query selects the product_id and product_name columns from the Products table but only returns rows where the unit_price is greater than $100. It filters out products with a unit_price less than or equal to $100.

8. Calculate the total revenue generated from all sales in the Sales table.

This SQL query calculates the total revenue generated from all sales by summing up the total_price column in the Sales table using the SUM() function.

9. Calculate the average unit_price of products in the Products table.

This SQL query calculates the average unit_price of products by averaging the values in the unit_price column in the Products table using the AVG() function.

10. Calculate the total quantity_sold from the Sales table.

This SQL query calculates the total quantity_sold by summing up the quantity_sold column in the Sales table using the SUM() function.

11. Retrieve the sale_id, product_id, and total_price from the Sales table for sales with a quantity_sold greater than 4.

This SQL query selects the sale_id, product_id, and total_price columns from the Sales table but only returns rows where the quantity_sold is greater than 4.

12. Retrieve the product_name and unit_price from the Products table, ordering the results by unit_price in descending order.

This SQL query selects the product_name and unit_price columns from the Products table and orders the results by unit_price in descending order using the ORDER BY clause with the DESC keyword.

13. Retrieve the total_price of all sales, rounding the values to two decimal places.

This SQL query calculates the total sales revenu by summing up the total_price column in the Sales table and rounds the result to two decimal places using the ROUND() function.

14. Calculate the average total_price of sales in the Sales table.

This SQL query calculates the average total_price of sales by averaging the values in the total_price column in the Sales table using the AVG() function.

15. Retrieve the sale_id and sale_date from the Sales table, formatting the sale_date as ‘YYYY-MM-DD’.

This SQL query selects the sale_id and sale_date columns from the Sales table and formats the sale_date using the DATE_FORMAT() function to display it in ‘YYYY-MM-DD’ format.

16. Calculate the total revenue generated from sales of products in the ‘Electronics’ category.

This SQL query calculates the total revenue generated from sales of products in the ‘Electronics’ category by joining the Sales table with the Products table on the product_id column and filtering sales for products in the ‘Electronics’ category.

17. Retrieve the product_name and unit_price from the Products table, filtering the unit_price to show only values between $20 and $600.

This SQL query selects the product_name and unit_price columns from the Products table but only returns rows where the unit_price falls within the range of $50 and $200 using the BETWEEN operator.

18. Retrieve the product_name and category from the Products table, ordering the results by category in ascending order.

This SQL query selects the product_name and category columns from the Products table and orders the results by category in ascending order using the ORDER BY clause with the ASC keyword.

19. Calculate the total quantity_sold of products in the ‘Electronics’ category.

This SQL query calculates the total quantity_sold of products in the ‘Electronics’ category by joining the Sales table with the Products table on the product_id column and filtering sales for products in the ‘Electronics’ category.

20. Retrieve the product_name and total_price from the Sales table, calculating the total_price as quantity_sold multiplied by unit_price.

This SQL query retrieves the product_name from the Sales table and calculates the total_price by multiplying quantity_sold by unit_price, joining the Sales table with the Products table on the product_id column.

These exercises are designed to challenge you beyond basic queries, delving into more complex data manipulation and analysis. By tackling these problems, you’ll solidify your understanding of advanced SQL concepts like joins, subqueries, functions, and window functions, ultimately boosting your ability to work with real-world data scenarios effectively.

1. Calculate the total revenue generated from sales for each product category.

This query joins the Sales and Products tables on the product_id column, groups the results by product category, and calculates the total revenue for each category by summing up the total_price.

2. Find the product category with the highest average unit price.

This query groups products by category, calculates the average unit price for each category, orders the results by the average unit price in descending order, and selects the top category with the highest average unit price using the LIMIT clause.

3. Identify products with total sales exceeding 30.

This query joins the Sales and Products tables on the product_id column, groups the results by product name, calculates the total sales revenue for each product, and selects products with total sales exceeding 30 using the HAVING clause.

4. Count the number of sales made in each month.

This query formats the sale_date column to extract the month and year, groups the results by month, and counts the number of sales made in each month.

5. Determine the average quantity sold for products with a unit price greater than $100.

This query joins the Sales and Products tables on the product_id column, filters products with a unit price greater than $100, and calculates the average quantity sold for those products.

6. Retrieve the product name and total sales revenue for each product.

This query joins the Sales and Products tables on the product_id column, groups the results by product name, and calculates the total sales revenue for each product.

7. List all sales along with the corresponding product names.

This query joins the Sales and Products tables on the product_id column and retrieves the sale_id and product_name for each sale.

8. Retrieve the product name and total sales revenue for each product.

This query will give you the top three product categories contributing to the highest percentage of total revenue generated from sales. However, if you only have one category (Electronics) as in the provided sample data, it will be the only result.

9. Rank products based on total sales revenue.

This query joins the Sales and Products tables on the product_id column, groups the results by product name, calculates the total sales revenue for each product, and ranks products based on total sales revenue using the RANK () window function.

10. Calculate the running total revenue for each product category.

This query joins the Sales and Products tables on the product_id column, partitions the results by product category, orders the results by sale date, and calculates the running total revenue for each product category using the SUM() window function.

11. Categorize sales as “High”, “Medium”, or “Low” based on total price (e.g., > $200 is High, $100-$200 is Medium, < $100 is Low).

This query categorizes sales based on total price using a CASE statement. Sales with a total price greater than $200 are categorized as “High”, sales with a total price between $100 and $200 are categorized as “Medium”, and sales with a total price less than $100 are categorized as “Low”.

12. Identify sales where the quantity sold is greater than the average quantity sold.

This query selects all sales where the quantity sold is greater than the average quantity sold across all sales in the Sales table.

13. Extract the month and year from the sale date and count the number of sales for each month.

14. Calculate the number of days between the current date and the sale date for each sale.

This query calculates the number of days between the current date and the sale date for each sale using the DATEDIFF function.

15. Identify sales made during weekdays versus weekends.

This query categorizes sales based on the day of the week using the DAYOFWEEK function. Sales made on Sunday (1) or Saturday (7) are categorized as “Weekend”, while sales made on other days are categorized as “Weekday”.

This section likely dives deeper into complex queries, delving into advanced features like window functions, self-joins, and intricate data manipulation techniques. By tackling these challenging exercises, users can refine their SQL skills and tackle real-world data analysis scenarios with greater confidence and efficiency.

1. Write a query to create a view named Total_Sales that displays the total sales amount for each product along with their names and categories.

This query creates a view named Total_Sales that displays the total sales amount for each product along with their names and categories.

2. Retrieve the product details (name, category, unit price) for products that have a quantity sold greater than the average quantity sold across all products.

This query retrieves the product details (name, category, unit price) for products that have a quantity sold greater than the average quantity sold across all products.

3. Explain the significance of indexing in SQL databases and provide an example scenario where indexing could significantly improve query performance in the given schema.

With an index on the sale_date column, the database can quickly locate the rows that match the specified date without scanning the entire table. The index allows for efficient lookup of rows based on the sale_date value, resulting in improved query performance.

4. Add a foreign key constraint to the Sales table that references the product_id column in the Products table.

This query adds a foreign key constraint to the Sales table that references the product_id column in the Products table, ensuring referential integrity between the two tables.

5. Create a view named Top_Products that lists the top 3 products based on the total quantity sold.

This query creates a view named Top_Products that lists the top 3 products based on the total quantity sold.

6. Implement a transaction that deducts the quantity sold from the Products table when a sale is made in the Sales table, ensuring that both operations are either committed or rolled back together.

The quantity in stock for product with product_id 101 should be updated to 5.The transaction should be committed successfully.

7. Create a query that lists the product names along with their corresponding sales count.

This query selects the product names from the Products table and counts the number of sales (using the COUNT() function) for each product by joining the Sales table on the product_id. The results are grouped by product name using the GROUP BY clause.

8. Write a query to find all sales where the total price is greater than the average total price of all sales.

The subquery (SELECT AVG(total_price) FROM Sales) calculates the average total price of all sales. The main query selects all columns from the Sales table where the total price is greater than the average total price obtained from the subquery.

9. Analyze the performance implications of indexing the sale_date column in the Sales table, considering the types of queries commonly executed against this column.

Query without indexing:.

Query with Indexing:

This format clearly displays the operations and details of the query execution plan before and after indexing.

Without indexing, the query performs a full table scan, filtering rows based on the sale date, which is less efficient. With indexing, the query uses the index to quickly locate the relevant rows, significantly improving query performance.

10. Add a check constraint to the quantity_sold column in the Sales table to ensure that the quantity sold is always greater than zero.

All rows in the Sales table meet the condition of the check constraint, as each quantity_sold value is greater than zero.

11. Create a view named Product_Sales_Info that displays product details along with the total number of sales made for each product.

This view provides a concise and organized way to view product details alongside their respective sales information, facilitating analysis and reporting tasks.

12. Develop a stored procedure named Update_Unit_Price that updates the unit price of a product in the Products table based on the provided product_id.

The above SQL code creates a stored procedure named Update_Unit_Price. This stored procedure takes two parameters: p_product_id (the product ID for which the unit price needs to be updated) and p_new_price (the new unit price to set).

13. Implement a transaction that inserts a new product into the Products table and then adds a corresponding sale record into the Sales table, ensuring that both operations are either fully completed or fully rolled back.

This will update the unit price of the product with product_id 101 to 550.00 in the Products table.

14. Write a query that calculates the total revenue generated from each category of products for the year 2024.

When you execute this query, you will get the total revenue generated from each category of products for the year 2024.

If you’re looking to sharpen your SQL skills and gain more confidence in querying database s, consider delving into these articles. They’re packed with query-based SQL questions designed to enhance your understanding and proficiency in SQL .

By practicing with these exercises, you’ll not only improve your SQL abilities but also boost your confidence in tackling various database-related tasks. The Questions are as follows:

• How to Insert a Value that Contains an Apostrophe in SQL?
• How to Select Row With Max Value in SQL?
• How to Efficiently Convert Rows to Columns in SQL?
• How To Use Nested Select Queries in SQL
• How to Select Row With Max Value on a Column in SQL?
• How to Specify Condition in Count() in SQL?
• How to Find the Maximum of Multiple Columns in SQL?
• How to Update Top 100 Records in SQL?
• How to Select the Last Records in a One-To-Many Relationship Using SQL Join
• How to Join First Row in SQL?
• How to Insert Row If Not Exists in SQL?
• How to Use GROUP BY to Concatenate Strings in SQL?
• How Inner Join works in LINQ to SQL
• How to Get the Identity of an Inserted Row in SQL
• How to Declare a Variable in SQL?

Mastering SQL requires consistent practice and hands-on experience. By working through these SQL practice exercises , you’ll strengthen your skills and gain confidence in querying relational databases.

Whether you’re just starting or looking to refine your expertise, these exercises provide valuable opportunities to hone your SQL abilities. Keep practicing , and you’ll be well-equipped to tackle real-world data challenges with SQL.

Please Login to comment...

Similar reads, improve your coding skills with practice.

What kind of Experience do you want to share?

Pardon Our Interruption

As you were browsing something about your browser made us think you were a bot. There are a few reasons this might happen:

• You've disabled JavaScript in your web browser.
• You're a power user moving through this website with super-human speed.
• You've disabled cookies in your web browser.
• A third-party browser plugin, such as Ghostery or NoScript, is preventing JavaScript from running. Additional information is available in this support article .

To regain access, please make sure that cookies and JavaScript are enabled before reloading the page.

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

SELECT @local_variable (Transact-SQL)

• 14 contributors

Sets a local variable to the value of an expression.

For assigning variables, we recommend that you use SET @local_variable instead of SELECT @ local_variable .

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation .

@ local_variable

A declared variable for which a value is to be assigned.

{ = | += | -= | *= | /= | %= | &= | ^= | |= } Assign the value on the right to the variable on the left.

Compound assignment operator:

Any valid expression . This includes a scalar subquery.

SELECT @ local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.

If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL.

One SELECT statement can initialize multiple local variables.

A SELECT statement that contains a variable assignment cannot be used to also perform typical result set retrieval operations.

A. Use SELECT @local_variable to return a single value

In the following example, the variable @var1 is assigned "Generic Name" as its value. The query against the Store table returns no rows because the value specified for CustomerID doesn't exist in the table. The variable retains the "Generic Name" value.

This example uses the AdventureWorksLT sample database, for more information, see AdventureWorks sample databases . The AdventureWorksLT database is used as the sample database for Azure SQL Database.

Here is the result set.

B. Use SELECT @local_variable to return null

In the following example, a subquery is used to assign a value to @var1 . Because the value requested for CustomerID doesn't exist, the subquery returns no value, and the variable is set to NULL .

C. Antipattern use of recursive variable assignment

Avoid the following pattern for recursive use of variables and expressions:

In this case, it isn't guaranteed that @Var would be updated on a row by row basis. For example, @Var may be set to initial value of @Var for all rows. This is because the order and frequency in which the assignments are processed is nondeterminant. This applies to expressions containing variables string concatenation, as demonstrated below, but also to expressions with non-string variables or += style operators. Use aggregation functions instead for a set-based operation instead of a row-by-row operation.

For string concatenation, instead consider the STRING_AGG function, introduced in SQL Server 2017 (14.x), for scenarios where ordered string concatenation is desired. For more information, see STRING_AGG (Transact-SQL) .

The Transact-SQL code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

An example to avoid, where using ORDER BY in attempt to order concatenation causes list to be incomplete:

Result set:

Instead, consider:

• DECLARE @local_variable (Transact-SQL)
• Expressions (Transact-SQL)
• Compound Operators (Transact-SQL)
• SELECT (Transact-SQL)
• AdventureWorks sample databases

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand
• OverflowAI GenAI features for Teams
• OverflowAPI Train & fine-tune LLMs
• Labs The future of collective knowledge sharing
• About the company Visit the blog

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

Assign to a T-SQL variable from a CASE statement

I'd like to assign some variables inside a query that uses CASE statements for it's columns. Not quite sure how to do this, having trouble finding the right syntax.

This is what I have so far, but it's got syntax errors.

What's the correct place/way to stick those variables in there?

• Can you be more specific as to how "it's not quite working"? –  Adam Robinson Commented Aug 4, 2011 at 17:37
• I'm putting my variable assignment in the wrong place and it causes a syntax error. I'm not sure where/how to put the variable assignment. –  rlb.usa Commented Aug 4, 2011 at 17:39
• 3 What you have posted works well for me. Your issue may not be in the assignment, but the actual code. Can you post the actual code? –  Raj More Commented Aug 4, 2011 at 17:41

3 Answers 3

The example you've given should work. You can assign to variables from a case statement. Just pretend that the entire CASE..WHEN..THEN..ELSE..END block is a field. Here is a generic example:

Can you tell us what specific error(s) you are getting?

You could probably do this more easily using ISNULL or COALESCE :

• 2 This is true in his particular case, but does not answer the question. –  Adam Robinson Commented Aug 4, 2011 at 17:49
• I disagree. Getting the desired functionality working while improving the code seems like an excellent answer. Also, his original code didn't appear to be broken... –  Abe Miessler Commented Aug 4, 2011 at 18:15
• I think you might be biased as to what might make an excellent answer to this question ;) If he'd asked the question in a broader way (i.e. stating his goals rather than asking questions about a particular implemention), then I'd agree with you, but he asked about assigning values to variables using a case statement, which is a perfectly valid use case that, in this particular case can be replaced with a different solution. Recommending alternatives is certainly good, but the original question needs to be addressed first. –  Adam Robinson Commented Aug 4, 2011 at 18:26
• I think you might be biased as to what might make an excellent answer to this question well maybe ;) –  Abe Miessler Commented Aug 4, 2011 at 19:00

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Sign up or log in

Post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy .

Not the answer you're looking for? Browse other questions tagged sql sql-server t-sql or ask your own question .

• Featured on Meta
• We've made changes to our Terms of Service & Privacy Policy - July 2024
• Bringing clarity to status tag usage on meta sites
• Feedback requested: How do you use tag hover descriptions for curating and do...

Hot Network Questions

• Does have subgather command like subequation? (elsevier format) (I wanna 1a,1b,1c)
• Genus 0 curves on surfaces and the abc conjecture
• Finding a Linear Algebra reading topic
• Is "Alice loves candies" actually necessary for "Alice loves all sweet foods"?
• Is it safe to carry Butane canisters bought at sea level up to 5500m?
• How much was Boole influenced by Indian logic?
• Can this minus operation be implemented via a quantum channel?
• Who became an oligarch after the collapse of the USSR
• MOSFETs keep shorting way below rated current
• Fitting the 9th piece into the pizza box
• Where exactly was this picture taken?
• What's the sales pitch for waxing chains?
• Fourth order BVP partial differential equation
• Creating a deadly "minimum altitude limit" in an airship setting
• Kyoto is a famous tourist destination/area/site/spot in Japan
• Use all eight of the given polygons to tile a parallelogram
• Can I travel with regional trains from operators other than DB if I can "use any train" due to a schedule change?
• Did the English Koren-Steinsaltz Utilize an Erroneous Translation for Meilah 7b?
• Adding XYZ button to QGIS Manage Layers Toolbars
• Norm in the minimal tensor product of C*-algebras
• Documents hardware engineers should write and prepare
• What is the origin of this quote on telling a big lie?
• How can I cover all my skin (face+neck+body) while swimming outside (sea or outdoor pool) to avoid UV radiations?
• Is the oil level here too high that it needs to be drained or can I leave it?