Search

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 :




See Also:


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

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


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