October 1. 2007 23:28

This is worth extending:

I've come up with a more generic way to do this, which will cater for generating all defined tables. I don't know how to format what I;ve got below (Pete, can you help ?) but it looks like this.

-- Set up data
IF EXISTS (SELECT * FROM sysobjects where name = 'CoffeeType' and xtype in ('U'))
DROP TABLE CoffeeType
GO

CREATE TABLE CoffeeType
(CoffeeTypeId INTEGER NOT NULL PRIMARY KEY,
CoffeeDescriptors VARCHAR(1024),
CoffeeTypeDescription VARCHAR(80) NOT NULL)
GO

INSERT INTO CoffeeType VALUES (1, 'Smooth & creamy', 'Arabica')
INSERT INTO CoffeeType VALUES (2, 'Your spoon will stand up in it', 'Italiano')
GO

IF EXISTS (SELECT * FROM sysobjects where name = 'MilkType' and xtype in ('U'))
DROP TABLE MilkType
GO
CREATE TABLE MilkType
(MilkTypeId INTEGER NOT NULL PRIMARY KEY,
MilkTypeDescription VARCHAR(80) NOT NULL,
FatPercentage DECIMAL(10, 2),
ContainsLactose BIT)
GO

INSERT MilkType VALUES (1000, 'Full cream', 4.2, 1)
INSERT MilkType VALUES (1001, 'Low fat', 1.0, 1)
INSERT MilkType VALUES (1002, 'Skinny', 0.1, 1)
INSERT MilkType VALUES (1003, 'Soy', 1.2, 0)
GO

IF EXISTS (SELECT * FROM sysobjects where name = 'SugarType' and xtype in ('U'))
DROP TABLE SugarType
GO
CREATE TABLE SugarType
(SugarCode VARCHAR(10) NOT NULL PRIMARY KEY,
SugarTypeDescription VARCHAR(80))
GO

INSERT SugarType VALUES ('WHITE', 'White')
INSERT SugarType VALUES ('RAW', 'Raw')
INSERT SugarType VALUES ('BROWN', 'Brown')
INSERT SugarType VALUES ('FAKE', 'Equal')
GO

IF EXISTS (SELECT * FROM sysobjects where name = 'ReferenceTable' and xtype in ('U'))
DROP TABLE ReferenceTable
GO
CREATE TABLE ReferenceTable
(TableName VARCHAR(80) NOT NULL PRIMARY KEY,
IDColumn VARCHAR(80),
DescriptionColumn VARCHAR(80))
GO

INSERT ReferenceTable VALUES ('CoffeeType', 'CoffeeTypeId', 'CoffeeTypeDescription')
INSERT ReferenceTable (TableName) VALUES ('MilkType')
INSERT ReferenceTable (TableName) VALUES ('SugarType')
GO

-- Real script starts here
SET NOCOUNT ON
DECLARE cur CURSOR
FOR SELECT TableName,
IDColumn,
DescriptionColumn
FROM ReferenceTable
DECLARE @TableName VARCHAR(80),
@IDColumn VARCHAR(80),
@IDColumnType VARCHAR(80),
@DescriptionColumn VARCHAR(80),
@DescriptionColumnLength INTEGER
OPEN cur
FETCH cur INTO @TableName,
@IDColumn,
@DescriptionColumn
WHILE @@FETCH_STATUS = 0
BEGIN
-- Default columns if not sepcified, you don't really have to do this, I'm showing off a bit ...
IF ISNULL(@IDColumn, '') = '' OR
ISNULL(@DescriptionColumn, '') = ''
BEGIN
DECLARE curDefaultColumns CURSOR
FOR SELECT TOP 2
C.name
FROM syscolumns C
JOIN
sysobjects T
ON C.id = T.id
WHERE T.name = @TableName
AND T.xType IN ('U', 'V')
ORDER BY colid
OPEN curDefaultColumns
FETCH curDefaultColumns
INTO @IDColumn
WHILE @@FETCH_STATUS = 0
BEGIN

FETCH curDefaultColumns
INTO @DescriptionColumn
END
CLOSE curDefaultColumns
DEALLOCATE curDefaultColumns
END

IF ISNULL(@IDColumn, '') = '' OR
ISNULL(@DescriptionColumn, '') = ''
BEGIN
DECLARE @ErrText VARCHAR(255)
SET @ErrText = 'Could not establish id and description columns for table ' + @TableName
RAISERROR(@ErrText, 16, 1) WITH SETERROR
END

-- Establish variable type of id column & length of description columns (which must be a string since we are going to use that for our enum name)
SELECT @DescriptionColumnLength = C.length
FROM syscolumns C
JOIN
sysobjects T on C.id = T.id
WHERE C.name = @DescriptionColumn
AND T.name = @TableName
AND T.xtype in ('U', 'V')

SELECT @IDColumnType = ST.name + CASE WHEN ST.name like ('%char%') or
ST.name like ('%var%') THEN ' (' + CONVERT(VARCHAR, C.length) + ')'
WHEN ST.name = 'decimal' THEN ' (' + CONVERT(VARCHAR, C.xprec) + ', '+ CONVERT(VARCHAR, C.xscale) + ')'
ELSE '' END
FROM systypes ST
JOIN
syscolumns C on C.xtype = ST.xtype
JOIN
sysobjects T on C.id = T.id
WHERE C.name = @IDColumn
AND T.name = @TableName
AND T.xtype in ('U', 'V')

-- Nested cursor unfortunately has to be dynamically created in the one batch ... C'est la vie
DECLARE @SQL NVARCHAR(4000)

SET @SQL = 'SET NOCOUNT ON
PRINT ''enum ' + @TableName + '''
PRINT ''{''
DECLARE @IDValue ' + @IDColumnType + ',
@DescriptionValue VARCHAR(' + CONVERT(VARCHAR, @DescriptionColumnLength) + ')
DECLARE nestedCursor CURSOR
FOR SELECT ' + @IDColumn + ', ' +
@DescriptionColumn + '
FROM ' + @TableName + '
OPEN nestedCursor
FETCH nestedCursor INTO @IDValue, @DescriptionValue
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '' '' + CONVERT(VARCHAR, @DescriptionValue) + '' = '' + CONVERT(VARCHAR, @IDValue)
FETCH nestedCursor INTO @IDValue, @DescriptionValue
END
CLOSE nestedCursor
DEALLOCATE nestedCursor
PRINT ''};''
PRINT '''''
EXEC sp_executeSQL @SQL

FETCH cur INTO @TableName,
@IDColumn,
@DescriptionColumn
END
CLOSE cur
DEALLOCATE cur

Andrew Connolly

October 1. 2007 23:32

Now the final extension to this is what happens if you have your reference values in a generic code table ? Two ways:

(i) You could add a 'condition' column to your reference table ... pretty easy really, just need to append this condition to the SELECT clause in the nested cursor (after having retrieved it in the outer cursor's fetch.

OR

(ii) Create each ref table you want to enumerate as a view and put the views in the ReferenceTable table

Easy.

Andrew Connolly

October 1. 2007 23:33

Lastly, I know there is an error in the generated syntax for enumerated strings (above). I just couldn't be bothered (have spent a little too much work time on this already) to escape it with double quotes if the variable type (which we have) is a string type.

That one's left for you as an exercise ...

Now I think I might try and implement that here.

Andrew Connolly