Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40

    Unanswered: Can't use cursor with SP

    Hi

    I have a SP and in it I call another SP which returns one row in one column, I need to concatenate the value (varchar) to the query in the first SP.
    I tried to use cursor with FAST_FORWARD to fetch the result and concatenate it, but I get an error, here is what I tried:

    DECLARE Cur CURSOR FAST_FORWARD
    FOR SP_Something @SomeValue

    So is it possible to use cursor on SP ? And if it's possible so how ???

    Thanks,

    Inon.
    Last edited by Inoni; 11-15-04 at 11:21.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't think so....why not put the result set into a table variable?
    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.

  3. #3
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40
    Quote Originally Posted by Brett Kaiser
    I don't think so....why not put the result set into a table variable?
    This also don't work, I get an error when I try to INSERT the result from the inner SP to a table var... so is THIS option possible ??

    I just want to add that I have several ways to make this work, but I'm trying to be efficient, this is why I want to use an inner SP and not just make the process in the same big SP, I can also make changes in the code (PHP) but I'm trying to make it work this way.

    Thanks,

    Inon.
    Last edited by Inoni; 11-15-04 at 12:34.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [homer]
    doooh
    [/homer]

    Yup...can't do that...but you can do

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    GO
    
    CREATE PROC mySproc99
    AS
    SELECT OrderId FROM Orders
    GO
    
    CREATE TABLE #myTemp99(OrderId int)
    
    INSERT INTO #myTemp99(OrderId) EXEC mySproc99
    
    SELECT * FROM #myTemp99
    GO
    
    SET NOCOUNT ON
    DROP TABLE #myTemp99
    DROP PROC mySproc99
    GO
    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.

  5. #5
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40
    Quote Originally Posted by Brett Kaiser
    [homer]
    doooh
    [/homer]

    Yup...can't do that...but you can do

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    GO
    
    CREATE PROC mySproc99
    AS
    SELECT OrderId FROM Orders
    GO
    
    CREATE TABLE #myTemp99(OrderId int)
    
    INSERT INTO #myTemp99(OrderId) EXEC mySproc99
    
    SELECT * FROM #myTemp99
    GO
    
    SET NOCOUNT ON
    DROP TABLE #myTemp99
    DROP PROC mySproc99
    GO
    Hmmm... there goes the efficient part...

    Inon.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well...

    I don't think (I hate when that happens) that I would ever use effecient and cursor in the same sentence...

    Unless it was like

    "I wish the developer wrote effecient code instead of using a cursor"

    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.

  7. #7
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40
    Quote Originally Posted by Brett Kaiser
    Well...

    I don't think (I hate when that happens) that I would ever use effecient and cursor in the same sentence...

    Unless it was like

    "I wish the developer wrote effecient code instead of using a cursor"

    I agree, didn't mean to express discontent of your solution BTW...

    You see, the DB is for a web page, that SP will be called many times.

    Thanks for the help,

    Inon.

  8. #8
    Join Date
    Feb 2004
    Posts
    88

    Can't use cursor with SP

    I'm not sure what you're trying to do, but it looks like you want to use a SP to return a varchar that contains a query that you then want to execute - am I right ?

    If so, try making the SP a function, viz:

    create function dbo.some_func (@inp_value char(?))
    returns varchar(100)
    as
    begin
    declare @temp_var varchar(100)

    set @temp_var = 'select * from ' + @inp_value
    return @temp_var

    end

    then in your calling sp:

    select @mysql = some_func(@parm)
    execute(@mysql)

    HTH

  9. #9
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40
    Quote Originally Posted by thompbil
    I'm not sure what you're trying to do, but it looks like you want to use a SP to return a varchar that contains a query that you then want to execute - am I right ?
    Almost right, the inner SP returns only a string that will be concatenate to the query in the outer SP, meaning, the inner SP returns only a part of a query, not entire query.

    I have a SP that returns a result, I want to use this result in another SP, I didn't want to run both queries because (and correct me if I'm wrong) I know that for optimal performance it's not recommended to run two separated queries on two table in one SP, because the optimizer will confuse with the best execution plans for each query...

    Anyway, this is what I finally did, ran both in one SP... is it as bad as I think ?? And should I just make some process in the code and just run two separated SPs? I wanted to make it in one connection session since it will run many many times and by using one SP I cut traffic (for this option only of course) in 50%...

    So what do you say is better? One SP with two queries or two SP's with two connections? (But each has its own optimal execution plan).

    Thanks,

    Inon.

Posting Permissions

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