Conditional dynamic SQL with cursor

Question

Hello,

I have a query which uses a cursor to cycle through the results of a select statement.

The select statement in short selects all of the records from a mapping table I have. One of the columns is 'SourceTableName'.

I use this field to generate some dynamic SQL.

I am looking to add a parameter to my stored procedure wrapped around this, which will allow me to only create dynamic SQL for the 'SourceTableName' that I want - IF I pass in a 'SourceTableNameFilter'.

I am stuck with some logic which wraps my dynamic SQL.

IF @SourceTableNameFilter(SP parameter) = @SourceTableName(from mapping table) BEGIN

Generate and execute some dynamic SQL based on the SourceTableName.

The problem is, I want this to either work on all tables that come back from a select against 'SourceTableName' BUT if a @SourceTableNameFilter parameter is present and not null - then only generate dynamic SQL for any rows in the cursor which match my filter parameter.

Is there a way for me to accomplish this with an IF statement without copying the logic inside the IF/ELSE twice?

 FETCH NEXT FROM TABLECUR INTO @SourceTableName
     ,@SourceInColumn
     ,@SourceOutColumn
     ,@TargetTableName
     ,@TargetLookupColumn
     ,@TargetLookupResultColumn
     ,@MappingTableID

 WHILE (@@fetch_status <> -1)
 BEGIN
  IF (@@fetch_status <> -2)
  BEGIN


                IF (@SourceTableName = @SourceTableNameFilter)

                 --GENERATE DYNAMIC SQL

                ELSE 
                 --GENERATE DYNAMIC SQL FOR ALL RECORDS

The generate dynamic SQL string is the same in both the if and the else, any way to change the conditions so that I'm not duplicating the dynamic SQL generation and to not generate dynamic SQL when the @SourceTableName != @SourceTableNameFilter?

Thank you

Answer 1

Knowing nothing about the dynamic sql you're building, I'd recommend doing something along the lines of:

SET @DynamicCommand = '<whatever, first part>'
 + isnull(@SourceTableNameFilter
          ,'<no special action, perhaps just empty string>'
          ,'<add conditional text dependent upon contents of @SourceTableNameFilter>')
 + '<whatever, second part>'

Answer 2

Consider adding this logic to the cursor definition, rather than having that logic within the processing of each cursor record.

So if the cursor is normally:

DECLARE MY_CURSOR Cursor     FOR
SELECT SourceTableName, SourceInColumn, SourceOutColumn
       ,TargetTableName, TargetLookupColumn
       ,TargetLookupResultColumn, MappingTableID

FROM MappingTable
--get source tables when filter is specified; otherwise get all
WHERE (SourceTableName = @SourceTableNameFilter) OR (LEN(ISNULL(SourceTableNameFilter,'')=0)

Now you can execute your business logic within the cursor without having to detect the filtered table or not. The cursor is loaded with the records you need to care about. It sounds, from the question, that the business logic is the same, no matter if the filter was passed in or not. If this is incorrect, or if it doesn't satisfy your requirement, please comment.