February 6, 2012 at 2:45 am
I have individual Monthly tables (eg: AMOLTST_01_2012), so Jan is 01 and so on...
I have a SP that takes the Name and a dateFrom and dateTo.
My question is how do I code a solution to dynamically create a temporary table that sums field1,field2..... from each of these tables. But at design time the code wont know what these tables will be.
Eg: The following parameters will be input: @authority, @dateFrom, @dateTo
So the tables could be : AMOLTST_01_2012, AMOLTST_02_2012, AMOLTST_03_2012
But i dont know how to say sum(field1) FROM .... and then reference these tables somehow.
Darryl Wilson
darrylw99@hotmail.com
February 6, 2012 at 5:21 am
This was removed by the editor as SPAM
February 6, 2012 at 5:28 am
This was removed by the editor as SPAM
February 6, 2012 at 5:58 am
begin
declare @param varchar(10),@dateFrom varchar(10)='09/13/2011'
declare @output nvarchar(255),@output1 nvarchar(255)
declare @tab_cnt int,@init int= 1,@query nvarchar(500)
declare @input_filename table (sno int identity (1,1),table_name varchar(20))
declare @output_tab table (sno int identity(1,1),table_name varchar(20),cnt int)
set @output = N'@outputval nvarchar(255) OUTPUT'
insert into @input_filename
select TABLE_NAME from information_schema.tables where TABLE_NAME like 'AMOLTST_%_2012'
select @tab_cnt = COUNT(*) from @input_filename
while (@init <=@tab_cnt)
begin
select @query = 'select @Outputval = count(*) from '+table_name +' AND CONVERT(VARCHAR (10),DATEfrom ,101)= '''+@dateFrom +''' ' from @input_filename where sno = @init
execute sp_executesql @query , @output, @output1 OUTPUT;
insert into @output_tab
select TABLE_NAME,@output1 FROM @input_filename WHERE sno = @init;
set @init = @init+1
end
select * from @output_tab
end
regards
siva kumar J
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply