SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create procedure testSP @resultsetcolumn sysname
--with recompile  -- Option #1
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 TempDB.sys.columns WHERE object_id = OBJECT_ID('tempdb..#headerX') ORDER BY column_id

select * from #headerX
  union all
select * from @data
--option(recompile) -- Option #3

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

--SELECT * FROM TempDB.sys.columns WHERE object_id = OBJECT_ID('tempdb..#headerY') ORDER BY column_id

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
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
7 8 9
c
d


1
2
3
4


execute testSP @resultsetcolumn='[X Y Z]'


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
X Y Z
c
d


1
2
3
4


execute testSP @resultsetcolumn='[R S T]' WITH RECOMPILE -- Option #2


a
R S T
c
d


1
2
3
4


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




a
R S T
c
d


1
2
3
4


select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
 Apr 11 2022 16:24:07 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)