sproc using 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 2005 Forums

Transact-SQL (2005)

sproc using cursor

Author

Topic

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2008-01-22 : 14:13:34

hi, this is my store proc...when i am executing it..gives me first select statement not inside the cursor's select statement..so can u tell me cursor is working properly...or i have to use another alternative...can anybody help me to get result?

CREATE PROCEDURE SP_Cpro
AS
BEGIN
SET NOCOUNT ON
DECLARE
@NUMBER CHAR(21),
@CRD CHAR(21),
@NDATE DATETIME,
@BNUMB CHAR(15),
@CUST CHAR(15),
@DDATE DATETIME,
@CNBR CHAR(21),
@CID CHAR(15),
@CNUM CHAR(21),
@CPDT DATETIME,
@PD NUMERIC(19,5),
@OD NUMERIC(19,5),
@OT NUMERIC(19,5),
@ORT NUMERIC(19,5),
@AMNT NUMERIC(19,5)

SELECT * FROM S3
WHERE (DDATE = '01/22/2008')

DECLARE C_CURSOR CURSOR
FOR
SELECT S.NUMBER, S.CRD, S.CNBR, S.NDATE,
SS.BNUMB, SS.CUST
FROM S3 S
LEFT OUTER JOIN S0 SS
ON S.NUMBER = SS.NUMBER
WHERE (S.DDATE = '1/22/2008')

OPEN C_CURSOR

FETCH NEXT FROM C_CURSOR
INTO
@NUMBER, @CRD, @CNBR,
@NDATE, @BNUMB, @CUST

WHILE @@FETCH_STATUS = 0
BEGIN
IF @CUST <> ' '
BEGIN
FETCH NEXT FROM C_CURSOR
INTO
@NUMBER, @CRD, @CNBR,
@NDATE, @BNUMB, @CUST
CONTINUE
END

UPDATE R1
SET
CRD = @CRD,
CNUM = @CNUM,
CPDT = @CPDT
WHERE CUST = @CUST

UPDATE S0
set @PD = 0,
@OD = 0,
@OT = @ORDOCAMT,
@ACCTAMNT = @ORT
WHERE NUMBER = @NUMBER

DELETE S3
WHERE NUMBER = NUMBER

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-01-22 : 16:03:06

Take a little time and look through these posts. Then post back with your attempt at writing this operation without using any cursors. If after reading these you are still hitting the wall post back and we will help.

You will get your answer a lot faster if you can post some sample data and desired results.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72457
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9978

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources