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 

See Also:

• T-SQL Scripts

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

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; 

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 careful 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 :

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 :

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 :

Won Microsoft TechNet Guru Awards

Won Gold medal - Microsoft TechNet Guru Awards - Under Category -
SQL Server Reporting Services, Power View Technical Guru - July 2013

Gold medal article - SSRS: Multi Font Color / Multi Font Size within a Single Field / Textbox

Announced as Featured article on TechNet Wiki 

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

SQL Server Technet Wiki Articles

SQL Server Developer Resources
T-SQL Useful Links
SQL Server Query Language - Transact-SQL
SQL Server Reporting Services - List of TechNet Wiki Articles 
SQL Server Integration Services - List of TechNet Wiki Articles
SQL Server Analysis Services - List of TechNet Wiki Articles
TechNet Guru Contributions

See Also:

• My Posts on Technet wiki articles

SQL Server Performance tuning with Indexing

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

See Also:

• My Posts on Technet wiki articles

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('.') 

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

SQL Server - Display dynamically horizontal rows vertically

Refer my post on Technet gallery - http://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-to-display-602afad6

Refer my post on Technet Wiki - http://social.technet.microsoft.com/wiki/contents/articles/19698.sql-server-display-horizontal-rows-vertically.aspx

See Also:

• My Posts on Technet wiki articles

SSRS - Multi-valued Parameter as Stored procedure Input

In this post, I have shared about how to handle when SSRS dataset gets delimited string as input from multi valued parameter.

Refer my post on Technet Wiki 

Refer my post on Technet Wiki ( for single character multi-valued parameter ) 

See Also:

• My Posts on SSRS

• My Posts on Technet wiki articles

SSIS - Import Excel to table - Cannot convert between unicode and non-unicode string data types

Refer my post on Technet Wiki - http://social.technet.microsoft.com/wiki/contents/articles/19612.ssis-import-excel-to-table-cannot-convert-between-uniocde-and-non-unicode-string-data-types.aspx

See Also:

• My Posts on Technet wiki articles

SSRS - Compare two versions of the same report

Refer my post on Technet gallery - SSRS - Compare two versions of the same report
http://gallery.technet.microsoft.com/scriptcenter/SSRS-Compare-two-versions-44be98e5

Refer my post on Technet Wiki - SSRS - RDL Compare -
http://social.technet.microsoft.com/wiki/contents/articles/19611.ssrs-rdl-compare.aspx

See Also:

• My Posts on Technet wiki articles

T-SQL Script to generate Index Creation Scripts for all tables in a database

Refer my post on Technet gallery :

SQL Server - Generate Index Script
http://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Generate-Index-fa790441

Refer my post on Technet Wiki :

T-SQL Script to generate Index Creation Scripts for all tables in a database
http://social.technet.microsoft.com/wiki/contents/articles/19598.t-sql-script-to-generate-index-creation-scripts-for-all-tables-in-a-database.aspx

See Also:

• My Posts on Technet wiki articles

T-SQL Script to get information about Index settings

Refer my post on Technet gallery :

T-SQL Script to get detailed information about Index settings for all tables
http://gallery.technet.microsoft.com/scriptcenter/T-SQL-Script-to-get-03d0fee7

Refer my post on Technet Wiki :

T-SQL Script to get detailed information about Index settings
http://social.technet.microsoft.com/wiki/contents/articles/19593.t-sql-script-to-get-detailed-information-about-index-settings.aspx

See Also:

• My Posts on Technet wiki articles

SSRS (Matrix) - How to Repeat Headers on Each Page and Keep Headers Fixed while Scrolling

Refer my post on Technet Wiki - http://social.technet.microsoft.com/wiki/contents/articles/19505.ssrs-matrix-how-to-repeat-headers-on-each-page-and-keep-headers-fixed-while-scrolling.aspx

See Also:

• My Posts on Technet wiki articles