IIF() in SQL 2012

by Valmik 26. June 2012 08:01
We have used IF..ELSE and CASE..END in earlier versions of SQL to perform logical conditional operations.
However, IIF() can be used as a writing conditional CASE statements in a single T-SQL. It evaluates the expression passed in the first parameter with the second parameter depending upon the evaluation of the condition and returns either TRUE or FALSE.
 
In most of the cases we use ISNULL() function, we ofthen need to handle other cases than just comparing the operand with NULL. IIF() serves the purpose of having checks based on as many expressions as you need. However, you can nest the IIF() upto 10 levels only, just like the CASE. 
 
Example:
 
DECLARE @A INT = 10
DECLARE @B INT = 8
SELECT IIF(@A > @B, 'A IS GREATER THAN B', 'B IS GREATER THAN A')
GO;
 
Result of execution of above statement: 
-------------------
A IS GREATER THAN B  
(1 row(s) affected)
 
Another Example with expressions:
 
DECLARE @MIKE_AGE INT = 30
DECLARE @CLARA_AGE INT = 24
SELECT IIF(@MIKE_AGE > @CLARA_AGE, 'MIKE IS OLDER THAN CLARA BY '+LTRIM(STR(@MIKE_AGE-@CLARA_AGE))+' YEARS', 'CLARA IS OLDER THAN MIKE BY '+LTRIM(STR(@MIKE_AGE-@CLARA_AGE))+' YEARS')
GO;
 
Result of execution of above statement:
--------------------------------------------
MIKE IS OLDER THAN CLARA BY 6 YEARS
(1 row(s) affected)

Tags:

OFFSET & FETCH options in SQL 2012

by Valmik 26. April 2012 10:32
SQL 2012 has a new T-SQL that helps your SELCT query to implement the page solution. We have used combination of TOP and ORDER BY in the past to handle this, However, OFFSET & FETCH clauses has more benefits.
  
OFFSET: Allows you to set the record pointer to a specific row in a table
FETCH: Allows you to select number of rows you request in Fetch.
 
For OFFSET you will specify starting row and page size will be specified to FETCH.
Example:
  
SELECT * FROM TableName
ORDER BY Table_ID
OFFSET 3 ROWS
FETCH NEXT 5 ROWS ONLY
 
FETCH and OFFSET can be used with variables as well.
Below is example:
DECLARE @OFFSET INT=3, @FETCH INT=5
SELECT * FROM Table_Name
ORDER BY Table_ID
OFFSET @OFFSET ROWS
FETCH NEXT @FETCH ROWS ONLY

Tags:

Root Cause behind DBProject Error 628 TSD01261

by Valmik 4. November 2011 08:43

Most often when we deal with the multiple .sql files to be included in post-deployment script in a MS database project, We may encounter this problem and hence the project build gets broken. The error text reads something like:

Error 628 TSD01261: The syntax check failed 'Incorrect syntax near USE.' in the batch near 'USE [StagingData]
' .  0 0 StagingData

 

After some googling and some thinking(:)), I found the root cause. It errors out because it expects some separator between two files that are specified in post deployment script list specified with :r .\blah.sql

So each of the script file needs to end with the statement GO and it works perfectly fine.

Tags:

Tag cloud