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:

table

Data in my table:

data

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:

code

  1. CREATEPROCEDURE [dbo].[GETEMPLOYEE]
  2. AS
  3. DECLARE @CurrentMonth INT;
  4. SET @CurrentMonth=(SELECTMONTH(GETDATE())AS CurrentMonth)-- Get Current Month
  6. CREATETABLE #TempTable(ID VARCHAR(2), Name char(20))
  7. INSERTINTO #TempTable(ID, Name)VALUES ('1','JAN')
  8. INSERTINTO #TempTable(ID, Name)VALUES ('2','FEB')
  9. INSERTINTO #TempTable(ID, Name)VALUES ('3','MAR')
  10. INSERTINTO #TempTable(ID, Name)VALUES ('4','APR')
  11. INSERTINTO #TempTable(ID, Name)VALUES ('5','MAY')
  12. INSERTINTO #TempTable(ID, Name)VALUES ('6','JUN')
  13. INSERTINTO #TempTable(ID, Name)VALUES ('7','JUL')
  14. INSERTINTO #TempTable(ID, Name)VALUES ('8','AUG')
  15. INSERTINTO #TempTable(ID, Name)VALUES ('9','SEP')
  16. INSERTINTO #TempTable(ID, Name)VALUES ('10','OCT')
  17. INSERTINTO #TempTable(ID, Name)VALUES ('11','NOV')
  18. INSERTINTO #TempTable(ID, Name)VALUES ('12','DEC')
  20. DECLARE @listStr VARCHAR(MAX)
  21. SELECT @listStr =COALESCE(@listStr+',','')+ Name
  22. FROM #TempTable WHERE ID> @CurrentMonth
  24. DECLARE @sqlCommand varchar(1000)
  25. DECLARE @columnList varchar(75)
  26. SET @sqlCommand ='SELECT EMP_ID, EMP_NAME, '+ @listStr +' FROM EMPLOYEE '
  27. EXEC (@sqlCommand)
  29. DROPTABLE #TempTable
  30. GO
Now Execute This Stored Procedure:

Stored Procedure

Here you can see the result set is showing columns after the current month.