Post reply

passing out put of one sql statement into another as a input and looping using cursor

  • January 20, 2010 at 1:32 am

    #218305

    Hi Eveyone,

    I have to create a single stored proceudre in which i ahve to pass output from one sql statement need to be passed as input for another.

    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

  • January 20, 2010 at 1:35 am

    #1105875

    sorry i posted in different area

  • January 20, 2010 at 1:40 am

    #1105878

    Urgent or not, your error is on this line

    SET @smaldate = select max(dat_Date) from tbl_Transaction where txt_statecode=@StateID and txt_DistrictCode=@DisID and dat_Date='3/31/2008'

    The correct syntax is

    select @smaldate = max(dat_Date) from tbl_Transaction where txt_statecode=@StateID and txt_DistrictCode=@DisID and dat_Date='3/31/2008'

    You dont mix SET and SELECT.

    However , my conscious wont allow me to pass up the opportunity to point out that you dont need cursors here.

    They should be removed in place of correct Set based logic.

    Heres a good article to get you started http://qa.sqlservercentral.com/articles/T-SQL/66097/


    Clear Sky SQL
    My Blog[/url]

  • January 20, 2010 at 2:15 am

    #1105898

    Thanks Dave,

    But when i remove select and execute that statement it is giving error

    Syntax error converting the varchar value 'max(dat_Date) from tbl_Transaction where txt_statecode=' to a column of data type int.

    but i have declared the varialbe @smaldate of type smalldatetime and setting that to the sql statement.

    Y it is happening so.

  • January 20, 2010 at 3:55 am

    #1105930

    So what does your statement look like now ?


    Clear Sky SQL
    My Blog[/url]

  • January 21, 2010 at 11:43 pm

    #1107117

    Heh... after two days, I wonder if it's still "urgent". :hehe:

    --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

Viewing 6 posts - 1 through 5 (of 5 total)

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