loop through few table in sp

Hi,
I have a lot monthly tables, in order to query some data for last few month,
I have to check a few tables. And the table names are determined by today's
date. If I find the record, I don't need to query other tables anymore. how
can I do it in store procedure? ThanksIf the table names are based on the current date, you'll need to use dynamic
SQL for your query.
Like this:
declare @.today datetime
set @.today = getdate()
declare @.qry nvarchar(1000)
set @.qry = 'select * from sometable_' +
replace(convert(nvarchar(10),@.today,120)
,'-','') + ' where col = 1'
sp_executesql @.qry
But of course, if you want to look through a bunch of tables and stop
searching, you might want to consider populating a temporary table with the
tables you want to check (search through sysobjects for them perhaps), and
then grab the top record, delete it out of your temporary table, search
through the table, and if you find the data, drop out of your while loop. If
you run out of tables to check, you're done and you haven't found it.
But if it's a fixed list of tables, you could just write it out in full,
with return statements appropriately placed.
Hope this helps,
Rob
"Jen" wrote:

> Hi,
> I have a lot monthly tables, in order to query some data for last few mont
h,
> I have to check a few tables. And the table names are determined by today'
s
> date. If I find the record, I don't need to query other tables anymore. ho
w
> can I do it in store procedure? Thanks|||Jen
Can you create a view with an UNION ALL clause to combine those tables and
then query the view BETWEEN required dates?
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:26AB2E6F-CED2-4559-B48E-1F8FDF6123EB@.microsoft.com...
> Hi,
> I have a lot monthly tables, in order to query some data for last few
> month,
> I have to check a few tables. And the table names are determined by
> today's
> date. If I find the record, I don't need to query other tables anymore.
> how
> can I do it in store procedure? Thanks|||thanks. I need to query myTable20060523 first, if record found then I am
done; otherwise I need to continue query myTable200604, myTable200603...,
etc. up to 6 tables. how can I loop through these tables? Thanks
"Rob Farley" wrote:
> If the table names are based on the current date, you'll need to use dynam
ic
> SQL for your query.
> Like this:
> declare @.today datetime
> set @.today = getdate()
> declare @.qry nvarchar(1000)
> set @.qry = 'select * from sometable_' +
> replace(convert(nvarchar(10),@.today,120)
,'-','') + ' where col = 1'
> sp_executesql @.qry
>
> But of course, if you want to look through a bunch of tables and stop
> searching, you might want to consider populating a temporary table with th
e
> tables you want to check (search through sysobjects for them perhaps), and
> then grab the top record, delete it out of your temporary table, search
> through the table, and if you find the data, drop out of your while loop.
If
> you run out of tables to check, you're done and you haven't found it.
> But if it's a fixed list of tables, you could just write it out in full,
> with return statements appropriately placed.
> Hope this helps,
> Rob
>
> "Jen" wrote:
>|||This code will update only tables name like cust and update value of id
to 100.
create table cust (id int)
GO
create table cust1 (id int)
GO
insert into cust values(10)
go
insert into cust1 values(10)
go
select * from cust
go
select * from cust1
declare @.ret int
declare @.sql nvarchar(4000)
declare @.db sysname
set @.db = DB_NAME()
Declare @.tabname sysname
set @.tabname= '%cust%'
set @.sql ='select ''update '' + QUOTENAME(table_SCHEMA) + ''.'' +
QUOTENAME(table_NAME) + '' set id = 100 '' FROM
INFORMATION_SCHEMA.tables ' +
'WHERE table_type = ''base table'''
if @.tabname is not null
set @.sql = @.sql + N' AND table_NAME LIKE ''' + @.tabname+ ''''
exec @.ret = master.dbo.xp_execresultset @.sql,@.db
print @.ret
select * from cust
select * from cust1
You can specify name pattern for your table like '%cust%' here in
sample and it will update or do other operation only on that tables.
Regards
Amish Shah|||This code will update only tables name like cust and update value of id
to 100.
create table cust (id int)
GO
create table cust1 (id int)
GO
insert into cust values(10)
go
insert into cust1 values(10)
go
select * from cust
go
select * from cust1
declare @.ret int
declare @.sql nvarchar(4000)
declare @.db sysname
set @.db = DB_NAME()
Declare @.tabname sysname
set @.tabname= '%cust%'
set @.sql ='select ''update '' + QUOTENAME(table_SCHEMA) + ''.'' +
QUOTENAME(table_NAME) + '' set id = 100 '' FROM
INFORMATION_SCHEMA.tables ' +
'WHERE table_type = ''base table'''
if @.tabname is not null
set @.sql = @.sql + N' AND table_NAME LIKE ''' + @.tabname+ ''''
exec @.ret = master.dbo.xp_execresultset @.sql,@.db
print @.ret
select * from cust
select * from cust1
You can specify name pattern for your table like '%cust%' here in
sample and it will update or do other operation only on that tables.
Regards
Amish Shah|||can I create view in the store procedure? Is there any side effect or
performance issue? How about more than one user is executing the same
procedure?Thanks
"Uri Dimant" wrote:

> Jen
> Can you create a view with an UNION ALL clause to combine those tables and
> then query the view BETWEEN required dates?
>
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:26AB2E6F-CED2-4559-B48E-1F8FDF6123EB@.microsoft.com...
>
>|||--Try something like this:
declare @.qry nvarchar(2000)
declare @.found bit
set @.found = 0
declare @.done bit
set @.done = 0
declare @.tablenames table (id int identity(1,1), name varchar(128));
insert into @.tablenames
select top 6 name
from sysobjects
where name like 'mytable%'
order by 1 desc
declare @.tablename varchar(128)
declare @.tableid int
while (@.found = 0 and @.done = 0)
begin
select top 1 @.tablename = name, @.tableid = id
from @.tablenames
order by id
if (@.@.rowcount = 0)
begin
set @.done = 1
end
else --search through the table
begin
delete from @.tablenames where id = @.tableid
set @.qry = 'declare @.misc int; select @.misc = id from ' + @.tablename + '
where somecol = 15' --This won't return a value, but will set @.@.rowcount
exec sp_executesql @.qry
if (@.@.rowcount > 0)
begin
set @.found = 1
end
end
end
-- Look at the values of @.found and @.tablename to see if you found it, and
what table you found it in
if (@.found = 1)
begin
select @.tablename
end
"Jen" wrote:
> thanks. I need to query myTable20060523 first, if record found then I am
> done; otherwise I need to continue query myTable200604, myTable200603...,
> etc. up to 6 tables. how can I loop through these tables? Thanks
> "Rob Farley" wrote:
>sql