Last month I had an interesting task to deal with. It had to do with the way our business views the data. In many cases my company is using MS Access as the way to access the data from tables and then save user filtered, ordered, or grouped reports in various formats. I am not saying it is good or bad. It is as it is. In many cases we are using stored procedures to get data out to Access. Stored procedures are linked in ICT Access files. Sometime views are created and linked to Access ICT file as well.

The request originated by the external users. In many cases clients would like to see a report output with their own header labels instead of our column names. Our internal business clients have the same request but with our internal labels. They would like to see a label instead of a column name. But the label should be a company specific label. We have about 200 databases and 5-6 new databases created per month. Each database should satisfy with such request. Remember that we are talking about direct stored procedure outputs and not about full reports developed by a specific reporting tool such as Crystal Reports.

Let illustrate a general idea of how it can be done. Let’s create a user table, a column dictionary table and populate the data dictionary with some data.

Create table customer (
Cust_cd char(4) not null primary key,
   Cust_lnm varchar(50),
   Cust_fnm varchar(50) )
INSERT INTO customer ( Cust_cd ,Cust_lnm ,Cust_fnm) 
   VALUES ('AAAA','LNM_TEST1','FNM_TEST1')
INSERT INTO customer ( Cust_cd,Cust_lnm ,Cust_fnm)
   VALUES ('BBBB','LNM_TEST2','FNM_TEST2')
CREATE TABLE COL_DICTIONARY (
   COL_DICTIONARY_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
   COLUMN_NM varchar (50) NOT NULL , 
INTERNAL_ELEMENT_LABEL VARCHAR (50) NOT NULL ,
   CLIENT_ELEMENT_LABEL varchar (50) NULL )
Insert into COL_DICTIONARY ( COLUMN_NM, INTERNAL_ELEMENT_LABEL, CLIENT_ELEMENT_LABEL )
  Values ( 'cust_cd', '"Customer Code"', 'Code')
Insert into COL_DICTIONARY ( COLUMN_NM, INTERNAL_ELEMENT_LABEL, CLIENT_ELEMENT_LABEL )
  Values ( 'cust_lnm', '"Customer Last Name"', '"Last Name"')
Insert into COL_DICTIONARY ( COLUMN_NM, INTERNAL_ELEMENT_LABEL, CLIENT_ELEMENT_LABEL )
  Values ( 'cust_fnm', '"Customer First Name"', '"First Name"')

This table allows you save every data element for all tables as well as client and company internal labels for the columns. Next step is to prepare the procedure that will allow label manipulation.

CREATE PROC REPORT1
@USERLABEL CHAR(1) = 'N'
AS
BEGIN
SET NOCOUNT ON
declare @cmd nvarchar(4000)
create table #tmp 
( Cust_cd char(4) not null primary key
  , Cust_lnm varchar(50)
  , Cust_fnm varchar(50)
)
INSERT INTO #TMP (cust_cd, cust_lnm, cust_fnm)
 SELECT cust_cd, cust_lnm, cust_fnm
  FROM customer
select @cmd = 'select cust_cd as cust_cd, cust_lnm as cust_lnm, cust_fnm as cust_fnm FROM #tmp' 
declare @minid int, @maxid int
select @minid = min(COL_DICTIONARY_ID) , @maxid = max(COL_DICTIONARY_ID)
  from COL_DICTIONARY
WHILE (@MINID <= @MAXID)
 BEGIN
  IF (@USERLABEL = 'N')
   BEGIN
     select @CMD = replace (@cmd, 'AS ' + COLUMN_NM , 'AS ' + INTERNAL_ELEMENT_LABEL)
      from COL_DICTIONARY
      WHERE COL_DICTIONARY_ID = @MINID
   END
  ELSE
   BEGIN
    select @CMD = replace (@cmd, 'AS ' + COLUMN_NM , 'AS ' + CLIENT_ELEMENT_LABEL)
     from COL_DICTIONARY
     WHERE COL_DICTIONARY_ID = @MINID
   END
   SELECT @MINID = @MINID + 1
 END
exec (@cmd)
SET NOCOUNT OFF
END

Flag @USERLABEL allows a business person to specify the label output: internal or customer specific.

The next two outputs illustrate the point.

EXEC REPORT1@USERLABEL = 'N'
Customer Code Customer Last Name                                 Customer First Name
------------- -------------------------------------------------- ------------------------- 
AAAA          LNM_TEST1                                          FNM_TEST1
BBBB          LNM_TEST2                                          FNM_TEST2
EXEC REPORT1@USERLABEL = 'Y'
Code Last Name                    First Name
---- ---------------------------- ----------------- 
AAAA LNM_TEST1                    FNM_TEST1
BBBB LNM_TEST2                    FNM_TEST2

Obviously, there are some restrictions:– columns with the same name will end up having the same label.

It can be avoided if the label for each column will be unique in the SQL statement:

select cust_cd as cust_cd, cust_lnm as cust lnm, cust_fnm as cust_fnm
 FROM #tmp

Table COL_DICTIONARY may have an additional column COL_LABEL. And one row will be created for every column in the database to support uniqueness of the label even if the column names are the same.

CREATE TABLE COL_DICTIONARY (
  COL_DICTIONARY_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  COLUMN_NM varchar (50) NOT NULL ,
  INTERNAL_ELEMENT_LABEL VARCHAR (50) NOT NULL ,
  CLIENT_ELEMENT_LABEL varchar(50) NULL ,
  COL_LABELvarchar(50)
)
Insert into COL_DICTIONARY ( COLUMN_NM, INTERNAL_ELEMENT_LABEL, CLIENT_ELEMENT_LABEL, COL_LABEL )
 Values ( 'cust_cd', '"Customer Code"', '"Code"', 'cust_cd_1')
Insert into COL_DICTIONARY ( COLUMN_NM, INTERNAL_ELEMENT_LABEL, CLIENT_ELEMENT_LABEL, COL_LABEL )
 Values ('cust_lnm', '"Customer Last Name"', '"Last Name"', 'last_nm_1')
Insert into COL_DICTIONARY ( COLUMN_NM, INTERNAL_ELEMENT_LABEL, CLIENT_ELEMENT_LABEL, COL_LABEL )
 Values ('cust_fnm', '"Customer First Name"', '"First Name"', 'first_nm_1')

The procedure will now look like:

CREATE PROC REPORT1
  @USERLABEL CHAR(1) = 'N'
AS
BEGIN
SET NOCOUNT ON
declare @cmd nvarchar(4000)
create table #tmp 
( Cust_cd char(4) not null primary key, Cust_lnm varchar(50), Cust_fnm varchar(50))
INSERT INTO #TMP (cust_cd, cust_lnm, cust_fnm)
 SELECT cust_cd, cust_lnm, cust_fnm 
  FROM customer
select @cmd =  'select cust_cd as cust_cd_1, cust_lnm as last_nm_1, cust_fnm as first_nm_1
                 FROM #tmp' 
declare @minid int, @maxid int
select @minid = min(COL_DICTIONARY_ID) , @maxid = max(COL_DICTIONARY_ID)
 from COL_DICTIONARY
WHILE (@MINID <= @MAXID)
 BEGIN
  IF (@USERLABEL = 'N')
   BEGIN
    select @CMD = replace (@cmd, 'AS ' + COL_LABEL , 'AS ' + INTERNAL_ELEMENT_LABEL)
     from COL_DICTIONARY
     WHERE COL_DICTIONARY_ID = @MINID
   END
  ELSE
   BEGIN
    select @CMD = replace (@cmd, 'AS ' + COL_LABEL , 'AS ' + CLIENT_ELEMENT_LABEL)
     from COL_DICTIONARY
     WHERE COL_DICTIONARY_ID = @MINID
   END
  SELECT @MINID = @MINID + 1
 END
exec (@cmd)
SET NOCOUNT OFF
END
EXEC REPORT1@USERLABEL = 'N'
Customer Code Customer Last Name                                 Customer First Name
------------- -------------------------------------------------- ------------------------------ 
AAAA          LNM_TEST1                                          FNM_TEST1
BBBB          LNM_TEST2                                          FNM_TEST2
EXEC REPORT1@USERLABEL = 'Y'
Code Last Name                      First Name
---- ------------------------------ ------------------------- 
AAAA LNM_TEST1                      FNM_TEST1
BBBB LNM_TEST2                      FNM_TEST2

The parameter passed to the procedure can be made table driven as well.. However, it may prove to be a bit tricky in a multi-user environment.

Conclusion

This is a simple way to change output labels returned by a stored procedure. Despite it’s many restriction, it is quite fitting for simple kind of output.