SQL Server Nuggets

  declare @sql nvarchar(max) = N'select top 10 * from dbo.TierReport where yy=''2017'' and mm=''01'''; select * from sys.dm_exec_describe_first_ result_set(@sql,null,1) exec sp_executesql @stmt=@sql set nocount on; declare    @sql nvarchar(max) = N'select top 10 * from dbo.TierReport where yy=''2017'' and mm=''01''' , @temp_table_name sysname = N'#pivot_resultset'; select 'create table ' + @temp_table_name + '('  select '[' + [name] + '] ' + [system_type_name] + ',' from sys.dm_exec_describe_first_ result_set(@sql,null,1) select 'eof int'   select ');' select 'insert ' + @temp_table_name select 'exec sp_executesql @stmt=@sql' select 'select * from ' + @temp_table_name Table has 0-N custom attributes.  Want to get 1-N custom attributes into an XML document. CREATE PROCEDURE [dbo].[CAQH_ PivotOfficeCustomAttributeData ] as...

  /*   SCRIPT: Distinct and NULL Counts   */       SET NOCOUNT ON;   DECLARE @SCHEMA_NAME VARCHAR(128) = 'dbo'; DECLARE @TABLE_NAME VARCHAR(128) = 'CAQH_DataSummary_ ProviderExtract';   -- set query results to text   SELECT   ';WITH CTE AS (' + CHAR(13) + CHAR(10) +   'SELECT' + CHAR(13) + CHAR(10) ;     SELECT   'COUNT(DISTINCT [' +    COLUMN_NAME + ']) AS [DISTINCT_' + COLUMN_NAME + '], ' +   'SUM(CASE WHEN [' + COLUMN_NAME + ']  IS NULL THEN 1 ELSE 0 END) AS [NULL_' + COLUMN_NAME + '],' +   CHAR(13) + CHAR(10) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @SCHEMA_NAME AND TABLE_NAME = @TABLE_NAME AND DATA_TYPE <> 'xml';   SELECT   'COUNT(*) [ROW_COUNT]' + CHAR(13) + CHAR(10) +   'FROM ' + @SCHEMA_NAME + '.' + @TABLE_NAME  + CHAR(13) + CHAR(10) +   ')' + CHAR(13) + CHAR(10) ;   SELECT   'SELECT ' +   '''' + TABLE_NAME  + '...

 CREATE PROCEDURE [dbo].[OutputFileName] @p_PLAN_NAME nvarchar(15) , @p_OUTPUT_FOLDER nvarchar(128) , @p_OUTPUT_FILE_NAME nvarchar(128) as begin declare    @OUTPUT_FILE_FULL_PATH nvarchar(256) , @STATE_ABBREVIATION nchar(2) , @ERROR_MESSAGE nvarchar(128) , @MMDDYYYY nchar(8) , @CURRENT_DATE date; set @CURRENT_DATE = getdate(); set @MMDDYYYY = format(@CURRENT_DATE, 'MMddyyyy', 'en-us');   set @STATE_ABBREVIATION =  case @p_PLAN_NAME  when N'XXXML' then N'NE' when N'XXXIA' then N'IA' when N'XXXOH' then N'OH' when N'XXXTX' then N'TX' else null end; if @STATE_ABBREVIATION is null begin set @ERROR_MESSAGE = N'STATE ABBREVIATION MISSING FOR PLAN ' + @p_PLAN_NAME; raiserror(@ERROR_MESSAGE, 11, 1); end set @OUTPUT_FILE_FULL_PATH = @p_OUTPUT_FOLDER + N'\' + replace(@p_OUTPUT_FILE_NAME, N'{STATE}', @STATE...

 CREATE PROCEDURE [dbo].[ CalendarQuarterDateRange] AS begin declare    @today date , @todayquarter int , @reportyear int , @reportquarter int , @quarterbegindate datetime , @quarterenddate datetime; set @today = getdate(); set @todayquarter = datepart(quarter, @today); -- -- figure out the reportyear and reportquarter -- if @todayquarter = 1 begin set @reportyear = datepart(year, @today) - 1; set @reportquarter = 4; end else  begin set @reportyear = datepart(year, @today); set @reportquarter = @todayquarter - 1; end -- -- figure out the begin and end date for the reportquarter -- if @reportquarter = 1 begin set @quarterbegindate = datefromparts(@reportyear, 1, 1); set @quarterenddate = datefromparts(@reportyear, 3, 31); end else if @reportquarter = 2 begin set @quarterbegindate = datefromparts(@reportyear, 4, 1); set @quarterenddate = datefromparts(@reportyear, 6, 30); end else if @...