SQL Code Smells

Could you please list some of the bad practices in SQL, that novice people do?

I have found the use of "WHILE loop" in scenarios which could be resolved using set operations.

Another example is inserting data only if it does not exist. This can be achieved using LEFT OUTER JOIN. Some people go for "IF"

Any other thoughts?

Edit: What I am looking for is specific scenarios (as mentio开发者_StackOverflowned in the question) that could be achieved using SQL without using procedural constructs

Thanks

Lijo

Here are some I have seen:

• Using cursors instead of equivalent (and faster) set operations (joins etc).
• Dynamic SQL for everything.
• Code that is open to SQL Injection attacks.
• Full outer joins even when they are not needed.
• Huge stored procedures (hundreds/thousands of lines).
• No comments.

Placing ODBC or dynamic SQL calls all over the code.

Often it is better to define a data abstraction layer that provides access to the databases. All the SQL code can hide in that layer. This often avoids replication of similar queries, and makes changing data models easier to do.

Personally for me: anything that is not a plain INSERT, UPDATE, DELETE or SELECT statement

I don't like logic in SQL.

My biggest beef here is definitely repetitive SQL. As an example, multiple stored procedures that perform the exact same joins but different filters.

Using Views in such cases can make your database MUCH easier to look at and work with

1. Creating vendor-specific SQL, when generic SQL would do.

2. Creating tables dynamically at runtime (other than TEMPORARY tables).

3. Letting your application code have table create or super user privs.