Passing output of one sql statement as input of another sql statement and looping using Cursor

Question

Passing output of one sql statement as input of another sql statement and looping using Cursor

chandrashekar.2512

SSC Eights!

Points: 965
More actions
January 20, 2010 at 1:39 am

#100049

Hi Eveyone,
I have to create a single stored proceudre in which i have to pass id from one sql statement to another sql statement which is a pressent in another table
And With that input value i have to select set of records and i have to loop through from first sql statement to another sql statement as long as value in both the table is exist.
Followings are my effort,
Declare MyCur Cursor
For
Select txt_StateCode From tbl_state
Open MyCur
Declare @StateID int
Fetch Next From MyCur Into @StateID
While @@Fetch_Status = 0
Begin
SELECT @StateID = @StateID
DECLARE District_Cursor Cursor for
Select d.txt_DistrictCode from tbl_District d,tbl_State s where d.txt_stateCode=s.txt_StateCode and d.txt_StateCode=@StateID
OPEN District_Cursor
Declare @DisID int
Declare @smaldate smalldatetime
Fetch Next From District_Cursor into @DisID
While @@Fetch_Status=0
Begin
Select @DisID= @DisID
SET @smaldate = select max(dat_Date) from tbl_Transaction where txt_statecode=@StateID and txt_DistrictCode=@DisID and dat_Date='3/31/2008'
Fetch Next From District_Cursor into @DisID
End
CLose District_Cursor
Deallocate District_Cursor
print @StateID
FETCH NEXT FROM MyCur INTO @StateID
END
Close MyCur
Deallocate MyCur
But i am getting error Incorrect syntax near the keyword 'select'.
Please somebody help me in creating single dynamic procedure as it is urgent.
Thanks & Regards
Chandrashekar

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

You've probably just shot yourself in the mouth by saying it's "urgent". 😉

Why are you writing a cursor for this instead of using set based code? And what is the purpose of code like the following?

Select @DisID= @DisID

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 2

Duplicate thread

http://qa.sqlservercentral.com/Forums/Topic850276-392-1.aspx

Clear Sky SQL
My Blog[/url]