Cool Tips‎ > ‎Data Formatting‎ > ‎

Data Formatting - Dynamic Projection of Columns from a Query

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Anthony Zackin
Date:        27 Jan 2009

Description

Defn:  Projection = A Relational Algebra term that is more commonly referred to as SELECT.  A full definition can be found on Wikipedia.
 
Many thanks to Anthony Zackin for this comprehensive piece of work.
 
Basically, this stored proc allows a dynamic number of columns to be retruned by a query, either directly by name, or indirectly via wildcard matching.
 
I have fought many times with problems requiring the returning of a dynamic column set, although never as tricky a problem as actually using wildcards to select the columns.  Mostly, I've needed to use stored application table metadata (before Master Data Management) to construct queries.  This involved a lot of repetetive text string manipulation.  Having this stored proc would have made it a breeze and produced much less verbose code.
 
There is extensive documentation/help within the SP definition, and familiarity is best gained through experimentation.
 
 

Code

 Supporting Function:

USE MASTER

-- =============================================
-- Author: Anthony Zackin
-- Create date: 2009-01-27
-- Description: Right justifies text in a varchar
-- Arguments: First argument is the string to justify.
-- Argument 2 is the length of the right justified text.
-- Result: An VARCHAR(8000) with a length of @outLen containing
-- the text of @s right justified to @outLen characters.
-- Example: SELECT CAST(DBO.fnJustRight('abcdefgjijklmnopqrstuvwxyz', 25) AS CHAR(25))
-- ==> "bcdefgjijklmnopqrstuvwxyz"
-- =============================================
IF OBJECT_ID('master.dbo.fnJustRight') IS NOT NULL
  
DROP FUNCTION dbo.fnJustRight
GO

CREATE FUNCTION dbo.fnJustRight
(
  
@s VARCHAR(8000), @outLen int
)
RETURNS VARCHAR(8000)
AS
BEGIN
   RETURN
SUBSTRING(@s, 1+LEN(@s)-@outLen, @outLen)
END
GO


 Stored Procedure:

IF OBJECT_ID('sp_wcProject') IS NOT NULL
  
DROP PROC sp_wcProject
GO

CREATE PROC sp_wcProject
          
@object         SYSNAME = NULL,
          
@colNames       VARCHAR(8000) = '%',
          
@sqlString      VARCHAR(8000) = NULL OUT,
          
@orderByName    BIT = 0,
          
@maxWidth       VARCHAR(5) = NULL,
          
@where          VARCHAR(8000) = NULL,
          
@orderBy        VARCHAR(8000) = NULL,
          
@top            VARCHAR(10) = ''
AS
SET NOCOUNT ON

DECLARE
  
@n                      INT,
  
@fn_args                VARCHAR(1000)  
  
@s                      VARCHAR(8000),
  
@t                      VARCHAR(50),
  
@ORfilter               VARCHAR(8000),
  
@ANDfilter              VARCHAR(8000),
  
@colFilter              VARCHAR(8000),
  
@i                      INT,
  
@tempTable              SYSNAME,
  
@exec                   TINYINT,
  
@db                     SYSNAME,
  
@rightJust              BIT,
  
@binToChar              BIT,    
  
@selName                VARCHAR(8000),  
  
@parmDef                NVARCHAR(500),
  
@cmd                    VARCHAR(8000),
  
@nv                     NVARCHAR(MAX) -- sp_executesql requires UNICODE
      
  
SET @fn_args = ''
SET @n = CHARINDEX('(', @object)

IF @n > 1 -- Don't allow "(" as the first character
BEGIN
   SET
@fn_args = SUBSTRING(@object,@n,1+LEN(@object)-@n)
  
SET @object = SUBSTRING(@object,1,@n-1)
END

IF
OBJECT_ID(@object) IS NULL
BEGIN
   IF
@object IS NOT NULL AND @object NOT LIKE '%?%'
  
BEGIN
       PRINT
