Dynamic SQL, Pivot, COALESCE, datename and EXEC examplesBefore 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'
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
|Dynamic SQL, Pivot, COALESCE, datename and EXEC examples using SQL Server|