Generating Views for MS SQL Server Constants

Nearly 3 years ago I described how to store application constants in SQL Server views so that T-SQL code can be checked against these constants be referring view columns.

Now a user on SO asks how to generate this view definition on the fly.

Well, I am not sure whether a view defined as SELECT PIVOT (sample) can be used to verify depending T-SQL code.

What you can do, however, is to re-create the view once the content of the base table changes.

For example, take a table defining the visibility status of a blog post:

OID ProgID
1 Published
2 Draft
3 Deleted

We can now define a CTE statement that generates the CREATE VIEW statement for this table like this:

WITH vv AS (
SELECT ROW_NUMBER() OVER (ORDER BY OID) AS r, OID, ProgID
  FROM [Visibility]
), 
v AS (
SELECT 1 AS order1, NULL AS order2, 'CREATE VIEW V_Visibility AS' AS code
UNION ALL
SELECT 2, r, 
    CASE WHEN r = 1 THEN N'SELECT ' ELSE ', ' END 
    + CONVERT(NVARCHAR, OID) + N' AS ' + ProgID FROM vv
UNION ALL
SELECT 3, NULL, 'GO'
)
SELECT code FROM v
ORDER BY order1, order2

which returns the following result set:

CREATE VIEW V_Visibility AS
SELECT 1 AS Published
, 2 AS Draft
, 3 AS Deleted
GO

Using aggregate string concatenation, we can modify the final SELECT statement to read like this:

DECLARE @SQL NVARCHAR(MAX) = ''

; WITH vv AS ( 
... [as above]
)
SELECT @sql = @sql + CHAR(13) + code FROM v
ORDER BY order1, order2

PRINT @sql

resulting in

CREATE VIEW V_Visibility AS
SELECT 1 AS Published
, 2 AS Draft
, 3 AS Deleted
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: