January 5, 2006 at 11:52 am
I am trying to work with our developers to remove the use of cursors from their code and the stored procedures that they write. I would like some feedback on this scenario and my proposed solution. We have a cursor that is set up to pass column values into another stored procedure that performs validation on those values and returns a bit value to represent valid or invalid. Outside of the cursor, that return value (now in a local variable) is used as a condition for an update statement. I propose that we take the logic from the validation stored procedure and place it into a scalar function. We then, instead of using the cursor, use the function within the UPDATE statement to update our table.
********************************************************
Existing Cursor logic:
1. Declare and open cursor, selecting valueA, valueB, and valueC into local variables.
2. Pass @valueA, @valueB, and @valueC into stored procedure for validation. Stored procedure's output will update a local variable @IsValid.
3. end cursor
4. Conditional update....If @IsValid = 1 (valid) UPDATE table SET field = field value.
********************************************************
My Proposed solution:
1. Move validate logic into a function.
2. Instead of cursor, use function in UPDATE statement as such UPDATE table SET fieldname = fieldvalue WHERE fn_Validate(@valueA,@valueB,@valueC) = 1
********************************************************
Suggestions??
January 5, 2006 at 12:07 pm
What happens inside fn_Validate() ?
Does it just use the passed in values, or does it perform additional querying on other tables ? If there is additional querying, you will still have performance issues, because it's still essentially a cursor like operation.
January 5, 2006 at 12:13 pm
There is a small handful select statements (using index seeks) to pull values used in the validation process. Min length, Max length, invalid characters, among others, are stored in a table. These values are stored in local variables and used throughout the validation.
What would you suggest then, getting the validation criteria upfront and passing all 12 into the function as parameters?
January 5, 2006 at 2:23 pm
>>getting the validation criteria upfront and passing all 12 into the function as parameters?
Yep, exactly.
January 5, 2006 at 2:59 pm
Those 12 parameters are meant to validate 4 separate values. As it turns out, the validation routine had some redundant code in it that allowed the developer to pear it down into one generic routine. The function now only accepts the value to validate, and the 3 corresponding criteria that will be used to validate it.
Thanks for your input.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply