cursor err - SQL Server Forums

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)

cursor err

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-02-20 : 10:17:01

alter proc test ( @type varchar(255)
as
begin
delcare @var
declare cur1 cursor fast_forward for
select table_name from information_schema.tables
where table_name like 'rpt1%'
declare cur2 cursor fast_forward for
select table_name from information_schema.tables
where table_name like 'rpt2%'

Begin
if @type='a'
begin
open cur1 fetch next from cur1 into @var
while @@fetch-status=0
begin
print @var
fetch next from cur1 into @var
end close cur1 deallocate cur1
end
if @type='b'
begin
open cur2 fetch next from cur2 into @var
while @@fetch_status=0
begin
print @var
fetch next from cur2 into @var
end close cur2 deallocate cur2
end
else
print 'enter only a or b plz'
end

I am getting cursor with the name cur1 already exist...
plz help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 10:20:27

why using cursor for this? isnt this enough?


alter proc test ( @type varchar(255)
as
begin
select table_name from information_schema.tables
where table_name like 'rpt1%'
and @type='a'

union all

select table_name from information_schema.tables
where table_name like 'rpt2%'
and @type='b'
go

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-02-20 : 12:47:23

Thanks Visakh,

you are right, however instead of print I will be calling another stored proc in while loop which will create objects etc for each and every table(table_name).

That is why I used cursor here, is there any work around.

Thanks Again.

quote:

Originally posted by visakh16

why using cursor for this? isnt this enough?


alter proc test ( @type varchar(255)
as
begin
select table_name from information_schema.tables
where table_name like 'rpt1%'
and @type='a'

union all

select table_name from information_schema.tables
where table_name like 'rpt2%'
and @type='b'
go

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 12:52:35

what does stored proc return? a single value or resultset?

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-02-20 : 13:02:09

It is not returning any thing but performing lot of DML operations

quote:
Originally posted by visakh16

what does stored proc return? a single value or resultset?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 13:03:12

DML operations like what? can you give a briefing?

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-02-20 : 13:41:28

It is also performing DDL, it is creating table in some other db
we need to pass the table name from this sp it will create that
table in some other db.

Regards,

quote:
Originally posted by visakh16

DML operations like what? can you give a briefing?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 13:44:45

do you mean for each row of your above query, you call a stored procedure to create new table? can you explain purpose of that?

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-02-20 : 13:53:33

Yes Visakh, it create table for each row executed for the stored proc
with cursor, performance is not an issues as this will not be executed every now and then, its one time job...

the sp which is called also created a function, view etc other object
but rt now what we want is to send an table name

say for eg

exec util_create_object 'type_table','table_name' this is called
for each row of the sp with cursors.

Regards,

quote:
Originally posted by visakh16

do you mean for each row of your above query, you call a stored procedure to create new table? can you explain purpose of that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-21 : 02:16:47

is your attempt to create the tables that exist in one db ontyo another db?

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-02-21 : 02:49:16

Yes Visakh,

Sorry for late reply.

We are creating a table( as well as other object with some validation) into other db, which exist in some other db.

quote:
Originally posted by visakh16

is your attempt to create the tables that exist in one db ontyo another db?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-21 : 02:52:29

can i ask whats the purpose of that? also what does this mean?

as well as other object with some validation

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-02-21 : 03:06:04

It actually create table ( & proc function views etc) by taking the structure passed in the sp, it add's identity constrainst etc, it's on remote server I cannot copy here restricted.

I just need to pass type as TABLE and Name as TABLE_NAME to the sp util_creat_object

Thanks,

quote:
Originally posted by visakh16

can i ask whats the purpose of that? also what does this mean?

as well as other object with some validation

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-02-21 : 03:16:41

There is a data dict table, in main db which contains all the info ( like information_schema.columns)
when a table name is passed, it get all the required field for that table in cursor where table is the tablename passed.
it drops that table if already exist if not create new one.

Thanks

quote:
Originally posted by aakcse

It actually create table ( & proc function views etc) by taking the structure passed in the sp, it add's identity constrainst etc, it's on remote server I cannot copy here restricted.

I just need to pass type as TABLE and Name as TABLE_NAME to the sp util_creat_object

Thanks,

quote:
Originally posted by visakh16

can i ask whats the purpose of that? also what does this mean?

as well as other object with some validation

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-21 : 04:01:44

sounds like what you need is just a ssis package with transfer sql objects task

http://msdn.microsoft.com/en-us/library/ms142159.aspx

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-02-22 : 14:43:15

quote:
Originally posted by visakh16

sounds like what you need is just a ssis package with transfer sql objects task

http://msdn.microsoft.com/en-us/library/ms142159.aspx

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-02-22 : 14:49:24

Thanks Visakh,

Yes we can load using SSIS, however my manager want me to write a SP for the time being.

Can you please help me in correcting the above sp error.

Thanks Again.

quote:
Originally posted by aakcse

quote:
Originally posted by visakh16

sounds like what you need is just a ssis package with transfer sql objects task

http://msdn.microsoft.com/en-us/library/ms142159.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources