Dynamic query in select and insert command

Posted by Guna27 under Sql Server on 12/20/2012 | Points: 10 | Views : 1094 | Status : [Member] | Replies : 1
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..............


Responses

Posted by: Sourabh07 on: 12/27/2012 [Member] Starter | Points: 25

Up
0
Down
hi..

will you please describe the fields of tables listed below:

1) IT2006FirstSemesterInternal1

2) IT2006FirstSemesterInternal2

3) IT2006FirstSemester

Sourabh07

Guna27, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response