KB Issue

Inner Select statements with too many expressions will cause an error.

Issue Details

When creating an inner select statement inside of your existing select statement, you may sometimes add in two many columns, thus causing the following error to occur.

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Recreate Issue

[SQK Server - Malformed SQL Seleted Statement with Subquery]

CFFCS | CarrzSynEdit: | SQL Script

Select (Select column1, column2 from table 1 where column1=table2.column1)as MyColumn, Table2.Column1, Table2.Column2

In the above example, we see that two columns are referenced in the INNER SELECT statement. This will cause this error.

Resolve Issue

To properly write out your subqueries, you need only to have one column name, and it is always best practice to have TOP 1. This ensures that, if multiple records are returned, the subquery returns only the top 1 record.

[SQK Server - Select Statement with Subquery]

CFFCS | CarrzSynEdit: | SQL Script

Select (Select top 1 column2 from table 1 where column1=table2.column1)as MyColumn, Table2.Column1, Table2.Column2