Case in ORDER BY (or) Custom ORDER BY in SQL

Recently i have experienced with "Case in ORDER BY" in SQL Server.

Let's take a scenario:

I want  Week Names in Proper Order (MonDay to SaturDay) wile i am using ORDER BY Clause in sql.

While i gone for ORDER BY.. then i received DAY_NAME like this......

To get the DAY_NAME properly we have to use case in ORDER BY....

select DAY_NAME from Dim_Date

order by 

case    WHEN DAY_NAME = 'Monday' THEN '1'

        WHEN DAY_NAME = 'Tuesday' THEN '2'

        WHEN DAY_NAME = 'Wednesday' THEN '3'

        WHEN DAY_NAME = 'Thursday' THEN '4'

        WHEN DAY_NAME = 'Friday' THEN '5'

        WHEN DAY_NAME = 'Saturday' THEN '6'

        WHEN DAY_NAME = 'Sunday' THEN '7'

ELSE DAY_NAME END ASC

OUTPUT:

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Sunday

NOTE: It is very much useful, In case you have to show the Country List drop down. Here you want to show "INDIA " as First Name to be load in top.

Create Custom Error Message for Error Handle (RAISERROR) in SQL server

It is very easy to create your own Error text in SQL Server Error handler.
We can achive this by using sp_addmessage .

In sp_addmessage there are few parameter (Message number,Severity,Message text,Language,log and replace message)

But as of now we will use following :

exec sp_addmessage @msgnum=50001,@severity=1,@msgtext='This is Custom Error Message created By Pritam'

While giving @msgnum have to remember it should be unique. For that we have to check whether given @msgnum is exists or not.

select max(message_id) from sys.messages

Output:

49913

NB: User-defined error messages must have an message_id greater than 50000.

Now we have to set the @severity.To set this we have to know about severity level (10 to 24).

Follow URL:

https://technet.microsoft.com/en-us/library/aa937483%28v=sql.80%29.aspx

Now you can see your custom message in  sys.messages

Now you can call this error message by using:

RAISERROR ( 50001,1,1)

Output:

This is Custom Error Message created By Pritam

Msg 50001, Level 1, State 1

You can use this RAISERROR ( 50001,1,1) as Custom Error message.

REBUILD all the index existing in Database

You can run the codes by Stored procedure also.

DECLARE @Table VARCHAR(255)  

DECLARE @fillfactor INT

DECLARE @cmd NVARCHAR(500) 

