rename column

Client liked the output, but (i guess he was thinking about exporting into Excel), the results column should be named “results_for_test” – so the name of the test was not only in the GUI but in the output.

Took a little longer than i expected.  I tried to create the table with the right column name, but had problems with trying to get the alias name right.  In hindsight, maybe making the column name different and not the alias might have worked.

Then i tried to write dynamic SQL to get the name of the lab test (@LAB) into the sp_name arguments list, but that was not going well.  Then i recalled that SQL is made to run with local variables, so i just created a second local variable, using the @LABS name and other words.  That finally worked.  I ended up looking at the column headings, and decided that i wanted to ‘quote’ the test, so instead of [results for Total PSA], the output was [result for ‘Total PSA’].  Might as well fuss with it while the iron is hot, rather than later when i have to try to remember all i was doing.

Create the table

IF OBJECT_ID('tempdb..##intermediateResults' , 'U') IS NOT NULL drop TABLE ##intermediateResults
set @cmd = 'CREATE TABLE [dbo].[##intermediateResults]
(
 [name] [varchar](20) NULL 
...
 ,[count_ovs] [int] NULL '
 if (@Lab is not null) 
 begin
 set @cmd = @cmd + ' 
 [result] [varchar](100) 
 , [lab_info] [varchar](100) ) '
 end
set @cmd = @cmd + ' 
) ON [PRIMARY] '

OK, at this point we have a table to select into, but the column name is wrong.  Since i know the name of the lab test, i can now create a local variable and rename the column.

declare @name varchar(40) = 'result for ''' + @lab + ''''
Execute tempdb..sp_rename 
'[tempdb].[dbo].[##intermediateResults].[result]', 
@name,'column';

And if you select ##intermediateResults now, you find that the results column is now named something  like ‘result for ‘PSA Total”, which is what we want.

Advertisements