SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create procedure testSP @resultsetcolumn sysname
--with recompile --not an option
as
  
set @resultsetcolumn=parsename(@resultsetcolumn,1)
declare @data table(col1 int default(1), col2 int default(2), col3 int default(3), col4 int default(4))
insert into @data default values
create table #headerX(a int, b int, c int, d int)
  
execute tempdb.sys.sp_rename '#headerX.b',@resultsetcolumn,'column'

select * from #headerX
  union all
select * from @data

create table #headerY(a int)
set @resultsetcolumn=quotename(@resultsetcolumn)
exec('alter table #headerY add '+@resultsetcolumn+' int,c int,d int')

select * from #headerY
  union all
select * from @data

execute testSP @resultsetcolumn='A B C D'


a
A B C D
c
d


1
2
3
4


Caution: Changing any part of an object name could break scripts and stored procedures.




a
A B C D
c
d


1
2
3
4


execute testSP @resultsetcolumn='[7 8 9]'


a
7 8 9
c
d


1
2
3
4


Caution: Changing any part of an object name could break scripts and stored procedures.




a
7 8 9
c
d


1
2
3
4


select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU30) (KB5013756) - 14.0.3451.2 (X64) 
 Jun 22 2022 18:20:15 
 Copyright (C) 2017 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)