Use stored procedure in place of select statement for cursor
-
I want to use stored procedure in place of select statement for cursor. e.g. DECLARE ProfInd_CURSOR CURSOR for dbo.EM_GET_WORKHOURS 88,1,'01/01/2007','08/08/2007' OPEN ProfInd_CURSOR .............................. any idea
Do good and have good.
-
I want to use stored procedure in place of select statement for cursor. e.g. DECLARE ProfInd_CURSOR CURSOR for dbo.EM_GET_WORKHOURS 88,1,'01/01/2007','08/08/2007' OPEN ProfInd_CURSOR .............................. any idea
Do good and have good.
Modest Bird wrote:
I want to use stored procedure in place of select statement for cursor.
I don't think that you can do this. What you can do is create a temporary table in your current procedure and fill it from nested procedure. select from this temporary table in cursor
-
I want to use stored procedure in place of select statement for cursor. e.g. DECLARE ProfInd_CURSOR CURSOR for dbo.EM_GET_WORKHOURS 88,1,'01/01/2007','08/08/2007' OPEN ProfInd_CURSOR .............................. any idea
Do good and have good.
Hello Modest, You can try to use User Definied Function for your cursor instead of stored procedure. Like this: DECLARE cur CURSOR FORWARD_ONLY READ_ONLY FOR SELECT * FROM dbo.fn_GetTestData(1) Please get the sample from --> http://www.oin1.com/Technical/SQL/FunctionInCursor.htm[^] Eliz.K
-
I want to use stored procedure in place of select statement for cursor. e.g. DECLARE ProfInd_CURSOR CURSOR for dbo.EM_GET_WORKHOURS 88,1,'01/01/2007','08/08/2007' OPEN ProfInd_CURSOR .............................. any idea
Do good and have good.
You can't. You have to provide a SELECT statement, and you cannot directly SELECT from a stored procedure's results. You can replace the stored procedure with a user-defined function - either an inline table-valued function, which simply contains a single SELECT statement, or a multistatement table-valued function. Alternatively, the INSERT statement can insert the results of a stored procedure into a table (e.g. INSERT table (columns) EXECUTE stored_proc). You would need to create a temporary table or table variable to store the results, with the appropriate schema, insert into the temporary table/table variable, then declare the cursor to select from that.
Stability. What an interesting concept. -- Chris Maunder