Sql cursor Declare

Posted on 2014-08-11
Last Modified: 2014-08-12
I am trying to declare a cursor in a stored procedure that is a little different from the standard cursor declare in that
I would like it make use of a stored procedure parameter (@ConfigurationFilename) and include a like as part of the where clause.  It is the where clause with the single quotes that is causing me grief.  I thought it was a matter of just using 2 double quotes but getting syntax errors'

Here is the select clause I would like to use for the cursor

select as [Column],
   syscolumns.xusertype as [Type],
   sysobjects.xtype as [Objtype]
   sysobjects, syscolumns
where =
and   sysobjects.xtype = 'u'
and = ' +  @ConfigurationFilename +
' and like '%FileFolder%''

I tried using

DECLARE @cursor VARCHAR(1000)

SET @cursor = 'DECLARE Files_cursor CURSOR
    FOR select as [Column],
   syscolumns.xusertype as [Type],
   sysobjects.xtype as [Objtype]
   sysobjects, syscolumns
where =
and   sysobjects.xtype = 'u'
and = ' + @ConfigurationFilename
and like '%FileFolder%'

EXEC (@cursor)

OPEN Files_cursor

(for sake of space I left off  the rest of the cursor)
Question by:johnnyg123
    LVL 142

    Accepted Solution

    You cannot do it like this. The cursor variable scope is over once the exec is completed.

    Please consider to avoid cursor anyhow
    LVL 11

    Expert Comment

    I don't think you need Dynamic-SQL, try this:
    SELECT	[Column]	=
    ,	[Type]		=	b.xusertype
    ,	[Objtype]	=	a.xtype
    FROM	sysobjects	a
    JOIN	syscolumns	b	ON =
    WHERE   a.xtype = 'u'
    AND = @ConfigurationFilename
    AND LIKE '%FileFolder%'

    Open in new window

    LVL 10

    Expert Comment

    I suggest you please do not try to handle the cursor dynamically.

    If you really need to use Dynamic code, you can instead dynamically create one table to hold the records and further use Cursor to retrieve the records from that.

    Author Closing Comment

    Very interesting....Thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now