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

THANKSDo a insert inot temp table and then use the temp table to declare the cursor|||/*
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|||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.|||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.|||I think Enigma was getting at something like:CREATE TABLE #procResults (
column1 -- whatever
column2 -- etc
)

DECLARE scum CURSOR FOR SELECT
column1, column2
FROM #procResultsDoes that make more sense?

-PatP|||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.|||Oops! My bad, I forgot an important step (sleep deprivation does funny things to me!). Let's try: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|||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|||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|||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|||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|||OK...anyone want to ask WHY there's a CURSOR in the mix?

How many levels of nested cursors do you have?|||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.|||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()|||Hi,

Thanks,hanif For The Suggestion, But Yes I Will Have To Think On It On A Long -term Basis . About How To Convert All The Procs Into Functions .but Right Now Will Have To Stick On The Existing System To See How It Works.

Thanks.