'The object, ''' + @object + ''', does not exist in the current database: ''' + db_name() + ''''
      
PRINT 'Omit the @object argument for help.'
      
RETURN
   END
   PRINT
'sp_wcProject version 3.0.0 [Copyright 2009 Anthony Zackin, Takamomto LLC]'
  
PRINT ''
  
PRINT 'Syntax:'
  
PRINT ''
  
PRINT 'sp_wcProject ''[ [ <database_name> . ] <schema_name> .] [@object=]<table, view, or table-valued function>'''
  
PRINT '              [, [@colNames=]''<[-]column name pattern> [ , ...n ] ]'''
  
PRINT '              [, [@sqlString]=<''P'' | a variable declared as VARCHAR(8000)>]'
  
PRINT '              [, [@orderByName]=0|1]'
  
PRINT '              [, [@where=]<where clause>]'
  
PRINT '              [, [@orderBy=]<order by column list>'
  
PRINT '              [, [@maxWidth=]<[-]maximum width of character columns[B]>'
  
PRINT '              [, [@top=]<number which limts rows returned>'
  
PRINT ''
  
PRINT '- An invalid or missing @object will cause this help text to be displayed.  You should'
  
PRINT '  fully qualify the @object argument to be safe, e.g., ''pubs.dbo.authors''.'
  
PRINT ''
  
PRINT '- This proc will display the contents of the required argument, @object, a table, view, or'
  
PRINT '  table-valued function, displaying the columns which are specified in the comma-separated list'
  
PRINT '  argument @colNames.  This argument list should contain full column names or partial names with'
  
PRINT '  wildcards to match a set of columns. If @colNames is omitted then all the columns are included.'
  
PRINT '  Note: you can use an underscore to match embedded spaces in a column name.'
  
PRINT ''
  
PRINT '- Column name token strings in @colNames may begin with a minus sign to indicate that all columns'
  
PRINT '  which match the token should be EXCLUDED from the projection.  If only excluded values are'
  
PRINT '  specified then all columns not excluded will be displayed.  For example, if @colNames=''-is%'''
  
PRINT '  then all column names not beginning with ''is'' will be included.  The command,'
  
PRINT '  "sp_wcProject ''sys.databases'',''name,is%,-%on''" will display the ''name'' column and all columns'
  
PRINT '  starting with ''is'' except for those which end with ''on'' such as ''is_quoted_identifier_on''.'
  
PRINT ''
  
PRINT '- The @sqlString argument, if specified, must be either be an INITIALIZED (non-NULL) string variable'
  
PRINT '  defined as VARCHAR(8000) or the string value ''P''.  If the former, the SQL command which will'
  
PRINT '  generate the result set is returned in the output variable which can subsequently be used in a'
  
PRINT '  more complex query, e.g., a JOIN or UNION.  If ''P'' then the SQL command string which will'
  
PRINT '  generate the result set will be printed allowing manual modifications.  If omitted the query will'
  
PRINT '   be run and the result set will be returned.'
  
PRINT ''
  
PRINT '- Note that the @sqlString argument variable must be non-NULL (an empty string is okay) and must be'
  
PRINT '  qualified with the OUT attribute to receive the returned SQL SELECT statement.  See the example below.'
  
PRINT ''
  
PRINT '- The @orderByName argument, if specified, allows you to partially control the order of the columns in'
  
PRINT '  the select statement.  Specify @orderByName=1 for alphabetical order.  Omit it or specify'
  
PRINT '  @orderByName=0 for the default column ordering.'
  
PRINT ''
  
PRINT '- The optional @maxWidth argument may be used to limit the displayed size of [N]CHAR and [N]VARCHAR'
  
PRINT '  strings.  If a negative value is specified then the RIGHTMOST characters of the string value which'
  
PRINT '  fit in the specified maximum width are displayed.'
  
PRINT ''
  
PRINT '- The @maxWidth numeric value may be optionally followed by the letter "B".  (In this case you must'
  
PRINT '  quote the argument, e.g. @maxWidth=''20B''.)  "B" signifies that BINARY data will be converted to'
  
PRINT '  character and will, therefore, have its display size limited to the @maxWidth numeric value.'
  
