Dynamic SQL, Pivot, COALESCE, datename and EXEC examples using SQL Server

Dynamic SQL, Pivot, COALESCE, datename and EXEC examples

Before going through below example, Download adventureworks database for sql server 2008 r2

Below stored procedure will return how many "Design Engineer" were joined in the company with in from date and to date with gender into consideration. 

After creating stored procedure execute below statement

spRangaRajeshDynamic 'Design Engineer','2002-02-01','2002-02-28'

Code:

create procedure spRangaRajeshDynamic (@id varchar(30), @fromDate date, @toDate date)
as
begin
declare @_MonthDay varchar(max)
declare @_query varchar(max)
declare @fromDateCharacters varchar(30)
declare @toDateCharacters varchar(30)

set @fromDateCharacters = convert(varchar(30), cast(@fromDate as date), 102)
set @toDateCharacters = convert(varchar(30), cast(@toDate as date), 102)

Select @_MonthDay=COALESCE(@_MonthDay+']'+',[' , '[') + 
datename(mm,HireDate)+ ' '+cast(day(HireDate) as varchar) 
from [AdventureWorks2008R2].[HumanResources].[Employee] 
WHERE HireDate BETWEEN @fromDate AND @toDate 
Group by 
 month(HireDate),
 day(hiredate),
 datename(mm,HireDate)
set @_MonthDay =@_MonthDay+']'
select @_MonthDay [COALESCE Example]

set @_query=
'select * from 
(Select --Year(HireDate) as year, 
datename(mm,HireDate)+ '' ''+cast(day(HireDate) as varchar) MonthDay,
--count(JobTitle) as TotalEmployees,
JobTitle,gender
from [AdventureWorks2008R2].[HumanResources].[Employee] 
WHERE HireDate BETWEEN '''+@fromDateCharacters+''' AND '''+@toDateCharacters+'''
and jobtitle='''+@id+'''
Group by 
 month(HireDate),
 day(hiredate),
 datename(mm,HireDate),
 JobTitle,
 gender
) 
 p
 PIVOT (count(jobtitle) for 
 MonthDay in ('+@_MonthDay+') 
 --([January 15],[January 16],[January 17],[January 18],[January 19],[January 20])
 ) as pvt'
 
 exec(@_query);
end

Output:

Dynamic SQL, Pivot, COALESCE, datename and EXEC examples using SQL Server