Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Customized Output Labels

By Leo Peysakhovich,

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.

Total article views: 5778 | Views in the last 30 days: 3
 
Related Articles
FORUM

Database Dictionary

Database Dictionary

FORUM

Maililng Labels

Print mailing labels

FORUM

Dictionary Table

Dictionary Table

FORUM

Customized Output Labels

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

FORUM

SQL Server data dictionary

data dictionary

Tags
stored procedures    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones