Himanshu

Negi

Developer & Internet Marketer


Welcome to my blog.

Sed sed leo sit amet elit sagittis fermentum. Nulla posuere commodo erat. Nam ac nunc ipsum, id espara tincidunt orci. Quisque adipiscing molestie dolor, ut congue sem porta et. Sed sapien urna, auctor et accumsan ut, accumsan sit amet dui. Nunc tristique interdum magna, dictum volutpat sem. Nam ac nunc ipsum, id espara tincidunt orci. Sed sapien urna, auctor et accumsan ut.

SQL Server Tips and Tricks for .Net Developers: Where Clause with Switch Case

May 12, 2017GUEST BLOGGER1 Comment

SQL Server Tips: “Where Clause with Switch Case”

Whenever, as a .net developer, you are using more than one parameter inside stored procedure, you don’t have to think much about parameter values at front end. It is strongly managed with stored procedures. A .net developer just has to pass value inside stored procedure, it does not matter whatever value it contains. Let us understand the concept through simple example,

We have a table with Column ID, Name and Department ID and we have to filter out name and ID.

[codesyntax lang=”sql” title=”Create Table” bookmarkname=”Create Table”]

//Create Table

Create Table Data
(
       EmpId int,
       Name Nvarchar(100),
       DeptId int
)

[/codesyntax]

 

[codesyntax lang=”sql” title=”Insert Sample data” bookmarkname=”Insert Sample data”]

//Insert Sample data

 

Insert Into #Data

select 1,’admin’,1

Union all

select 2,Peter,1

Union all

select 3,’Bruno’,1

Union all

select 4,’Williams’,1

Union all

select 5,’Adems’,1

Union all

select 6,’John’,1

Union all

select 7,’Murrey’,1

Union all

select 8,’Lee’,1

[/codesyntax]

The next step is to create stored procedure where parameter value will be passed. Don’t forget that we can pass null value also and stored procedure has t take care of everything.

[codesyntax lang=”sql”]

Create Procedure BestPractice1

(

@EmpId int,

@Name Nvarchar(20)

)

as

 

Select * from #Data

Where 1= Case When Isnull(@EmpId,0) =0 Then 1

Else

Case When @EmpId = EmpId Then 1 Else 0 End

End

And 1 = Case When Isnull(len(@Name),0) =0 Then 1

Else

Case When @Name = Name Then 1 Else 0 End

End

[/codesyntax]

See the Procedure Call with null value

Execute the Procedure with Default null value

exec BestPractice1 null,null

Results

EmpId   Name                    DeptId

1              admin                   1

2              Peter                     1

3              Bruno                   1

4              Williams              1

5              Adems                  1

6              John                      1

7              Murrey                1

8              Lee                         1

 

// it will returns all the records

// let us see what happen

[codesyntax lang=”sql”]

Where 1= Case When Isnull(@EmpId,0) =0 Then 1

Else

Case When @EmpId = EmpId Then 1 Else 0 End

End

[/codesyntax]

Any condition which is evaluate always treated as true or false,

In condition there is main logic is to evaluate 1=1 or 1=0 in case of criteria matching,

Let Condition evaluation step by step

Isnull(@EmpId,0) it will simply replace Null By Zero using Isnull Function.

1 = case when Isnull(@EmpId,0) =0 Then

// Because @EmpID is null so it replace by 0

1 = Case When 0 =0 Then 1

//Switch case returns true part on success or returns else part

//it return 1 because 0=0 is true

1 = 1

So for the emp Id will have no effect in select it will return all the records. Simply by pass this condition.

Let see with String value

[codesyntax lang=”sql”]

Where 1 = Case When Isnull(len(@Name),0) =0 Then 1

Else

Case When @Name = Name Then 1 Else 0 End

End

[/codesyntax]

When you will look at the string variable, only one step is required for calculating length of string. With a simple click, it will return length of variable’s value. In case, value is null then it will return null value. And null value is replace by 0 (zero).

Isnull (len(@Name),0)

1 = Case When Isnull(len(@Name),0) =0 Then 1

1 = case when 0=0 then 1

1 = 1

Suppose the Parameter value is not null then it will go inside the Else Potion and fire this criteria for relevant records

Like

See the Procedure Call with other  values

Execute the Procedure with values

exec BestPractice1 1,null

Results

EmpId   Name    DeptId

1              admin   1

 

See the effect

[codesyntax lang=”sql”]

Where 1= Case When Isnull(@EmpId,0) =0 Then 1

Else

Case When @EmpId = EmpId Then 1 Else 0 End

End

[/codesyntax]

Condition will go in Else because

[codesyntax lang=”sql”]

Case When Isnull(@EmpId,0) =0 Then 1

Case when 1 = 0 // @EmpId id 1 so here else part going to execute

Where 1= Case When Isnull(@EmpId,0) =0 Then 1

Else

Case When @EmpId = EmpId Then 1 Else 0 End

End

[/codesyntax]

For every records it will match EmpId When It matched

criteria then return 1 else 0

Case When @EmpId = EmpId Then 1 Else 0 End

This line either return 1 or 0

So main results like this

1=1

1=0

1=0

1=0

1=0

1=0

1=0

1=0

SMART Check Condition for Date Time

Suppose you want to get specific date wise data and simply check the condition Like

[codesyntax lang=”sql”]

Select * from TableName where DateField = @CurrentDate

[/codesyntax]

Instead of these, try this

[codesyntax lang=”sql”]

Select * From TableName

Where DATEDIFF(DD,DateField,@CurrentDate) = 0

[/codesyntax]

The method will work faster and performance will be improved.

The individuals or team that one hires will exclusively work on his/her project and directly report to the concerned person and simultaneously technological and infrastructural support are given by the .Net Application Development (Link Broken).

This article has one Comment
  1. Andrew G
    January 28, 2014

    Good info, a couple of syntax issues (Data table missing #, no quotes around Peter on Insert etc) but works as an explanation.

    However, the smart date check at the end is another story. Using a function on the WHERE clause causes a index scan where a seek is possible with the first code. For SQL to know if the date difference is 0, it needs to check every record. With an indexed date column, using the first code it can immediately navigate to a matching date.
    The queries will also return a different result if the column datatype is DATETIME, however will work with DATE.


Leave a Reply
www.edatastyle.com www.introducingsomething.com