This is my script which dynamically get the columns and get the corresponding column value to calculate avg value and then it'll insert into another table.
USE [collegeautomation]
GO
/****** Object: StoredProcedure [dbo].[Proc_Internal_Calc2] Script Date: 12/21/2012 10:00:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Proc_Internal_Calc2](@RegNo bigint)
as
begin
declare @InsertString varchar(100);
declare @count int;
declare @colum varchar(50);
declare @mark1 int;
declare @mark2 int;
declare @Subject table(cols varchar(50));
insert into @Subject(cols) (SELECT COLUMN_NAME 'All_Columns' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='IT2006FirstSemesterInternal1' and COLUMN_NAME like 'I_%');
select * from @Subject;
set @count=(select count(cols) from @Subject);
while @count>0
begin
set @colum=(select top 1 cols from @Subject);
set @mark1=(select @colum from IT2006FirstSemesterInternal1 where RegisterNo=@RegNo);
set @mark2=(select @colum from IT2006FirstSemesterInternal2 where RegisterNo=@RegNo);
set @mark1=(@mark1+@mark1)/2;
SET @InsertString= 'insert into IT2006FirstSemester(RegisterNo,' + @colum + ') values(@RegNo,@mark1)';
print @InsertString
EXEC sp_executesql @InsertString,N'@RegNo bigint,@mark1 int',@RegNo, @mark1
delete from @Subject where cols=@colum
set @count=(select count(cols) from @Subject);
end
end
exec [dbo].[Proc_Internal_Calc2] 92107134010
//Execution Message
(8 row(s) affected)
(8 row(s) affected)
Msg 245, Level 16, State 1, Procedure Proc_Internal_Calc2, Line 17
Conversion failed when converting the varchar value 'I_TechnicalEnglish' to data type int.
Guna..............