Here I am going to explain the solution of a business problem which I gave to one of my friends.
There is a SQL Server table like below:
Data in my table:
Now the problem is that he wants to fetch record columns for after the current month: This means if the current month is February then the result set columns should show the month record for after February (March- December) or if the current month is March then the result set should show (April- December).
So for this I wrote the below stored procedure:
- CREATEPROCEDURE [dbo].[GETEMPLOYEE]
- AS
- DECLARE @CurrentMonth INT;
- SET @CurrentMonth=(SELECTMONTH(GETDATE())AS CurrentMonth)-- Get Current Month
-
- CREATETABLE #TempTable(ID VARCHAR(2), Name char(20))
- INSERTINTO #TempTable(ID, Name)VALUES ('1','JAN')
- INSERTINTO #TempTable(ID, Name)VALUES ('2','FEB')
- INSERTINTO #TempTable(ID, Name)VALUES ('3','MAR')
- INSERTINTO #TempTable(ID, Name)VALUES ('4','APR')
- INSERTINTO #TempTable(ID, Name)VALUES ('5','MAY')
- INSERTINTO #TempTable(ID, Name)VALUES ('6','JUN')
- INSERTINTO #TempTable(ID, Name)VALUES ('7','JUL')
- INSERTINTO #TempTable(ID, Name)VALUES ('8','AUG')
- INSERTINTO #TempTable(ID, Name)VALUES ('9','SEP')
- INSERTINTO #TempTable(ID, Name)VALUES ('10','OCT')
- INSERTINTO #TempTable(ID, Name)VALUES ('11','NOV')
- INSERTINTO #TempTable(ID, Name)VALUES ('12','DEC')
-
- DECLARE @listStr VARCHAR(MAX)
- SELECT @listStr =COALESCE(@listStr+',','')+ Name
- FROM #TempTable WHERE ID> @CurrentMonth
-
- DECLARE @sqlCommand varchar(1000)
- DECLARE @columnList varchar(75)
- SET @sqlCommand ='SELECT EMP_ID, EMP_NAME, '+ @listStr +' FROM EMPLOYEE '
- EXEC (@sqlCommand)
-
- DROPTABLE #TempTable
- GO
Now Execute This Stored Procedure:
Here you can see the result set is showing columns after the current month.