Cant 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.I don't think so...why not put the result set into a table variable?|||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.|||doooh

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

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

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

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

:D|||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"

:D

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