SET @fillfactor = 90

  

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM INFORMATION_SCHEMA.TABLES

  WHERE table_type = ''BASE TABLE'''  

 

   -- create table cursor 

   EXEC (@cmd) 

   OPEN TableCursor  

 

  FETCH NEXT FROM TableCursor INTO @Table  

   WHILE @@FETCH_STATUS = 0  

   BEGIN  

-- SQL 2005 or higher command

           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

           EXEC (@cmd)

--      

       FETCH NEXT FROM TableCursor INTO @Table  

   END  

   CLOSE TableCursor  

   DEALLOCATE TableCursor

   EXEC sp_updatestats

Code explanation:

1. We have created a cursor called "TableCursor" which is running through a  "@cmd" variable.

2. Now we have to loop the cursor until @@FETCH_STATUS = 0.

3. @@FETCH_STATUS = 0 means the entire table values has read.

4. Now alter all existing index in a table and make REBUILD.

5. At last we have to run  sp_updatestats sp to configure statistics of indexes.

Week of Month calculation in sql server

In SQL server you can't get the Week of Month calculation.

To archive the situation please go through given code.

Here GETDATE()='2015-05-15'

select datediff(week, dateadd(month, datediff(month, 0, GETDATE()), 0), GETDATE()) +1 AS  WEEK_OF_MONTH

In this formula e "Zero" indicates '1900-01-01'

OR

select datediff(week, dateadd(month, datediff(month, '1900-01-01', GETDATE()), '1900-01-01'), GETDATE()) +1 AS  WEEK_OF_MONTH

Step by step:

select datediff(month, 0, GETDATE())--1384 months (In sql server default start date is 1900-01-01)

select dateadd(month, 1384, 0)--2015-05-01

select datediff(week,'2015-05-01',GETDATE())+1 --Ans: 3

Try and Catch block in sql

Here, one of important things is "Why should we use this try and catch ?"
Because sql server already provided Error handler. If we write a wrong code or syntax in sql server window..then we will get some error indignation immediately.

In various transnational scope this "Try catch" is more important. Because through this we can write our custom action in sql.

For example, while booking train ticket you filled up entire details in reservation form then save the details and while go for payment option it is getting failure. Now is the ticket book for you?

BEGIN TRY
     
       1. Passengers details filled up
       2. Complete internet payment
       3. Book() commit

END TRY

BEGIN CATCH  
     
        Book() role back

END CATCH

BEGIN TRY

       SELECT 1/0;   

   END TRY

  

   BEGIN CATCH   

 

         SELECT

         ERROR_NUMBER() AS ErrorNumber,

         ERROR_SEVERITY() AS ErrorSeverity,

         ERROR_STATE() as ErrorState,

         ERROR_PROCEDURE() as ErrorProcedure,

         ERROR_LINE() as ErrorLine,

         ERROR_MESSAGE() as ErrorMessage;  

   

   END CATCH

Output:

ErrorNumber     ErrorSeverity    ErrorState    ErrorProcedure       ErrorLine     ErrorMessage

8134                         16                         1                     NULL                 2           Divide by zero

Here One more real time example given below:

For SQL server User creation in SSMS:

  begin try

   if not exists (select 1 from master.sys.syslogins where name='Sql_user_Pritam')

   --login not existing

       

             execute('create login Sql_user_Pritam with password=''##########'', check_policy=on')

  --commit

    commit transaction

  end try

 

begin catch

    --rollback on error

    rollback transaction

    declare @ErrorMessage varchar(max)

    set @ErrorMessage=error_message()

    raiserror(@ErrorMessage, 16, 1)

  end catch

Week of Year Calculation (logic)

We know that in sql server we have a lib function for week of year calculation.
But we need to know the calculation behind that. Here is the actual calculation given below.

DECLARE @Inputdate DATE='2015-01-01'

DECLARE @ORDINAL INT

DECLARE @RESULT FLOAT

IF (datepart(yy,@Inputdate)%100=0 and datepart(yy,@Inputdate)%4=0)

      BEGIN

            SET @ORDINAL=

                CASE WHEN datepart(mm,@Inputdate)=1 then 0

                         WHEN datepart(mm,@Inputdate)=2 then 31

                         WHEN datepart(mm,@Inputdate)=3 then 60

                       WHEN datepart(mm,@Inputdate)=4 then 91

                       WHEN datepart(mm,@Inputdate)=5 then 121

                       WHEN datepart(mm,@Inputdate)=6 then 152

                       WHEN datepart(mm,@Inputdate)=7 then 182

                       WHEN datepart(mm,@Inputdate)=8 then 213

                       WHEN datepart(mm,@Inputdate)=9 then 244

                       WHEN datepart(mm,@Inputdate)=10 then 274

                       WHEN datepart(mm,@Inputdate)=11 then 305

                  ELSE 335 END

      SET @ORDINAL=@ORDINAL+datepart(DD,@Inputdate)

      SET @RESULT=(@ORDINAL-(datepart(DW,@Inputdate)-1)+10)/7

     

      SELECT CAST(@RESULT AS INT) WEEK_OF_YEAR

      END

ELSE

      BEGIN

      SET @ORDINAL=

               CASE WHEN datepart(mm,@Inputdate)=1 then 0

                        WHEN datepart(mm,@Inputdate)=2 then 31

                        WHEN datepart(mm,@Inputdate)=3 then 59

                        WHEN datepart(mm,@Inputdate)=4 then 90

                        WHEN datepart(mm,@Inputdate)=5 then 120

                        WHEN datepart(mm,@Inputdate)=6 then 151

                        WHEN datepart(mm,@Inputdate)=7 then 181

                        WHEN datepart(mm,@Inputdate)=8 then 212

                        WHEN datepart(mm,@Inputdate)=9 then 243

                        WHEN datepart(mm,@Inputdate)=10 then 273

                        WHEN datepart(mm,@Inputdate)=11 then 304

                  ELSE  334 END

      SET @ORDINAL=@ORDINAL+datepart(DD,@Inputdate)       

      SET @RESULT=(@ORDINAL-(datepart(DW,@Inputdate)-1)+10)/7

     

      SELECT CAST(@RESULT AS INT) WEEK_OF_YEAR

      END