How to declare cursor after with keyword in SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi,

I want to use cursor after with keyword its giving an error incorrect syntax near...

What I have tried:

SQL

BEGIN TRANSACTION
		;WITH C AS
		(
		  SELECT MC_id
		  FROM CM_MaterialCategory
		  WHERE MC_id IN (SELECT id FROM @id)
		  UNION ALL
		  SELECT T.MC_id
		  FROM CM_MaterialCategory AS T INNER JOIN C ON T.MC_parent = C.MC_id
		)
		DECLARE A CURSOR FOR SELECT DISTINCT MC_id FROM C
			UPDATE CM_MaterialCategory SET MC_isdeleted=1 WHERE MC_id IN(29,28,27)
			 
			UPDATE CT_MaterialCategory SET TMC_isdeleted=1 WHERE TMC_id=@TMCID

			EXEC Insertinto_Log @tablename='CM_MaterialCategory',@fun=2,@U_id=@U_id,@Key=@MC_id,@datetime=@ErrorTime
			EXEC Insertinto_Log @tablename='CT_MaterialCategory',@fun=2,@U_id=@U_id,@Key=@TMCID,@datetime=@ErrorTime
		COMMIT

Posted 5-May-16 4:05am

itsmehaboob

Updated 5-May-16 4:18am

CHill60

v2

Add a Solution

Comments

Dave Kreskowiak 5-May-16 10:44am

As Chill60 has said already, you REALLY need to try and avoid using a CURSOR whenever possible. Cursors do not scale well as they will run slower and slower the greater the number of records you have the Cursor iterating over.

itsmehaboob 6-May-16 0:52am

Yeah that's really we are doing now(removing all cursors) but in this case i want to run a sub query before updating CM_table for getting CT_tables's primary key then only possible to update CT_Table(Second Update query in that section) can you give any suggestion for that ?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-05-05T04:18:00

Declare the cursor before the CTE i.e.

SQL

DECLARE A CURSOR FOR
WITH C AS
(
  SELECT MC_id
  FROM CM_MaterialCategory
  WHERE MC_id IN (SELECT id FROM @id)
  UNION ALL
  SELECT T.MC_id
  FROM CM_MaterialCategory AS T INNER JOIN C ON T.MC_parent = C.MC_id
)
SELECT DISTINCT MC_id FROM C

This solution comes with all the usual warnings about cursors - are you sure you can't do this with set-based processing?

And I don't think your code is going to work without a FETCH somewhere