Search

Advertisements

SQL Server - ORDER BY with CASE statement - Example

In this article , let us see an example for ORDER BY with CASE statement 

Consider below sample data :

DECLARE @Tmp TABLE (Id VARCHAR(5),Name VARCHAR(5))
INSERT @Tmp SELECT 1,'C'
INSERT @Tmp SELECT 2,'B'
INSERT @Tmp SELECT 3,'A'


If we are required to sort based on column and if the name of the column is passed as input , then we can make use of  ORDER BY with CASE statement 

--Sort Input
DECLARE @Sortby VARCHAR(20) = 'Name'

SELECT * FROM @Tmp
ORDER BY CASE WHEN @Sortby = 'Name' THEN Name
              ELSE Id END

--Sort Input
SET @Sortby = 'Id'

SELECT * FROM @Tmp
ORDER BY CASE WHEN @Sortby = 'Name' THEN Name
              ELSE Id END  





Reference links :

http://technet.microsoft.com/en-us/library/ms188385.aspx
http://technet.microsoft.com/en-us/library/ms188723%28v=sql.105%29.aspx 

SSRS - Divide by Zero error - Myth of IIF

In SSRS , IIF evaluates both True and False part irrespective of condition imposed .

Because of that , expressions using IIF will lead us to error in some cases .

Two common scenarios where IIF causes problem :

i. ) Divide by zero .
ii ) Argument 'Month' is not a valid value .

i . )  Divide by zero :


Refer below two MSDN blogs for explanations on how IIF causes divide by zero error and workaround for the same .

http://blogs.msdn.com/b/sqlforum/archive/2011/02/28/faq-why-does-the-attempted-to-divide-by-zero-error-still-happen.aspx

http://blogs.msdn.com/b/bwelcker/archive/2006/09/26/end-of-amnesia-_2800_avoiding-divide-by-zero-errors_2900_.aspx

ii ) Argument 'Month' is not a valid value :


Refer this link for example and explanations on how IIF causes "Argument Month is not a valid value" error and workaround for the same .

Here is the Microsoft Connect for SSRS IIF - Divide by zero error -
http://connect.microsoft.com/SQLServer/feedback/details/729872/ssrs-iif-divide-by-zero-error 

SQL Server - Concatenating string / integer with NULL

In SQL Server , SET statement - CONCAT_NULL_YIELDS_NULL -
Controls whether concatenation results are treated as null or empty string values.

Concatenation of string with NULL (Example) :

DECLARE @a VARCHAR(5) = 100
SET CONCAT_NULL_YIELDS_NULL ON;
SELECT @a + NULL;
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT @a + NULL; 




Concatenation of integer with NULL (Example) :

In case of  integer , we cannot make use of CONCAT_NULL_YIELDS_NULL ,
CONCAT_NULL_YIELDS_NULL works only with string types .

SET CONCAT_NULL_YIELDS_NULL OFF;
DECLARE @b INT = 100
SELECT @b + NULL ;
SELECT @b + ISNULL(NULL,0) ; 




In SQL Server 2012 , we have CONCAT( ) function to concatenate string with NULL .

For example :

SELECT CONCAT(100,NULL)
--returns 100


Reference - http://technet.microsoft.com/en-us/library/ms176056.aspx

SQL Server - How to find environment difference on SSMS Query window

This article is about finding environment difference on SSMS query window .

Suppose If we have access to all environments (Production,QA and Development) , we should be really carefull in executing DML scripts on respective environments .

On query window at the bottom , we can find the server name , user name , SPID and database details
as shown in below image :






We have one more option to find easily environment difference on SSMS query window
by defining colors for each  environment .

Options <<  - > Connection Properties - > Connection - > Use custom color - > Select ...




Note :
This setting applies to particular Server type , Server name , Authentication  type and User .

SSMS - How to connect to particular database when connecting to SQL Server database engine

After connecting into SQL Server Database engine and opening Query window , you will notice master database in the Available Databases list by default .

We can choose the required database from the Available Databases list .



To connect to particular database when connecting to SQL Server database engine :

Options <<  - > Connection Properties - >  Connect to database - >  <Browse Server ...>










Note :
This setting applies to particular Server type , Server name , Authentication  type and User .

SSRS - SUM based on condition

In this article , let us see an example on how to SUM based on condition in SSRS tablix report .

Consider below query is my report dataset :

DECLARE @tmp TABLE (Code VARCHAR(10),Level1 INT,Level2 INT)
INSERT @tmp SELECT 'CHN',5,10
INSERT @tmp SELECT 'KOL',5,15
INSERT @tmp SELECT 'IND',10,25
INSERT @tmp SELECT 'NY',5,10
INSERT @tmp SELECT 'TX',5,15
INSERT @tmp SELECT 'US',10,25
SELECT * FROM @tmp


