[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

Sql cursor Declare

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
   syscolumns.name as [Column],
   syscolumns.xusertype as [Type],
   sysobjects.xtype as [Objtype]
from
   sysobjects, syscolumns
where sysobjects.id = syscolumns.id
and   sysobjects.xtype = 'u'
and   sysobjects.name = ' +  @ConfigurationFilename +
' and syscolumns.name like '%FileFolder%''

I tried using

DECLARE @cursor VARCHAR(1000)

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


EXEC (@cursor)

OPEN Files_cursor

(for sake of space I left off  the rest of the cursor)
0
johnnyg123
Asked:
johnnyg123
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You cannot do it like this. The cursor variable scope is over once the exec is completed.

Please consider to avoid cursor anyhow
http://mobile.experts-exchange.com/Database/MS-SQL-Server/A_13640-processing-cursor-vs-temp-table-syntax.html
0
 
John_VidmarCommented:
I don't think you need Dynamic-SQL, try this:
SELECT	[Column]	=	b.name
,	[Type]		=	b.xusertype
,	[Objtype]	=	a.xtype
FROM	sysobjects	a
JOIN	syscolumns	b	ON	a.id = b.id
WHERE   a.xtype = 'u'
AND	a.name = @ConfigurationFilename
AND	b.name LIKE '%FileFolder%'

Open in new window

0
 
HuaMinChenBusiness AnalystCommented:
Hi,
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.
0
 
johnnyg123Author Commented:
Very interesting....Thanks!
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now