SQL Server CTE Basics

The very good article written about the CTE in SQL server. I found it very useful so I thought to share it with you.

SQL SERVER CTE Basics 

Efficient Text Searching Query

Normally when you want to write a query for searching something then you use the like operator. Consider for example that you want to write an algorithm for book search. Consider a situation where you ask BOOK TITLE from the user and then user enter the title the you write query something like this

SELECT BOOK_NAME WHERE TITLE LIKE ‘%@title%’

Now what if you want that if user write some title then your query perform above operations and if user write nothing , leave the field empty then you want to show all the books available in your system, so here is the query which will perform the same operation

SELECT

BOOK_NAME

WHERE

          ((@title IS NULL) OR (@title = ‘’) OR ([TITLE] LIKE '%'+@title+'%'))

Now when you will enter some string then last part of the query will be executed and if you enter nothing then first part of the query will be executed.