Consider , I have tablix report as shown in below image :



In the above shown report , If we are required to SUM values in the Columns Level1 and Level2
, only for Code = "IND" and Code = "US" , then we need to write expression as shown below :

In the Total data row , under Level1 column expression:

=Sum(IIF(Fields!Code.Value="IND" OR Fields!Code.Value="US",Fields!Level1.Value,0))

In the Total data row , under Level2 column expression:

=Sum(IIF(Fields!Code.Value="IND" OR Fields!Code.Value="US",Fields!Level2.Value,0))




Reference : 
http://technet.microsoft.com/en-us/library/ms159134(v=sql.100).aspx

Won Microsoft TechNet Guru Awards

Awarded as Microsoft Community Contributor

What is a Microsoft Community Contributor?

 It is a badge offered by Microsoft which is designed to showcase those who make notable contributions to Microsoft online communities such as TechNet, MSDN and Microsoft Community.

Microsoft Community Contributor (FAQ's) -  https://www.microsoftcommunitycontributor.com/faq.aspx

Current recipients list of the Microsoft Community Contributor badge - https://www.microsoftcommunitycontributor.com/current-recipients.aspx

/**********************************************************************/

I was Awarded as "Microsoft Community Contributor"  - July 2013





/**********************************************************************/

SQL Server Technet Wiki Articles

SQL Server Performance tuning with Indexing

Refer my post on Technet Wiki - SQL Server Performance tuning with Indexing

SQL Server - ISNUMERIC

ISNUMERIC - Determines whether an expression is a valid numeric type .
Returns 1 if it is a valid numeric type else 0 .

--Valid Scenarios (ISNUMERIC returns 1)

--valid numbers
SELECT ISNUMERIC(12345)

--valid numbers inside quotes
SELECT ISNUMERIC('12345')

--valid numbers with comma separated
SELECT ISNUMERIC('1,23,456')

--decimal type
SELECT ISNUMERIC(12345.)

--decimal type
SELECT ISNUMERIC(123.45)

--decimal type
SELECT ISNUMERIC(.12345)

--decimal type
SELECT ISNUMERIC('.')

--plus and minus symbols are allowed in the beginning
SELECT ISNUMERIC('+')

--plus and minus symbols are allowed in the beginning
SELECT ISNUMERIC(+12345)

--plus and minus symbols are allowed in the beginning
SELECT ISNUMERIC(-12345)

--money symbols are allowed in the beginning
SELECT ISNUMERIC('$')

--money symbols are allowed in the beginning
SELECT ISNUMERIC($12345)

--Exponential function
SELECT ISNUMERIC(123E2)

--Exponential function
SELECT ISNUMERIC(123e-2)




--Invalid Scenarios (ISNUMERIC returns 0)


--dots are allowed to represent decimal & float type(multiple dots are not allowed)
SELECT ISNUMERIC('12.3.45')

--symbols are allowed only in the beginning
SELECT ISNUMERIC('12345$')

--symbols are allowed only in the beginning
SELECT ISNUMERIC('12345+')

-- Other symbols are not allowed except +,- and money symbols
SELECT ISNUMERIC('*12345')

--Invalid exponential function
SELECT ISNUMERIC('1232E')

--Invalid exponential function
SELECT ISNUMERIC('12E3E2')

--Invalid exponential function
SELECT ISNUMERIC('E1232')

--Obviously alphabets are not allowed
SELECT ISNUMERIC('A1232')


Reference links :
http://technet.microsoft.com/en-us/library/ms186272.aspx
http://technet.microsoft.com/en-us/library/ms179882.aspx

SQL Server - Script to find particular column from all tables/views except system databases

Below script can be used to find particular column from all tables/views in all databases except system databases

IF OBJECT_ID('tempdb..##SearchColumn') IS NOT NULL
BEGIN
DROP TABLE ##SearchColumn
END
CREATE TABLE ##SearchColumn (ObjectName NVARCHAR(200))
EXECUTE sp_MSforeachdb '
IF ''?'' NOT IN (''master'',''model'',''Msdb'',''tempdb'') --system databases
BEGIN
    USE [?]
      INSERT ##SearchColumn
      SELECT Object_name(object_id)
      FROM Sys.Columns
      WHERE name LIKE ''%BusinessEntityID%'' --Pass ColumnName
END'
SELECT * FROM ##SearchColumn

Advertisements