You are hereDynamic SQL Cursors


Dynamic SQL Cursors


By hagrin - Posted on 15 May 2007

Today, at work, I was working on creating an application that would perform validated, bulk updates to our accounting system software (Deltek Vision). So, I create a very simple Windows Form, create a real quick GUI, write all the necessary validation and then started writing the stored procedures needed to perform each of the functions. I had already written the SQL syntax as before this application I was manually changing the variables to fit the needs of our business units. However, as I was trying to "generalize" the statements to allow for different types of queries, I started to run into a SQL problem. I was attempting to use dynamic cursors that were being fed by dynamic SQL statements. Yikes.

In English, I had created two cursors - one to read through all the "target" projects and one for all the "source" information. I also gave the user the ability to specify whether or not the target was "equal to", "starts with" or "ends with" a certain criteria. This causes a problem since if you know general cursor syntax you know that a cursor is fed with a select statement as such:

declare wbs_cursor cursor for
select wbs1 as w1 from PR WHERE wbs1 LIKE @targetproject
open wbs_cursor
declare @w1 varchar(30)
fetch next from wbs_cursor into @w1
while @@fetch_status = 0

Now, by giving users the ability to dynamically change the entire WHERE clause (including the operator), you suddenly run into a syntax issue because you can't just store the entire clause in a varchar variable and then have it EXEC SQL (@stmt) because of later references to the cursor. Since that eliminates one major way of preparing dynamic SQL statements, I developed a very dirty solution that revolved around IF statements -

if @targetcondition = 'starts with'
begin
      set @targetproject = @targetproject + '%'
      declare wbs_cursor cursor for
      select wbs1 as w1 from PR WHERE wbs1 LIKE @targetproject
end
if @targetcondition = 'is equal to'
begin
      declare wbs_cursor cursor for
      select wbs1 as w1 from PR WHERE wbs1 = @targetproject
end
if @targetcondition = 'ends with'
begin
      set @targetproject = '%' + @targetproject
      declare wbs_cursor cursor for
      select wbs1 as w1 from PR where wbs1 LIKE @targetproject
end

Pretty dirty right?

Most of the time when I post code here, it's generally the non-dirty version, but I really couldn't find a non-dirty version to this problem and would really like to see if someoone has any ideas on a better, cleaner solution. It seems that all of the sample code I saw online when searching "dynamic cursors" only made table names or WHERE criteria (not the operator) dynamic and therefore fails to appropriately address my issue. I got away with this solution since the problem was simplistic, but what about if I had to deal with 1000 conditions - I wouldn't want to write 1000 IF statements.

Thoughts?

P.S. - As I read through the SQL Books Online, it seems that the PREPARE statement might be able to solve this problem, but I don't have much experience with PREPARE.