dynamically add column in sql temp table

 CREATE TABLE student 

(

student_id int

,student_name varchar(100)

,class int

)

insert into student(student_id,student_name,class) values(10001,'Pabitra Behera',10);

insert into student(student_id,student_name,class) values(10002,'Swasti Prakash Brahma',10);

insert into student(student_id,student_name,class) values(10003,'Ankit Kalia',10);

CREATE TABLE tsubject 

(

subject_id int

,subjects varchar(100)

 

)

insert into tsubject values(1, 'Odia');

insert into tsubject values(2, 'English');

insert into tsubject values(3, 'Hindi');

create proc USP_GET_DATA

(

@action varchar(10)=null

)

AS BEGIN

SELECT * INTO #tstudent FROM student with(nolock)

 

 declare @COLUMN_NAME varchar(256)

declare _cursor cursor read_only for

select subjects from tsubject

open _cursor

fetch next from _cursor into @COLUMN_NAME

while @@FETCH_STATUS=0

begin

declare @DynamicSQL nvarchar(max);

SET @DynamicSQL = 'ALTER TABLE #tstudent ADD ['+ CAST(@COLUMN_NAME AS NVARCHAR(100)) +'] NVARCHAR(100) NULL'

EXEC(@DynamicSQL) 

 

fetch next from _cursor into  @COLUMN_NAME

end

close _cursor

deallocate _cursor

select * from #tstudent

 

END