PRINT ''
  
PRINT '- The @top numeric value, if specified, adds a TOP(@top) clause to the select statement.'
  
PRINT ''
  
PRINT '- You can use the optional @where and @orderBy arguments to further qualify the generated SQL.'
  
PRINT ''
  
PRINT 'Examples:'
  
PRINT ''
  
PRINT '  declare @sql VARCHAR(8000)'
  
PRINT '  set @sql='''' -- MAKE SURE THE STRING IS INITIALIZED TO SOMETHING'
  
PRINT '  exec sp_wcProject ''SYS.DATABASES'', ''name,database_id,reco%,%ansi%'', @sql out'
  
PRINT ''
  
PRINT '- returns the following sql statement in @sql:'
  
PRINT ''
  
PRINT '  SELECT name,database_id,recovery_model,recovery_model_desc,'
  
PRINT '         is_ansi_null_default_on,is_ansi_nulls_on,is_ansi_padding_on,is_ansi_warnings_on'
    
PRINT '  FROM SYS.DATABASES'
  
PRINT ''
  
PRINT '==='
  
PRINT ''
  
PRINT '  exec sp_wcProject ''sys.databases'',''name,database_id,%desc,%ansi%,-log%'',@where=''owner_sid<>1'',@orderBy=''name'',@maxWidth=20'
  
PRINT ''
  
PRINT '- returns a result set like the following:'
  
PRINT ''
  
PRINT '  name                 database_id user_access_desc     state_desc           snapshot_isolation_state_desc recovery_model_desc  page_verify_option_desc is_ansi_null_default_on is_ansi_nulls_on is_ansi_padding_on is_ansi_warnings_on'
  
PRINT '  -------------------- ----------- -------------------- -------------------- ----------------------------- -------------------- ----------------------- ----------------------- ---------------- ------------------ -------------------'
  
PRINT '  AdventureWorks       7           MULTI_USER           ONLINE               OFF                           SIMPLE               CHECKSUM                0                       1                1                  1'
  
PRINT '  Northwind            5           MULTI_USER           ONLINE               OFF                           SIMPLE               TORN_PAGE_DETECTION     0                       0                0                  0'
  
PRINT '  Pubs                 6           MULTI_USER           ONLINE               OFF                           SIMPLE               TORN_PAGE_DETECTION     0                       0                0                  0'
  
PRINT ''
  
PRINT '==='
  
PRINT ''
  
PRINT '  exec sp_wcProject ''master.sys.sysfiles'',''%name%,%id'',@orderbyname=1'
  
PRINT ''
  
PRINT '- returns a result set like the following:'
  
PRINT ''
  
PRINT '  fileid filename                                                              groupid name'
  
PRINT '  ------ --------------------------------------------------------------------- ------- --------------'
  
PRINT '  1      c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf   1       master'
  
PRINT '  2      c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf  0       mastlog'
  
PRINT ''
  
PRINT '==='
  
PRINT ''
  
PRINT '- to access selected columns of the PUBS log you might use something like the following:'
  
PRINT ''
  
PRINT '  exec sp_wcProject ''pubs.sys.fn_dblog(null,null)'',''current_lsn,operation,page_id,slot_id%row%,Checkpoint%,m%lsn,spid,Tran%Begin%,lock%,%contents%'',@maxWidth=''-22b'''
  
PRINT ''
  
RETURN
END

-- Parse the column names and create a WHERE clause:
SET @tempTable = '##wcProject' + CAST(@@SPID AS VARCHAR(5))
SET @ANDfilter = ''
SET @ORfilter = ''
SET @s = @colNames

WHILE LEN(@s) > 0
BEGIN
   SET
@i = CHARINDEX(',',@s)
  
IF @i = 0                                              -- Last or only token. Clear @s to end loop
      
BEGIN
           SET
@t = LTRIM(RTRIM(@s))
          
SET @s = ''
      
END
   ELSE                                                    
-- Extract the token and remove it from @s
      
BEGIN
           SET
@t = LTRIM(RTRIM(LEFT(@s,@i-1)))
          
SET @s = RIGHT(@s,LEN(@s)-@i)
      
END

   IF
LEFT(@t,1) = '-'
      
SET @ANDfilter = @ANDfilter + 'name NOT LIKE ''' + SUBSTRING(@t,2,LEN(@t)-1) + ''' AND '
  
ELSE
       SET
@ORfilter = @ORfilter + 'name LIKE ''' + @t + ''' OR '
END

IF
LEN(@ANDfilter) >= 4
  
SET @ANDfilter = SUBSTRING(@ANDfilter,1,LEN(@ANDfilter) - 4)
ELSE
   SET
@ANDfilter = ''

IF LEN(@ORfilter) >= 3
  
SET @ORfilter = '(' + SUBSTRING(@ORfilter,1,LEN(@ORfilter) - 3) + ')'
ELSE
   SET
@ORfilter = ''

IF @ANDfilter = '' AND @ORfilter = ''
  
SET @colFilter = '1=1'
ELSE
  
--BEGIN
  
IF @ANDfilter = ''                                     -- @ORfilter is NOT an empty string
      
SET @colFilter = @ORfilter
  
ELSE
       IF
@ORfilter = ''                                  -- @ANDfilter is NOT an empty string
          
SET @colFilter = @ANDfilter
      
ELSE
           SET
@colFilter = @ORfilter + ' AND ' + @ANDfilter
  
--END

SET @rightJust = 0
SET @binToChar = 0
IF @sqlString IS NULL SET @exec = 1
IF @sqlString = 'P' SET @exec = 2

IF @maxWidth IS NULL
  
SET @maxWidth = 'NULL'                                 -- This must be the character representation for concatenation
ELSE
   BEGIN                                                  
-- Check for leading minus or trailing "B"
      
IF LEFT(@maxWidth,1) = '-'                         Leading minus = Right justify
            
BEGIN
               SET
@rightJust = 1
              
SET @maxWidth = SUBSTRING(@maxWidth,2,LEN(@maxWidth)-1)
            
END
       IF
RIGHT(@maxWidth,1) = 'B'                            -- Trailing "B" = Convert binary to char for maxWidth
            
BEGIN
               SET
@binToChar = 1
              
SET @maxWidth = LEFT(@maxWidth,LEN(@maxWidth)-1)
            
END
   END

IF
@maxWidth = ''
  
SET @maxWidth = 'NULL'                 -- If no numeric value then treat as if omitted

IF OBJECT_ID('tempdb..' + @tempTable) IS NOT NULL EXEC('DROP TABLE ' + @tempTable)       -- Clean up any existing table in case

SELECT @db = COALESCE(PARSENAME(@object,3), DB_NAME()) -- Get explicit db name or, if not specified, then use the current

SET @sqlString = 'USE ' + @db +                           -- USE @db is necessary since <db>.sys.all_columns doesn't always work as expected
  
' SELECT name, column_id,' +                     -- If @maxWidth = 'NULL' then max_length will be used
              
' CASE WHEN type_name(system_type_id) LIKE ''%CHAR''' +
              
CASE @binToChar
                  
WHEN 1 THEN ' OR type_name(system_type_id) LIKE ''%BINARY'''
                  
ELSE ''
              
END +
                    
' THEN CASE WHEN max_length > ' + @maxWidth + ' THEN ' + @maxWidth + ' ELSE max_length END ' +
                    
' ELSE 0' +                      -- Non-char are not affected
--    The following commented code applies @maxWidth to non-character types:
-- ' CASE WHEN user_type_id = 61 THEN 0' + -- DateTime
--   ' WHEN user_type_id = 36 THEN 0' + -- GUID
--   ' WHEN max_length < precision THEN precision' +
--   ' WHEN max_length < 5 THEN 5' + -- Minimum column width = 5 (needed for certain types)
--   ' ELSE 0' +
-- ' END' +
              
' END AS max_length' +
  
' INTO ' + @tempTable + ' FROM sys.all_columns WHERE object_id = ' +
  
CAST(OBJECT_ID(@object) AS SYSNAME) +
  
' ORDER BY ' + CASE @orderByName WHEN 1 THEN 'name' ELSE 'column_id' END

IF
@sqlString IS NULL                                 -- This shouldn't happen since an invalid object should display the help text
BEGIN
   PRINT
'sp_wcProject: ' + @object + ' not found in ' + @db + ' database'
  
IF OBJECT_ID('tempdb..' + @tempTable) IS NOT NULL EXEC('DROP TABLE ' + @tempTable)        -- Clean up any existing table in case
  
RETURN
END

EXEC
(@sqlString)

IF @maxWidth = 'NULL'                                 -- If field was NULL it was converted to a string representation
  
SET @selName = 'SELECT @sql = @sql + ' + '''['' + name + ''],''' + ' FROM ' + @tempTable
ELSE
   IF
@rightJust = 0
      
SET @selname = 'SELECT @sql = @sql +
CASE WHEN max_length > 0 THEN
''CAST(['' + name + ''] AS VARCHAR('' + CAST(max_length AS VARCHAR(5)) + '')) AS ['' + name + ''],''
ELSE
''['' + name + ''],''
END
FROM '
+ @tempTable
  
ELSE    -- @maxWidth was negative so right justify the chars and varchars
      
SET @selname = 'SELECT @sql = @sql +
CASE WHEN max_length > 0 THEN
''CAST(master.dbo.fnJustRight(['' + name + ''],'' +
CAST(max_length AS VARCHAR(5)) + '') AS VARCHAR('' + CAST(max_length AS VARCHAR(5)) + '')) AS ['' + name + ''],''
ELSE
''['' + name + ''],''
END
FROM '
+ @tempTable

IF isNumeric(@top)=1 SET @top = 'TOP(' + @top + ')'
SET @sqlString = 'USE ' + @db +                           -- USE @db needs to get issued here if @db <> current db
  
' DECLARE @sql VARCHAR(8000) ' +
  
'SET @sql = '''' ' + @selName +
  
' WHERE ' + @colFilter +
  
' IF @sql = '''' PRINT ''sp_wcProject: No columns match ' + '''''' + @colNames + '''''''' + ' ELSE ' +
  
' BEGIN SET @sql = ''SELECT ' + @top + ' '' + LEFT(@sql,LEN(@sql)-1) + '' FROM '' + ''' +
  
@object + @fn_args + ''' ' +
  
CASE WHEN @where IS NOT NULL THEN '+ '' WHERE ' + REPLACE(@where,'''','''''') + '''' ELSE '' END +
  
CASE WHEN @orderBy IS NOT NULL THEN '+ '' ORDER BY ' + REPLACE(@orderBy,'''','''''') + '''' ELSE '' END +
  
CASE WHEN @exec = 1 THEN ' EXEC (@sql)' ELSE ' SELECT @cmdout=@sql' END +
  
' END'

SET @parmDef = '@cmdout VARCHAR(8000) OUTPUT'
SET @nv = @sqlString

EXECUTE sp_executesql @nv, @parmDef, @cmdout=@cmd OUTPUT

IF @exec = 2
  
PRINT @cmd
SET @sqlString = @cmd
GO


Test Code: 

EXEC sp_wcProject 'sys.databases','name,database_id,%desc,%ansi%',@where='owner_sid<>1',@orderBy='name',@maxWidth=10

EXEC sp_wcProject 'sys.databases','name,database_id,%desc,%ansi%,-log%,-page%',@where='owner_sid<>1',@orderBy='name',@maxWidth=10

EXEC sp_wcProject 'sys.databases','-source%,-co%,-is%,-%sid,-%guid,-log%',@where='owner_sid<>1',@orderBy='name',@maxWidth=10

EXEC sp_wcProject 'sys.databases','name,is%,-%on'


 
ċ
Dynamic projection of columns from a query.sql
(15k)
Andy Hughes,
Jun 8, 2012, 7:09 AM
Comments