Need help in Avoiding 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)

Need help in Avoiding Cursor

Author

Topic

red108
Starting Member

6 Posts

Posted - 2009-08-12 : 02:30:53

I have the following Stored Procedure where
im executing a stored procedure for each and every value in ERROR_CODE
For this i have used Cursor ,when the data is huge im getting timed out error

Is there any way i can avoid Cursor and achieve the follwing in any other way

Follwing is my Stored Procedure

ALTER PROCEDURE dbo.[USP_RunAllErrors]
@iCId SMALLINT

AS
DECLARE @ErrMsg NVARCHAR(500)
DECLARE @v_trans_started INT
DECLARE @ErrCode VARCHAR(5)
BEGIN
BEGIN TRY
BEGIN TRANSACTION

--===================================================================================================
DECLARE curErrors CURSOR STATIC FOR
SELECT ERROR_CODE FROM dbo.Error_codes where CId=@iCId
OPEN curErrors
FETCH NEXT FROM curErrors INTO @ErrCode
WHILE @@FETCH_STATUS=0
BEGIN
If @ErrCode='D1'

EXEC [USP_RunD1] @iCId

else if @ErrCode='D2'

EXEC [USP_RunD2] @iCId

else if @ErrCode='D3'

EXEC [USP_RunD3] @iCId

else if @ErrCode='D4'
EXEC [USP_RunD4] @iCId

else if @ErrCode='D5'

EXEC [USP_RunD5] @iCId

else if @ErrCode='D6'

EXEC [USP_RunD6] @iCId

else if @ErrCode='D7'

EXEC [USP_RunD7] @iCId

else if @ErrCode='D8'

EXEC [USP_RunD8] @iCId

else if @ErrCode='D9'

EXEC [USP_RunD9] @iCId

else if @ErrCode='D10'

EXEC [USP_RunD10] @iCId

else if @ErrCode='D11'

EXEC [USP_RunD11] @iCId

FETCH NEXT FROM curErrors INTO @ErrCode
END
CLOSE curErrors
DEALLOCATE curErrors
--===================================================================================================
COMMIT TRANSACTION
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION
SELECT @ErrMsg=ERROR_MESSAGE()
RAISERROR(@ErrMsg,16,1)
END CATCH
END

red108
Starting Member

6 Posts

Posted - 2009-08-13 : 09:49:28

I appreciate if any help is provided

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-08-13 : 09:53:51

How about posting the structure of your tables, the code for at least 1 of the procedures you call, and a description of what these procedures (are supposed to) do? There's no way to tell if it can be written without a cursor otherwise.

red108
Starting Member

6 Posts

Posted - 2009-08-13 : 10:16:23

Thanks for the reply

ERROR_CODE contains values like D1,D2,D3,D4,D5,D6,D7,D8,D9

all these are business rules ,in the front end application user got a
choice to enable or disable these rules while running the errors,
so what iam doing is im passing the users cID and getting rules(D1,D2,D3 ...) that were enabled to that user and based on that im executing the stored procedures for the enabled rules.
Im not posting the procedure code for these rules becos it deals with
75% of my data model and it will create confusion.

Please let me know if you need any further information

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-13 : 10:21:10

Somewhat smaller code.
But since you are running this from client side, you probably have a command timeout value in your connection string, which defaults to 30 second.
Increase that value to 300 seconds, for example.

ALTER PROCEDURE dbo.USP_RunAllErrors
(
	@iCID SMALLINT
)
AS 

SET NOCOUNT ON

DECLARE @ErrCode VARCHAR(5),
	@Cmd VARCHAR(200)

DECLARE	curErrors CURSOR STATIC FOR
		SELECT	ERROR_CODE
		FROM	dbo.Error_codes
		where	CID = @iCID

OPEN	curErrors

FETCH	NEXT
FROM	curErrors
INTO	@ErrCode 

WHILE @@FETCH_STATUS = 0
BEGIN
	SET	@Cmd = 'USP_Run' + @ErrCode + ' ' + STR(@iCID, 5)

	EXEC	@Cmd

	FETCH	NEXT
	FROM	curErrors
	INTO	@ErrCode 
END

CLOSE		curErrors
DEALLOCATE	curErrors

N 56°04'39.26"
E 12°55'05.63"

red108
Starting Member

6 Posts

Posted - 2009-08-13 : 10:33:06

Thanks for the reply Peter, it sounds good and i can avoid nested If's , im just curious to know whether i can do it in any other way

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-13 : 10:35:34

It depends on what the stored procedures actually does.

N 56°04'39.26"
E 12°55'05.63"

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-13 : 10:35:42

You could change the IF..ELSE statements into a CASE statement to improve clarity.

This cursor is unlikely to cause time-out issues.
It's the called stored procedures USP_RunDnn that are causing the time-outs.

What is your connection/command time-out setting?
Which stored procedure is timing-out? USP_RunD1, USP_RunD2..
Can you optimize these procedures?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-13 : 10:57:50

quote:
Originally posted by YellowBug

You could change the IF..ELSE statements into a CASE statement to improve clarity.

IF.. ELSE is a totally different structure than CASE in sql. IF..Else is flow control. CASE is run time data substitution.

quote:

This cursor is unlikely to cause time-out issues.
It's the called stored procedures USP_RunDnn that are causing the time-outs.

What is your connection/command time-out setting?
Which stored procedure is timing-out? USP_RunD1, USP_RunD2..
Can you optimize these procedures?

The best way to optimise those procedures (depending on what they do) would be to rewrite them in a set based way so that the cursor becomes redundant

red108 -- it looks like you actually want to pass an array to the stored proc?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources