Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2004
    Posts
    49

    Question Unanswered: How To Call A Procedure While Declaring A Cursor

    HI,
    WHILE DECLARING A CURSOR TO SELECT RECORDS FROM A TABLE WE NORMALLY WRITE :-

    DECLARE CUR_NAME CURSOR
    FOR SELECT * FROM CLEANCUSTOMER

    BUT SAY, IF I HAVE WRITTEN A SIMPLE PROCEDURE CALLED AS MY_PROC :-

    CREATE PROCEDURE MY_PROC
    AS
    SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
    FROM CLEANCUSTOMER A
    INNER JOIN TRCUSTOMERPREFERENCE03JULY B
    ON A.INTCUSTOMERID = B.INTCUSTOMERID
    INNER JOIN TMPREFERENCE C
    ON B.INTPREFERENCEID = C.INTPREFERENCEID
    ORDER BY B.INTPREFERENCEID

    WHICH IS RUNNING FINE AND GIVING ME THE REQUIRED DATA WHILE EXECUTING THE PROCEDURE :-

    EXEC MY_PROC

    BUT IF I WANT TO CALL THIS PROCEDURE MY_PROC WHILE DECLARING A CURSOR :-

    I AM USING :-

    DECLARE CHK_CUR CURSOR
    FOR SELECT * FROM MY_PROC

    WHICH IS GIVING AN ERROR "Invalid object name 'MY_PROC'."


    AND IF I USE :-

    DECLARE CHK_CUR CURSOR
    FOR EXEC MY_PROC

    WHICH IS GIVING AN ERROR "Incorrect syntax near the keyword 'EXEC'".


    AND IF I USE :-

    DECLARE CHK_CUR CURSOR
    FOR CALL MY_PROC

    WHICH IS GIVING AN ERROR "Incorrect syntax near 'CALL'. "

    IS THERE ANY WAY BY WHICH I CAN FETCH RECORDS FROM THE STORED PROCEDURE?
    HOW DO I DECLARE THE PROCEDURE WHILE WRITING THE CURSOR
    PLS HELP.

    I NEED THIS URGENTLY, I HAVE TO USE THE CURSOR TO FETCH THE RECORDS FROM THE SP,THAT'S HOW THEY WANT IT.I CAN'T HELP IT AND I DON'T KNOW HOW

    THANKS

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Do a insert inot temp table and then use the temp table to declare the cursor
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    23
    /*
    As far as i know, there is only one way, you must create a temporary table, for example:
    */

    Create Proc Caller
    As
    Create Table #temp (INTCUSTOMERID [FieldType],
    CHREMAIL [FieldType], INTPREFERENCEID [FieldType],
    CHRPREFERENCEDESC [FieldType])
    Declare CHK_CUR For Select a.INTCUSTOMERID From #temp a
    Insert Into #temp (INTCUSTOMERID, CHREMAIL, INTPREFERENCEID,
    CHRPREFERENCEDESC) Exec MY_PROC

    /*Do what ever you want with your cursor

  4. #4
    Join Date
    Apr 2004
    Posts
    49

    Question

    Hi,
    Thanks Enigma And Sneaky Pie For The Quick Replies ,but The Problem Still Lies And It Has Not Helped Me Much.

    The Thing Is It Might Be Possible That I Have Not Written It Down
    Properly Or You Might Not Have Understood What I Have Wanted To Ask From You.

    First Of All I Don't Want To Declare A Cursor Inside A Procedure For Some Reasons Out Here.

    Second,i Want To Declare A Cursor Outside The Procedure And Then Call The Procedure From The Cursor.

    Third,as You Said That I Should Use A Temp Table,which Is Not Possible Cause The Temp Table Is Not Present Outside The Procedure In Which It Is Created,i Might Be Wrong ,but Pls Tell Me So Then.

    Fourth, I Can Go For A Permanent Table ,but It Consumes Too Much
    Space,which Is A Constraint In Our Case.

    Pls ,look Into The Above Scenario And Help Me Out With It.i Personally Think That It Should Be A Nobrainer,but Somehow It Has Become A Nightmare For Me.

    Pls Look Into It As Soon As Possible As I Am Really Feeling The Heat On This Now.

    Thanks.

  5. #5
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    23
    But the way with the temporary table is the only solution with procs.
    Create the #table and after that you are able to create a cursor fetching this #table. If you need a result after some changes or calculations with the cursor data, you need a second #table. At the end return the data of the second #table.

    There is, maybe, an other way, but it works with funtions, not with procs. You can create a function that returns a cursor.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think Enigma was getting at something like:
    Code:
    CREATE TABLE #procResults (
    column1 -- whatever
    column2 -- etc
    )
    
    DECLARE scum CURSOR FOR SELECT
    column1, column2
       FROM #procResults
    Does that make more sense?

    -PatP

  7. #7
    Join Date
    Apr 2004
    Posts
    49

    Question

    HI,
    tHANKS SNEAKY PIE ,BUT I THINK PAT GOT IT RIGHT ABOUT WHAT I WANT : - BUT THE PROBLEM STILL PERSISTS.

    I AM GETTING :-

    IF,
    I AM USING :-

    DECLARE CHK_CUR CURSOR
    FOR SELECT * FROM MY_PROC

    WHICH IS GIVING AN ERROR "Invalid object name 'MY_PROC'."


    AND IF I USE :-

    DECLARE CHK_CUR CURSOR
    FOR EXEC MY_PROC

    WHICH IS GIVING AN ERROR "Incorrect syntax near the keyword 'EXEC'".

    AND IF I USE :-

    DECLARE CHK_CUR CURSOR
    FOR CALL MY_PROC

    WHICH IS GIVING AN ERROR "Incorrect syntax near 'CALL'. "


    IS THERE ANY WAY TO CALL THE PROCEDURE FROM A CURSOR AND SAVE THE RESULTS OF THE PROCEDURE IN A VARICABLE OR SOMETHING.
    BUT REMEMBER ONLY AFTER THE PROCEDURE IS CALLED FROM THE CURSOR.

    THANKS.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oops! My bad, I forgot an important step (sleep deprivation does funny things to me!). Let's try:
    Code:
    CREATE TABLE #procResults (
    column1 -- whatever
    column2 -- etc
    )
    
    INSERT INTO #procResults  -- The "missing link"
       EXECUTE myProc
    
    DECLARE scum CURSOR FOR SELECT
    column1, column2
       FROM #procResults
    -PatP

  9. #9
    Join Date
    Apr 2004
    Posts
    49

    Thumbs up

    Hi,

    Fantastic !!! Right On !! Bull's Eye !!!!!

    Got It ... Thanks To You... I Was Scratching My Skull Whole Of Last Week.

    But,

    Now Though, Things Are Running Fine .... It Is Giving :-

    System Low On Resources
    Affecting The Whole System .

    Is There Any Way Or Any Settings That Could Be Changed. ?

    So That I Can Allocate More Resources While The Cursor Is Running.

    Thanks

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What is giving the messages? Are they in the Transact-SQL output, the SQL Server log, an NT log, or somewhere that I haven't thought of yet?

    -PatP

  11. #11
    Join Date
    Apr 2004
    Posts
    49
    HI,

    THE MESSAGES ARE FROM THE T-SQL OUTPUT :-

    "SYSTEM LOW ON RESOURCES SOME OF THE RESULTS WOULD BE DROPPED."

    THIS IS AFFECTING MY WHOLE SYSTEM.... AFTER I RUN THE CURSORS

    Is There Any Way Or Any Settings That Could Be Changed. ?

    So That I Can Allocate More Resources While The Cursor Is Running.

    Thanks

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your choices are quite limited in that situation. You can upgrade the hardware (most likely RAM memory) to make more resources available, or stop other (non-essential) processes running on the same machine to free up the resources that they are using. Those are the only choices that I think might help.

    -PatP

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK....anyone want to ask WHY there's a CURSOR in the mix?

    How many levels of nested cursors do you have?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Apr 2004
    Posts
    49
    Hi,

    I Don't Think My Ram Must Be A Problem Has It Is 256mb Ram And I Have Seen Stopping The Remaining Processes And Running Only This Processes Still It Gives A Problem.

    Also, There Is Only One Level Of Cursors.

    And The Data Contained In The Table Is Just Around 10,000 .so Shouldn't Be Problem.

    So If Something About This Could Be Done Then It Would Be Fantastic.

    Thanks.

  15. #15
    Join Date
    Nov 2003
    Posts
    94
    Don't use a Procedure use an inline table function:

    CREATE FUNCTION dbo.fn_MY_PROC()
    RETURNS TABLE
    AS
    RETURN (

    SELECT TOP 100 PERCENT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
    FROM CLEANCUSTOMER A
    INNER JOIN TRCUSTOMERPREFERENCE03JULY B
    ON A.INTCUSTOMERID = B.INTCUSTOMERID
    INNER JOIN TMPREFERENCE C
    ON B.INTPREFERENCEID = C.INTPREFERENCEID
    ORDER BY B.INTPREFERENCEID

    )

    declare cursor cur as fast_forward local for
    select * /*or whatever*/ from dbo.fn_MY_PROC()

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •