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.
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
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.
Examples of how Falcon Space platform components workExamples of convenient tables and forms Examples of dashboards Examples of graphs and charts Example of a calendar Table with API data Example of displaying a map with points Types of form fields Example of a statistics widget Example of a hierarchy tree Example of the Time line component Example of a product card Example of resource load tracking Example of a Kanban board Example of a master form Example of uploading Excel/Word documents Example of filling in TIN data
- Management Introduction to Falcon Space. What's the first? Guidance on usability of solutions on the Falcon Space platform Example of creating the Personnel subsystem on the Falcon Space platform Management. How to create a table Management. How to create a form Management. How to create a dashboard Installing Falcon Space on a server / hosting How to localize a site for a non-technical specialist Common SQL errors in stored procedures and queries Problems with the processor SQL Server CPU. CPU issues. Processor query Optimization Creating pages with the table/form component with automatic generation of necessary SQL procedures SQL. Optimization of SQL queries. MS SQL queries are slow Using SQL Profiler Express to solve SQL query problems Setting up fulltext search in SQL Server SQL Management Studio is slow. How to solve the problem How to improve the performance of IIS (Internet Information Services web server)
- Falcon Space Foundation
- Basic components
- Falcon Space Features
- Additional component
- HOWTO Tables
- HOWTO Forms
- HOWTO JS
- HOWTO CSS
- HOWTO Layout
- Solve problems
Falcon Space Platform
This is a reduction in the cost of ownership
at the expense of fewer people to support
This is a quick change
while using the program
This is a modern interface
full adaptation for mobile devices