February 1, 2005 at 11:40 am
Hey,
I'm trying to create a cursor containing table names. Using these table names I want to return a result set from information for all these tables. The cursor works fine. Can anyone suggest a way do the select...from @TableName? OR suggest an easier way of getting the whole thing done?
Below is a snippet of the stored procedure code...
declare MonthlyLogs cursor for
select TableName
from
RadLog_Table
open MonthlyLogs
fetch next from MonthlyLogs into @LogTable
while @@Fetch_Status = 0
begin
select
@TimeStamp = Time_Stamp,
@Message = Message,
@FailedPass = FailedPass
from @LogTable
-- where
-- Priority = 1
-- and
-- User_Name like @LoginName
-- order by Time_Stamp desc
print @LogTable
fetch next from MonthlyLogs into @LogTable
end
close MonthlyLogs
deallocate MonthlyLogs
return
ROSS
February 1, 2005 at 11:47 am
So these tables are identical in structure and are essentially a horizontal partitioning scheme, with 1 table per month ?
Why not just build a UNION view across all the tables ?
Create View vwAllLogs
As
Select * from MogMonth1
Union
Select * From LogMonth2
Union
Select * From LogMonth3
etc etc
February 1, 2005 at 12:17 pm
This is not really related to your problem, but I have always wondered why, when using a cursor (ugh!), you would want to use two 'fetch' statements? Why not set up the 'fetch' loop the following way?:
declare MonthlyLogs cursor for
select
TableName
from
RadLog_Table
open MonthlyLogs
while 1 = 1
begin
fetch next from MonthlyLogs into @LogTable
if @@fetch_status <> 0
break
else
begin
select
@TimeStamp = Time_Stamp,
@Message = Message,
@FailedPass = FailedPass
from
@LogTable
where
Priority = 1
and
User_Name like @LoginName
order by Time_Stamp desc
print @LogTable
end
end
close MonthlyLogs
deallocate MonthlyLogs
February 1, 2005 at 10:18 pm
Thanks, I'll try that ... this may come out be less overhead. I'll see...
February 2, 2005 at 1:22 am
You can not use select .. from @var. But you can use execute @procname. I would suggest to use a temporary procedure to do the select. To have exec() and the procedure you use share the temporary procedure created, it has to be a ## procedure ( as for temporary tables ). To make sure the temporary procedure is unique, I use the name of the calling procedure + the number of the connection in use. To use a cursor, I also suggest the syntax used in the script to avoid 2 fetch statements ...
Please try following code ( it worked for me ):
-- test script : run in tempdb ...
use tempdb
go
-- set up the environment
create table RadLog_Table ( TableName sysname )
insert RadLog_Table (TableName) values ( 'tst1' )
insert RadLog_Table (TableName) values ( 'tst2' )
create table tst1 ( Time_Stamp datetime, Message varchar(50), FailedPass varchar(50))
insert tst1 (Time_Stamp , Message , FailedPass) values ( getdate(),'Test Message','Failed' )
create table tst2 ( Time_Stamp datetime, Message varchar(50), FailedPass varchar(50))
insert tst2 (Time_Stamp , Message , FailedPass) values ( getdate(),'Test Message from 2','OK from 2' )
go
-- create the procedure
create proc usp_aproc
--()
as
begin
declare @sql varchar(8000) -- holds the SQL to create a temp procedure
declare @tmpproc sysname -- holds a unique name for the temp procedure
declare @LogTable sysname
declare @TimeStamp datetime,
@Message varchar(50),
@FailedPass varchar(50)
set @tmpproc = '##' + object_name(@@procid) + '_' + convert(varchar(12),@@spid)
declare MonthlyLogs cursor
local read_only
for select TableName
from
RadLog_Table
open MonthlyLogs
goto next_MonthlyLogs
while @@Fetch_Status = 0
begin
if object_id(@tmpproc) is not null exec ('drop proc ' + @tmpproc )
set @sql = 'create proc ' + @tmpproc + '
(
@TimeStamp datetime out,
@Message varchar(50) out,
@FailedPass varchar(50) out
)
as
begin
select TOP 1
@TimeStamp = Time_Stamp,
@Message = Message,
@FailedPass = FailedPass
from ' + @LogTable + '
end '
exec ( @sql )
exec @tmpproc @TimeStamp out,@Message out, @FailedPass out
select LogTable = @LogTable, TimeStamp = @TimeStamp, Message = @Message, FailedPass = @FailedPass
next_MonthlyLogs:
fetch next from MonthlyLogs into @LogTable
end
close MonthlyLogs
deallocate MonthlyLogs
if object_id(@tmpproc) is not null exec ('drop proc ' + @tmpproc )
return
end
go
exec usp_aproc
February 2, 2005 at 12:39 pm
Bert is correct. The table name can not be a variable in SQL Server. Building the entire query as a variable and then running with EXEC(@variable) should give you the results you want.
Marvin
Marvin Dillard
Senior Consultant
Claraview Inc
February 2, 2005 at 1:08 pm
You guys are brilliant! I hope this works...It'll save alot of time for me as a new table is auto created each month to store logs and this way I will not have to keep manually adjusting the code every month.
February 2, 2005 at 1:18 pm
How many logs do you have to keep in there? Unless you have a few gigs of data I don't see the point of building a new table every month. You could simply create your clustered primary key like this : Year(datelog) + month(datelog) as Period, currentkey
That way you could do range searches and you wouldn't have to rebuild your views every month... and it could also prove to be a big speed improvement.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply