SQL Using User Defind Function to dynamically build "Between" statement for where clause

Basically I want to use a return value from a UDF (a string that builds a BETWEEN clause) in a SQL statement.  The details are below:

I have a SP that is dynamically building a BETWEEN for a where clause.  The value of this SP is being stored in a table that I created a UDF to retrieve.  The Return of the UDF can look like this (it can be just one set of Between or have the OR and have a second set, thus the need for the SP):
(ROWID BETWEEN 40001 AND 80000) OR (ROWID BETWEEN 0 AND 30654)

I am dynamically building a SQL statement in an SSIS script task that I want to use the above BETWEEN statement in something like this:

      Select * From TableName
      Where (
            (select dbo.FUN_QueryGenerationGetBetweenValues('Paramater1'))            
      )


Where this lines return value:
 (select dbo.FUN_QueryGenerationGetBetweenValues('Paramater1')) would be:
(ROWID BETWEEN 40001 AND 80000) OR (ROWID BETWEEN 0 AND 30654)

And I want to find a way to make this work.  I am doing something similar using a UDF in the between clause like this:

WHERE ACCTNUM Between
                      (select dbo.FUN_GetMinMaxAcctNum(Paramaters'))
                      and
                  (select dbo.FUN_GetMinMaxAcctNum(Paramaters'))
                )

This works like this in the above but I need to find a way to get the above to work.

I know of a lot of other ways to do it (set the return value to a variable and dynamically build the SQL statement using the variable) but with the existing process I am using in the SSIS package and the way I am running the SSIS and the way I need to generate the BETWEEN statement any other way I can think of would be extremely complicated and really mess up the existing process.

So if I can somehow get this to work the way I have described above that would be great.
Start Free Trial