Passing a variable to an SP for a cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Passing a variable to an SP for a cursor

Author

Topic

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2007-10-16 : 17:17:08

Does anyone know how to pass a variable (table name) to a stored procedure that will be used on a cursor?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-16 : 17:19:02

Yes, but why would you want to do that? Can you show us an example?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2007-10-16 : 17:35:37

Sure. This is just part of the SP, the purpose of this SP is go into the archive DB rename the table that was copied last month - Table_old to Table_MMM_YYYY -, delete any tables from the archive db older than 6 months and copy the current table from the production DB to the archive DB as Table_old.
We have about 8 tables that we have to do the same thing every month.
This is done this way so we can push the monthly updates and run comparisons with last month's data, let me know if the below is enough for you to get the idea.

ALTER procedure [dbo].[ArchiveMtgTables]

(@Table as varchar(50),
@Table2 as varchar(50))

as

BEGIN

declare t cursor local read_only forward_only for

select cast(replace(substring(name,charindex('_',[name],len(''@Table2''))+1,len([name])),'_',' ') as smalldatetime),
so.name,so.id
from sysobjects so
where left(name,len(@Table2))=@Table
and isdate(replace(substring(name,charindex('_',[name],len(@Table2))+1,len([name])),'_',' '))=1
order by cast(replace(substring(name,charindex('_',[name],len(@Table2))+1,len([name])),'_',' ') as smalldatetime) desc, crdate desc

-- the above sets up a queue to go through for tables Beginning with m_ and have a valid date format (mmm_yyyy)
-- after the tablename (i.e. 'm_apr_2006' will be picked up but 'm_apr2_2006', or 'm_apr_2006_phoneupdate' will not')
-- it orders it by date descending so we can eliminate tables past an archive count

declare @sq as varchar(4000)
declare @tabledate as smalldatetime,
@mostrecent_tabledate as smalldatetime,
@tabledate_forolddata as smalldatetime
declare @tablename as varchar(50),
@mostrecent_tablename as varchar(50),
@tablename_forolddata as varchar(50)
declare @counter as int,
@tableid as int
declare @tablelimit as int

set @tablelimit = 6 --change this to alter how many tables are held in archive
set @counter = 0

open t
fetch next from t into @tabledate, @tablename, @tableid
while @@fetch_status = 0

...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources