Customized Output Labels

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPe

  • How about using extended properties instead?

    This approach will use field names from extended properties, or the original field name if the property is missing:

    create

    table customer

      ( Cust_cd char(4) not null primary key

      , Cust_lnm varchar(50)

      , Cust_fnm varchar(50)

    )

    GO

    CREATE

    VIEW vwReport1 AS

    SELECT cust_cd, cust_lnm, cust_fnm FROM customer

    GO

    exec

    sp_addextendedproperty 'INTERNAL_ELEMENT_LABEL', '"Customer Code"', 'USER', 'dbo', 'VIEW', 'vwReport1', 'COLUMN', 'cust_cd'

    exec sp_addextendedproperty 'CLIENT_ELEMENT_LABEL', 'Code', 'USER', 'dbo', 'VIEW', 'vwReport1', 'COLUMN', 'cust_cd'

    exec sp_addextendedproperty 'INTERNAL_ELEMENT_LABEL', '"Customer Last Name"', 'USER', 'dbo', 'VIEW', 'vwReport1', 'COLUMN', 'cust_lnm'

    exec sp_addextendedproperty 'CLIENT_ELEMENT_LABEL', '"Last Name"', 'USER', 'dbo', 'VIEW', 'vwReport1', 'COLUMN', 'cust_lnm'

    exec sp_addextendedproperty 'INTERNAL_ELEMENT_LABEL', '"Customer First Name"', 'USER', 'dbo', 'VIEW', 'vwReport1', 'COLUMN', 'cust_fnm'

    exec sp_addextendedproperty 'CLIENT_ELEMENT_LABEL', '"First Name"', 'USER', 'dbo', 'VIEW', 'vwReport1', 'COLUMN', 'cust_fnm'

    GO

    CREATE

    PROC REPORT1

       @USERLABEL CHAR(1) = 'N'

    AS

    BEGIN

    SET NOCOUNT ON

    declare @cmd nvarchar(4000), @property nvarchar(100)

    set @property = CASE @USERLABEL WHEN 'N' THEN 'INTERNAL_ELEMENT_LABEL' ELSE 'CLIENT_ELEMENT_LABEL' END

    select @cmd = ISNULL(@cmd + ',','') + rtrim(column_name) + ' AS ' + ISNULL(cast(value as varchar), rtrim(column_name))

    from information_schema.columns a

    left join ::fn_listextendedproperty(@property, 'user', 'dbo', 'view', 'vwReport1', 'COLUMN', NULL) b on a.column_name = b.objname

    where a.table_name = 'vwReport1'

    order by ordinal_position

    set @cmd = 'SELECT ' + @cmd + ' FROM vwReport1'

    exec (@cmd)

    SET NOCOUNT OFF

    END

    go

    exec

    report1 'n'

    exec report1 'y'

  • I forgot to mention that this uses extended properties tied to each view column, so different views can apply different labels to the same column name.

    Also, the report procedure could accept the view name as a second parameter so that only one procedure is needed to run any number of reports.  Only the views and extended properties must be defined to create a new report.

  • It is good if you have simple select. But with extended properties:

     

    1. Change is more complicated

    2. Flexebility is limited

    If more complicated stored procedure is created with temp tables, temp variables, calculated values then extended properties are useless. But data dictionary table anyway available.

  • Sorry, previous post is mine as well

     

    It is good if you have simple select. But with extended properties:

     

    1. Change is more complicated

    2. Flexebility is limited

    If more complicated stored procedure is created with temp tables, temp variables, calculated values then extended properties are useless. But data dictionary table anyway available.

  • I attached the label properties to views to allow the flexibility to include computed columns and other complex queries.  If you wanted to get more creative you could use this scheme to relable the columns of a table-returning function.

    If you want to use the data dictionary approach to relable the fields of the results returned by a stored procedure, the data dictionary logic has to be built into every stored procedure.  If you use some imagination you could figure out how to do the same with extended properties.  There is probably some level of complexity where this approach becomes too difficult to implement, but I don't think delivering static reports to Access (see original article) will reach that level.

    The main advantage is that the labels are attached to a specific field of a specific view.  There is no confusion about which label belongs to which identically-named column.  If columns or entire views are dropped, their properties go away.  If columns are renamed, their properties are retained.  Keeping the data dictionary in sync with schema changes, and knowing which data dictionary entries are obsolete, sounds like a maintenance nightmare.

    In some cases the data dictionary approach may be easier.  If the 200 databases mentioned in the article have similar structures, with many fields using the same labels in each, one dictionary would probably be easier to manage.  On the other hand, if the 5-6 new databases created each month use an existing database as a template, it is fairly easy to have extended properties scripted and copied to the new database.

    You say more complicated and less flexible, I say more organized, robust, and self-documenting.  The flexibility is limited only by your imagination, and as for complicated, anyone maintaining over 200 databases should be able to creatively query the sysproperties table to generate all the sp_addextendedproperty commands they need.  I think the choice would depend on the amount of maintenance each approach required.

  • You may be right, but unfortunately, we using extended properties for the applications. So, we already have 20-25 properties on each table (4-5 per column). To create some more (especially for the existing databases) will require revalidation (FDA requirements). Single data dictionary table in stand along database gives me a lot of flexibilities without messing up with existing environment. Anyway thanks for the advice

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply