SQL, how to use Dynamic Condition logics?

Question

When you need Dynamic WHERE Clause I can use;

CREATE PROCEDURE [dbo].[sp_sel_Articles]
      @articleId            INT              = NULL
    , @title            NVARCHAR(250)    = NULL
    , @accessLevelId    INT              = NULL
AS
BEGIN
     SELECT *
     FROM table_Articles Art
     WHERE
          (Art.ArticleId = @articleId OR @articleId IS NULL)
          AND (Art.Title LIKE '%' + @title + '%' OR @title IS NULL)
          AND (Art.AccessLevelId = @accessLevelId OR @accessLevelId IS NULL)
END

So, I am able to invoke this procedure -for example- ONLY by ArticleId

EXEC [sp_sel_Articles] @articleId = 3

But, sometimes I'll need to invoke by AccessLevelId and sometimes NOT by an EXACT VALUE. For example, I'll need MORE THAN the given accesslevelId or LESS THAN.

Current procedure can ONLY handle the EXACT value by using

Art.AccessLevelId = @accessLevelId

Could also be possible to give the CONDITION type as well as the value into the procedure? It may seem very odd in this example but please just bear with me:

CREATE PROCEDURE [dbo].[sp_sel_Articles]
          @articleId            INT              = NULL
        , @title            NVARCHAR(250)    = NULL
        , @accessLevelId    INT              = NULL
        , **@accessLevelIdCondition**
    AS
    BEGIN
         SELECT *
         FROM table_Articles Art
         WHERE
              (Art.ArticleId = @articleId OR @articleId IS NULL)
              AND (Art.Title LIKE '%' + @title + '%' OR @title IS NULL)
              AND (Art.AccessLevelId **@accessLevelIdCondition** @accessLevelId OR @accessLevelId IS NULL)
    END

Perhaps an Function can be used, I don't know. Since, there will be at least 20 Procedure that will require this flexibility, I'll need a better, more global solution as much as possible rather than writing IF ELSE condition in every procedure.

Thanks in advance,

Answer 1

Read this www.sommarskog.se/dynamic_sql.html before applying

  CREATE PROCEDURE [dbo].[sp_sel_Articles] 
              @articleId            INT              = NULL 
            , @title            NVARCHAR(250)    = NULL 
            , @accessLevelId    INT              = NULL 
            , @accessLevelIdCondition varchar(100)
        AS 
        BEGIN 
                DECLARE @SQL varchar(8000)
    SET @SQL='
         SELECT * 
         FROM table_Articles Art 
         WHERE 
              (Art.ArticleId = '+cast(@articleId as varchar(100))+' OR '+cast(@articleId as varchar(100))+'IS NULL) 
              AND (Art.Title LIKE ''%'' + @title + ''%'' OR @title IS NULL) 
              AND (Art.AccessLevelId '+@accessLevelIdCondition+ cast(@accessLevelId as varchar(100))+' OR '+cast(@accessLevelId as varchar(100))+' IS NULL) '
    EXEC(@sql)
        END

Answer 2

You'd probably need to use dynamic SQL to pass in the operator. Or you could pass in two values, e.g.

@MinAccessLevelID INT,
@MaxAccessLevelID INT

...
WHERE (
   (@MinAccessLevelID IS NULL AND @MaxAccessLevelID IS NULL)
   OR 
   (AccessLevelID >= @MinAccessLevelID AND AccessLevelID <= @MaxAccessLevelID)
)

When you want exact (e.g. only 3), just pass 3 into both values. When you want anything above 3, pass 20000000000 into the @Max param, or 0 if you want everything below 3.

But you'll find as these permutations get more complex, you are going to be better off just using dynamic SQL (and with optimize for ad hoc workloads set, this will be better for plan cache reuse and thwarting parameter sniffing as well).

Answer 3

You can always make a dynamic query with just making a querystring

execute ('select count(*) from table' ) 

So with the params entered in your stored procedure, you can also form up a querystring which you can execute.

Answer 4

You could use a case statement - it can look a little funny if not formatted correctly but you can try something like:

SELECT Columns FROM SomeTable
WHERE 1 = CASE
              WHEN @SomeOption = '<>' AND SomeValue >= @SomeMinParam AND SomeValue <= SomeMaxParam THEN 1
              WHEN @SomeOption '=' AND SomeValue = @SomeMinParam THEN 1
              ELSE 0
          END     

(though as Aaron pointed out - the <> you pass in doesn't really reflect the comparison operators in the statement - change this to something meaningful :))

in your case:

CREATE PROCEDURE [dbo].[sp_sel_Articles]
    @articleId            INT              = NULL,
    @title            NVARCHAR(250)    = NULL,
    @MinaccessLevelId    INT              = NULL,
    @MaxaccessLevelId     INT          = NULL,
    @accessType       varchar(5)       = '<>'
AS
BEGIN
 SELECT *
 FROM table_Articles Art
 WHERE
      (Art.ArticleId = @articleId OR @articleId IS NULL)
      AND (Art.Title LIKE '%' + @title + '%' OR @title IS NULL)
      AND 1 = CASE 
                  WHEN @accessType = '<>' AND (Art.AccessLevelId = @MinaccessLevelId OR @accessLevelId IS NULL) THEN 1
                  WHEN @accessType = '=' AND (Art.AccessLevelId >= @MinaccessLevelId OR Art.AccessLevelId <= @MaxaccessLevelId) THEN 1
                  ELSE 0
              END
END

Maybe use a bit @CompareAccessLevelToMin instead of a varchar() for the @accessType param. Still has the trouble of not telling you what setting it to 'false' means though.