[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Detecting existence of a cursor and temporary table

Posted on 2000-01-24
4
Medium Priority
?
669 Views
Last Modified: 2010-03-30
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
0
Comment
Question by:mfarid1
  • 2
4 Comments
 
LVL 2

Expert Comment

by:formula
ID: 2381895
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
 

Author Comment

by:mfarid1
ID: 2381915
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
 
LVL 4

Accepted Solution

by:
Gustavo Perez Buenrostro earned 400 total points
ID: 2381928
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
 
LVL 2

Expert Comment

by:formula
ID: 2381970
See gpbuenrostro's comment, his answer is more complete than mine and should work
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question