Common SQL errors in stored procedures and queries


I will not describe quite banal like syntax errors (talbe instead of table). Let's look at annoying errors that reduce the speed of our development:


1. Keywords in names

For example, you have the Key field. It must be enclosed in brackets

select Name from @table where [Key]='code1'


2. Problems when casting types

It is better to explicitly give types in queries via cast and convert

select cast(itemID as nvarchar) + ' ' + name from @table

If you have a heterogeneous table (the itemID field can be either a string or a number), we recommend using try_cast or try_convert to avoid an exception in the query


3. Incorrect parentheses in conditions

select * from @table where @visible =1 and (isVisible=1 or @visible=0)

It is better to explicitly mark conditions with brackets so that it is easier to read (even if the priority of operations does not require it, as in this case).

select * from @table where (@visible =1 and isVisible=1) or @visible=0


4. Error comparing NULL fields

If you compare a null bit field with a false one, you need to do it carefully.

This comparison is correct

select * from @table where isVisible=1


This comparison is incorrect

select * from @table where isVisible=0

The fact is that it does not take into account that the field can be null (and therefore in business logic this visibility is false)

The correct option

select * from @table where isnull(isVisible, 0)=0

Also be careful with NULL and using the not IN () operator. Consider using Exists

5. When grouping, use fields in select or order that are not included in the grouping

You can use either the fields that are used for grouping, or aggregating data.

Erroneous example:

select code, name, count(*)
from table1
group by code

6. Confusion with the order of keywords in select

Use the correct order

select
from (join)
where
group by
having
order by

7. Passion for join, left join

If you need to pull 1 field from a neighboring table, use subqueries. In order for the query to work quickly, you need to cut off unnecessary data as quickly as possible. If you combine several large tables, you end up with a large mass of data that is then filtered.

First select data from a single table. for the selected data, you can get all the additional data in the select subquery.

select id, (select name from cats where id = catID) catName from products

8. Duplicates of names in select when join

Invalid request:

select id, name from products inner join cats on product.catID = cats.id

The problem is that the id is in both tables.
You must specify more precisely

select products.id, products.name from products inner join cats on product.catID = cats.id

9. Moderately use function in Where clause

If you use Datepart functions or similar, this will probably cause indexes on the fields you use to not be used in the query (which is bad for performance).

If possible, redo the query so that you can do with a minimum of functions in where.

10. The subquery returned multiple values

It so happens that you expect that your subquery must return a single value. But then the business logic and DB structure changes and your query may already return multiple values and an exception will appear.

you can hedge your bets and put top 1 in the subquery. In this case, you are guaranteed to get a maximum of 1 value.


11. Concatenation of strings with NULL

As a result, you will get NULL for all the strings.
If you form a large string in SELECT, use the NULLIF(col, defValue) functions. If the column is NULL, the function will substitute the default value (for example, an empty string) and your entire large generated string will not be NULL.

select name + '' + nullif(descriotion, '') from products

A minute of self-promotion
We are creating a web shell for MS SQL Server. It is called Falcon Space. 


All system development and management takes place via SQL.
Output of any component (tables, forms, dashboards, calendars, etc.), page settings - everything is done via SQL.

The demo shows examples of web platform components

Full system documentation is described here - https://falcon.web-automation.ru/docs

We have also compiled a list of articles on this topic, where you can learn about other simpler errors.

Note