SQL vs. Procedural Code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL vs. Procedural Code

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-04-17 : 04:59:57

Hi Team,

It is common that novice people use lots of procedural constructs, which could be have been achieved using SQL set operations. Following is an attempt to list such specific scenarios.

1) Inserting data only if it does not exist. This can be achieved using LEFT OUTER JOIN. Some people go for "IF"
2) Use of "WHILE loop" in scenarios which can be resolved using set operations
3) Use of multiple IF blocks based on filtering condition

For the third point, following is the code

IF @Region IS NOT NULL
BEGIN

SELECT * FROM Employee WHERE Region = @Region
END

IF @Area IS NOT NULL
BEGIN

SELECT * FROM Employee WHERE Area = @Area
END

--- Improved Query

SELECT * FROM Employee
WHERE ( @Region IS NULL OR Region = @Region)
AND ( @Area IS NULL OR Area = @Area)

Could you please list such examples if you have come across similar scenarios.

Thanks
Lijo Cheeran Joseph

Kristen
Test

22859 Posts

Posted - 2010-04-17 : 08:12:43

Not sure I agree with you on the IF tests for user-provided parameters.

Each of the two IF code-blocks you have shown will produce a different query plan. If there are indexes on [Region] and [Area] each will be extremely efficient.

OTOH your "improved query" will cache a query plan based on the parameters used the first time the query is made, and that query plan will be used for subsequent executions ... so, if the first query has a value for @Region then SQL Server may decide to use the Region-index. All subsequent executions with @Region = NULL may perform terribly badly

But I agree with you about programmers coming from a procedural-programming background. I was one of those once ... and it took me several years to make the change to using good, set-based, logic for SQL queries.

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-04-18 : 10:32:31

Thanks. Any other examples..?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources