making where clause dynamic

  • Can we make where clause dynamic?

    declare @hourly int

    if @hourly = 1

    --I want the where clause to say

    TableA.LoadDateTime >= @TodaysDate

    if @hourly = 0

    --I want the where clause to say

    convert(varchar(8),TableA.LoadDateTime,112) >= convert(varchar(8), @TodaysDate,112)

    select col1, col2 from TableA

    where TableA.LoadDateTime >= @TodaysDate

  • To get a dynamic where clause you would need to use dynamic sql and execute that sql string.

    Or you could have both queries and an if else statement. Based on the conditions you provide, either one or the other query will be executed.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Will this help...

    DECLARE @Hourly INT

    SELECT @Hourly = 1

    SELECT Col1,Col2 FROM TableA

    WHERE

    (CASE WHEN @Hourly = 1 THEN TableA.LoadDateTime

    ELSE CONVERT(VARCHAR(8),TableA.LoadDateTime,112)

    END) >=

    (CASE WHEN @Hourly = 1 THEN @TodaysDate

    ELSE CONVERT(VARCHAR(8),@TodaysDate,112)

    END)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply