Sql Script Questions using Cursor???

Question

Hello great developers of SQL!!!  I have a question about this script i am trying to build....Let me first tell you what I am trying to accomplish.....

 

1 I am trying to get all of my "parent_topic_id's with idenitcal "sort_orders"

2.  For each "parent_topic_id" that has an identical sort order I want to run that update command...that sets the sort order back to normal which is 12345678 etc.......

The first query will only return the "parent_topic_ids" that have duplicate sort orders......

 

I have a few errors that all say the same thing  check them out....what do they mean by declare the "@parent_topic_id"  do i need to put "int" next to it???? Thank you for any assistance!!

 

Msg 137, Level 15, State 2, Line 9

Must declare the scalar variable "@parent_topic_id".

Msg 137, Level 15, State 2, Line 34

Must declare the scalar variable "@parent_topic_id".

Msg 137, Level 15, State 2, Line 44

Must declare the scalar variable "@parent_topic_id".

Msg 137, Level 15, State 2, Line 55

Must declare the scalar variable "@parent_topic_id".

Msg 137, Level 15, State 2, Line 58

Must declare the scalar variable "@parent_topic_id".

Msg 102, Level 15, State 1, Line 63

Incorrect syntax near 'getParetIdAndTopics'.

 

 

 

 

DECLARE getBrokenParentIDs Cursor For Select sam_topic_items.parent_topic_id From sam_topic_items Open fixContentSort Fetch Next From getBrokenParentIDs Into @parent_topic_id int While @@Fetch_Status = 0 Begin Declare getParetIdAndTopics Cursor For Select dt.parent_topic_id From ( SELECT st.id,sti.parent_topic_id, st.description_short, sti.sort_order FROM sam_topic_items sti INNER JOIN sam_topic st ON sti.topic_id = st.id WHERE EXISTS ( SELECT 1 FROM sam_topic_items sti1 INNER JOIN sam_topic st1 ON sti1.topic_id = st1.id WHERE st1.type = 'Topic' AND sti.parent_topic_id = sti1.parent_topic_id AND sti.sort_order = sti1.sort_order And sti.parent_topic_id = @parent_topic_id GROUP BY sti1.parent_topic_id, sti1.sort_order, st1.Type HAVING COUNT(sti1.sort_order) > 1 ) ) dt Open getParetIdAndTopics Fetch Next From getParetIdAndTopics Into @parent_topic_id While @@Fetch_Status = 0 Begin Declare @intCounter int Set @intCounter = 0 Update sam_topic_items Set @intCounter = sort_order = @intCounter + 1 Where sam_topic_items.parent_topic_id = @parent_topic_id Fetch Next From getParetIdAndTopics Into @parent_topic_id End Close getParetIdAndTopics Deallocate getParetIdAndTopics

Answer 1

It means that the variables are not defined. There is NO DECLARE statement defining such variables in the current scope.

I also noticed that you opened one cursor and fetch from a different one.

Tryin2Bgood wrote:

Open fixContentSort Fetch Next From getBrokenParentIDs

You would really do yourself and your project a great service to avoid using CURSORs. There is most likely a set based way to get the results you desire.

Answer 2

Really?!?! Why are Cursors so bad??? and yes I saw that issue with the Fetch and I fixed that...I got the cursor working and it seems to work well...here is the final result.....

 

 

 

Declare @parent_topic_id int

Declare @topic_id int

Declare @old_sort_order int

Declare @intCounter int

DECLARE getBrokenParentIDs Cursor For

Select Distinct dt.parent_topic_id

From

(

SELECT st.id,sti.parent_topic_id,

st.description_short,

sti.sort_order

FROM sam_topic_items sti

INNER JOIN sam_topic st ON sti.topic_id = st.id

WHERE EXISTS

(

SELECT 1

FROM sam_topic_items sti1

INNER JOIN sam_topic st1 ON sti1.topic_id = st1.id

WHERE st1.type = 'Topic'

AND sti.parent_topic_id = sti1.parent_topic_id

AND sti.sort_order = sti1.sort_order

GROUP BY sti1.parent_topic_id, sti1.sort_order, st1.Type

HAVING COUNT(sti1.sort_order) > 1

)

) dt

Order By parent_topic_id

Open getBrokenParentIDs

Fetch Next From getBrokenParentIDs

Into @parent_topic_id

While @@Fetch_Status = 0

Begin

Set @intCounter = 0

Declare getParetIdAndTopics Cursor For

 

SELECT sti.topic_id, sti.sort_order

From sam_topic_items sti

Where sti.parent_topic_id = @parent_topic_id

Order By sti.sort_order

 

 

 

Open getParetIdAndTopics

Fetch Next From getParetIdAndTopics

Into @topic_ID, @old_sort_order

While @@Fetch_Status = 0

Begin

Select @topic_id, @parent_topic_id, @old_sort_order,@intCounter + 1

Set @intCounter = @intCounter + 1

-- Update sam_topic_items

-- Set @intCounter = sort_order = @intCounter + 1

-- Where sam_topic_items.topic_id = @topic_id

-- And sam_topic_items.parent_topic_id = @parent_topic_id

-- And sam_topic_items.sort_order = @old_sort_order

Fetch Next From getParetIdAndTopics

Into @topic_ID, @old_sort_order

End

Close getParetIdAndTopics

Deallocate getParetIdAndTopics

Fetch Next From getBrokenParentIDs

Into @parent_topic_id

End

Close getBrokenParentIDs

Deallocate getBrokenParentIDs

Answer 3

>>Really?!?! Why are Cursors so bad??? and yes I saw that issue with the Fetch and I fixed that...I got the cursor working and it seems to work well...here is the final result.....<<

Cursors are bad for the same reason you don't hammer nails with a wrench.

SQL is a set based language, and the relational engine is based on the concept of taking a statement that works on multiple rows and doing it, rapidly, doing all of the looping and such internally.  When you use cursors, you are taking the power of the engine and forcing your looping algorithms on it, and limiting SQL Server to doing one thing at a time.

T-SQL is interpreted, which means it is slow to do single operations, so the fewer operations you do, the more you push into the query processor, the faster your results *usually* are (there are exceptions.)

In the case of your query, there may be a solution that boils down to one statement that you have to execute.  It is not guarateed to be faster, but it will be easier to work with.

But if your solution works, and you get the results/performance you need, it is fine...but you could probably do better.  I don't really understand what you are doing, so I won't even try :)

Answer 4

"Cursors are bad for the same reason you don't hammer nails with a wrench."

That was funny  

 

 

Thanks for the passing along the knowledge!!

Answer 5

I like this one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=892822&SiteID=1

 and this one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=790533&SiteID=1

I especially like the part that says about cursors (albeit maybe not completely serious): "... You can never loathe them enough. ..."

:-)

 

The funny part about all of this is that I am reviewing a query that I hate because of a cursor.  Do you mind if I reference this post as part of my review?

Answer 6

Do what you need in pursuit to spread the knowledge!!!