SQL Server -- Elegant way to use custom fields for record set.

Question

I'm looking for an elegant way to select fields from a view, where a second supporting table defines the fields that should be selected.

The view contains all of my employee data. The constraint table contains the field name, which references the view field name, and a bit which signifies to select that field.

Example of View:

Name   | Age | Weight
Peter  | 38  | 180
Martha | 25  | 115

Example of constraint table:

Field  | Enabled
Name   | 1
Age    | 0
Weight | 1

...Where after running the query, the following data should return:

Name   | Weight
Peter  | 180
Martha | 115

Any ideas on how to approach this?

Thanks!

Answer 1

Without dynamic SQL you can't: SQL is a "fixed column contract" language

I'd suggest using the constraint data in the client to hide data and don't do this in the database

Answer 2

DECLARE @TABLE TABLE
( field varchar(70),
[enabled] int)

DECLARE @SELECT VARCHAR(MAX)
SET @SELECT = ''
INSERT INTO @TABLE VALUES ('Name',1)
INSERT INTO @TABLE VALUES ('Age',0)
INSERT INTO @TABLE VALUES ('Weight',1)

SELECT  @SELECT = @SELECT + field + ', '
FROM @TABLE
WHERE [enabled] = 1

SET   @SELECT = LEFT(@SELECT,LEN(@SELECT)-1)     --trim last ,

SET @SELECT = 'SELECT ' + @SELECT + ' FROM MyView'

PRINT @SELECT
--EXEC @SELECT

will execute

SELECT Name, Weight FROM MyView

Bring on the dynamic sql haters.

Using this like a table or view is not trivial but it is possible, first wrap it in a stored procedure, then create a linked server 'loopback'

if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
    @srvproduct = N'',
    @provider = N'SQLOLEDB', 
    @datasrc = @@servername
go

Then you can use the following syntax:

select * FROM openquery(loopback, 'exec yourSproc')