StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>A dynamic sql option would be (written out as a query rather than made into a proc):</p> <pre><code>declare @machtype varchar(40) --stored proc parameter? set @machtype = 'Switch' --or 'Server' declare @sql nvarchar(4000) set @sql = 'select MachID, TestDate, MachType, Value1 as ''' + (select [Description] from dbo.xref where machtype = @machtype and [Column] = 'Value1') + ''', Value2 as ''' + (select [Description] from dbo.xref where machtype = @machtype and [Column] = 'Value2') + ''', Value3 as ''' + (select [Description] from dbo.xref where machtype = @machtype and [Column] = 'Value3') + ''' from dbo.datadump where machtype = ''' + @machtype + '''' exec sp_executesql @sql </code></pre> <p>If you find that simply too ugly for you then wrapping the logic for getting the column name in a function would tidy it up:</p> <pre><code>create function dbo.ColNameForDataDump( @machtype varchar(40), @column varchar(40) ) RETURNS varchar(40) as begin declare @col_desc varchar(40) select @col_desc = [description] from dbo.xref where machtype = @machtype and [column] = @column return @col_desc end </code></pre> <p>Then your dynamic SQL will look more like:</p> <pre><code>declare @machtype varchar(40) --stored proc parameter? set @machtype = 'Switch' --or 'Server' declare @sql nvarchar(4000) set @sql = 'select MachID, TestDate, MachType, Value1 as ''' + dbo.ColNameForDataDump(@machtype, 'Value1') + ''', Value2 as ''' + dbo.ColNameForDataDump(@machtype, 'Value2') + ''', Value3 as ''' + dbo.ColNameForDataDump(@machtype, 'Value3') + ''' from dbo.datadump where machtype = ''' + @machtype + '''' exec sp_executesql @sql </code></pre> <p>Finally a passing point / comment on the code above: you mentioned that you are on SQL Server 2000 so make sure when you do have to write some dynamic sql to define it as an nvarchar and use sp_executesql to call it...thereby negating some of the performance pain of having to go dynamic.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload