Post reply

where clause in dynamic query

  • July 2, 2015 at 1:22 am

    #299716

    I am creating a dynamic query where i am appending a where clause something like -

    IF (@CurCaptureDate IS NOT NULL)

    SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + @CurCaptureDate

    PS - CD.CaptureDate is datetime and @CurCaptureDate is also datetime

    but when executing , it gives this error - Msg 241, Level 16, State 1, Line 169

    Conversion failed when converting date and/or time from character string.

    i am not able to use convert here with these quotes. please help

    i tried this - SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + CONVERT(VARCHAR(25),@CurCaptureDate )

    but it makes it to -

    AND CD.CaptureDate = Jul 19 2014 12:00AM. I would need the date in quotes.

    Can you please help.

  • July 2, 2015 at 1:53 am

    #1809733

    Please take a look at

    https://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/.

    See if it helps.

  • July 2, 2015 at 2:17 am

    #1809740

    DECLARE @where_clause VARCHAR(100);

    DECLARE @CurCaptureDate DATETIME;

    SET @CurCaptureDate = GETDATE();

    SET @where_clause = '';

    SET @where_clause = @where_clause + CHAR(10) + ' AND CD.CaptureDate = ''' + CONVERT(VARCHAR(25),@CurCaptureDate ) +''''

    SELECT @where_clause

    Look at the number of single quotes used in the query. It gets 'interesting' with dynamic sql.

    There's also an article on this site regarding this - http://www.sqlservercentral.com/articles/T-SQL/95670/

    Steve Hall
    Linkedin
    Blog Site

  • July 2, 2015 at 3:39 am

    #1809770

    Thanks.. I am done with this 🙂 thanks for taking out time...

    Can you please tell me if we can use table variable in join condition in dynamic query or should temp tables be created for it

  • July 2, 2015 at 7:50 am

    #1809886

    try not to hard code your values in the string, else if the code runs with different parameter values, there will be zero plan reuse, unless forced parameterization is enabled.

    Use something like

    EXEC sp_executesql @sql_statement, @Parameters, @parameter1,

    @parameter2

    --Instead of

    exec(@sql_statement)

    I only mention this because it looks like you are trying to successfully embed the scalar value in the dsql.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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