Problem related to cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem related to cursor

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2006-02-21 : 08:45:52

I have one procedure

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create Proc cm_test
(
@claimid char(20) = NULL,
@payfromdt smalldatetime = NULL,
@paytodt smalldatetime = NULL,
@processfromdt smalldatetime = NULL,
@processtodt smalldatetime = NULL

)
As
Begin
declare
@ClaimID char(20),
@EventID numeric(12,0),
@CoinsurancePd money ,
@DeductPd money,
@deductPd money,
@coinsPd money,
@outded money,
@outcoins money,
@precClaimID char(20),
@paymntamt money,
@addtlpayamt money,
@adjamt money,
@balancetransamt money
set nocount on

select @precClaimID ='TEST'
declare @string1 varchar(255),
@string2 varchar(255),
@string3 varchar(255)

SELECT @string1 = 'select ClaimID,EventID from PaymentsDetail'

exec (@string1)

DECLARE PayDedCoins CURSOR FOR

Exec (@string1)

open PayDedCoins
FETCH PayDedCoins INTO
@ClaimID,@EventID

WHILE @@Fetch_Status = 0
BEGIN
exec cm_get_payment_dedcoins @ClaimID,@EventID
end
FETCH PayDedCoins INTO
@ClaimID,@EventID
END

CLOSE PayDedCoins
Deallocate PayDedCoins

return
End

Here Exec (@string1) statement returns 50 ClaimID and EventID .those ClaimID and EventID ,I want to pass to exec cm_get_payment_dedcoins statement which is written in cursor .
Is it possible to write a exec (@string1) in cursore instead of select statement

Please help me to overcome from this problem.

shubhada

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-02-21 : 08:47:47

no it is not.
you do not want that.

Go with the flow & have fun! Else fight the flow

shubhada
Posting Yak Master

117 Posts

Posted - 2006-02-21 : 09:09:43

how i can overcome this problem?
i used this approch because every time my query is changed .

shubhada

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-21 : 09:11:12

You can also consider using xp_execresultset. Note it is undocumented.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39461&SearchTerms=xp_execresultset

----------------------------------
'KH'
It is inevitable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources