mr roys - Page 12 - Developer IT

SQL SERVER – Implementing IF … THEN in SQL SERVER with CASE Statements

- by Pinal Dave

Here is the question I received the other day in email. “I have business logic in my .net code and we use lots of IF … ELSE logic in our code. I want to move the logic to Stored Procedure. How do I convert the logic of the IF…ELSE to T-SQL. Please help.” I have previously received this answer few times. As data grows the performance problems grows more as well. Here is the how you can convert the logic of IF…ELSE in to CASE statement of SQL Server. Here are few of the examples: Example 1: If you are logic is as following: IF -1 < 1 THEN ‘TRUE’ ELSE ‘FALSE’ You can just use CASE statement as follows: -- SQL Server 2008 and earlier version solution SELECT CASE WHEN -1 < 1 THEN 'TRUE' ELSE 'FALSE' END AS Result GO -- SQL Server 2012 solution SELECT IIF ( -1 < 1, 'TRUE', 'FALSE' ) AS Result; GO If you are interested further about how IIF of SQL Server 2012 works read the blog post which I have written earlier this year . Well, in our example the condition which we have used is pretty simple but in the real world the logic can very complex. Let us see two different methods of how we an do CASE statement when we have logic based on the column of the table. Example 2: If you are logic is as following: IF BusinessEntityID < 10 THEN FirstName ELSE IF BusinessEntityID > 10 THEN PersonType FROM Person.Person p You can convert the same in the T-SQL as follows: SELECT CASE WHEN BusinessEntityID < 10 THEN FirstName WHEN BusinessEntityID > 10 THEN PersonType END AS Col, BusinessEntityID, Title, PersonType FROM Person.Person p However, if your logic is based on multiple column and conditions are complicated, you can follow the example 3. Example 3: If you are logic is as following: IF BusinessEntityID < 10 THEN FirstName ELSE IF BusinessEntityID > 10 AND Title IS NOT NULL THEN PersonType ELSE IF Title = 'Mr.' THEN 'Mister' ELSE 'No Idea' FROM Person.Person p You can convert the same in the T-SQL as follows: SELECT CASE WHEN BusinessEntityID < 10 THEN FirstName WHEN BusinessEntityID > 10 AND Title IS NOT NULL THEN PersonType WHEN Title = 'Mr.' THEN 'Mister' ELSE 'No Idea' END AS Col, BusinessEntityID, Title, PersonType FROM Person.Person p I hope this solution is good enough to convert the IF…ELSE logic to CASE Statement in SQL Server. Let me know if you need further information about the same. Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Server, SQL Tips and Tricks, T SQL

Search Results

Search found 766 results on 31 pages for 'mr roys'.

Page 12/31 | < Previous Page | 8 9 10 11 12 13 14 15 16 17 18 19 | Next Page >

- by Pinal Dave

- by albertpascual

- by Roger Brinkley

- by [email protected]

- by California Ken

- by KKline

- by Bob Rhubart

- by KKline

- by Dittenhafer Solutions

- by user205162

- by Mr. Pig

- by Mr Grok

- by mr.b

- by Mr CooL

- by mr o

- by mr.b

- by Mr Bell

- by Mr Bell

- by mr.b

- by Mr. Flibble

- by Mr guy 4

- by Mr. Kakakuwa Bird

- by Mr. D

- by Mr AH

< Previous Page | 8 9 10 11 12 13 14 15 16 17 18 19 | Next Page >