Detecting existence of a cursor and temporary table

How do I check for the existence of a cursor? I am trying to create a cursor cursorA but would like check to make sure that a cursor with that name does not exist before creating one by using CREATE CURSOR.

Also, how do I test for the existence of a temporary table before creating one with that name?

Musleh
mfarid1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

formulaCommented:
You can't check for the existance of a cursor to my knowledge.  However, properly deallocating a cursor will insure that no conflict will ever exist.
For example, do the following when programming each cursor:

declare ... open ... fetch ... close ... deallocate.

For temporary tables, you can always check for the existance of a temp table by looking at tempdb, then dropping the existing table before recreating the new one.  Here is example:

if exists (select * from tempdb..sysobjects where id = object_id('dbo.tablename') and sysstat & 0xf = 3)
      drop table dbo.tablename
GO

0
mfarid1Author Commented:
Lets say you have already declared a cursor. During open, the proc encounters an error and it kicks out. What do you do then? You have a cursor hanging. Also, during execution of some other statement after the declare you might encounter a fatal error. How do you get around that?

Musleh
0
Gustavo Perez BuenrostroCommented:
Musleh,

--You can check the existence of a cursor as follows:

if (select count(cursor_handle)
      from master..syscursors
     where cursor_name='cr_YourCursor')>0
  begin
    print 'cursor cr_YourCursor exists'
  end
else
  begin
    declare cr_YourCursor cursor
    for select * from master..sysmessages
  end


-- You can check the existence of a temporary table as follows:

if (select count(id)
      from tempdb..sysobjects
     where id
          =object_id(N'tempdb..#YourTable'))>0
  begin
    print 'table #YourTable exists'
  end
else
  begin
    create table #YourTable (YourColumn int)
  end

PD: Please let me know if the code above works as you expect.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
formulaCommented:
See gpbuenrostro's comment, his answer is more complete than mine and should work
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.