Post reply

An expression of non-boolean type specified in a context where a condition is expected

  • August 13, 2009 at 1:17 pm

    #95755

    Hi,

    I am trying to make a query dynamic in a way that computing the where condition based on the parameters and building condition as a string @strCondition.

    Here is the example code...

    DEClare @strCondition VARCHAR(3000)

    Set @strCondition = 'TB.SEx ='+' M'+''

    SELECT TOP 100 * FROM tableA AS TB WHERE @a

    When running this, getting error

    Msg 4145, Level 15, State 1, Line 4

    An expression of non-boolean type specified in a context where a condition is expected, near '@a'

    Can anybody help ?

  • August 14, 2009 at 2:28 am

    #1039201

    If you want to use variables that hold some part of the where clause you need to use dynamic queries.

    "Keep Trying"

  • August 14, 2009 at 7:28 am

    #1039313

    This is just to add a bit on Chraig’s answer. Using dynamic SQL has its own price in terms of security and query plan usage. I strongly advice you to read Erland Sommaskog’s whit paper “The Curse and Blessings of Dynamic SQL” at http://www.sommarskog.se/dynamic_sql.html%5B/url%5D

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • August 18, 2009 at 3:33 pm

    #1041198

    Thanks a lot

  • August 19, 2009 at 12:22 pm

    #1041778

    I'm not sure the poster actually needs dynamic SQL. If done the way his code suggested, then yes, dynamic SQL would be required, because SQL doesn't allow you to cobble together textual pieces of the query with variables, directly. You can, however, create variables, and assign parameter values to those variables, and have a query that goes along the lines of:

    SELECT A, B, C

    FROM dbo.MYDATA

    WHERE X = @XDATA

    AND Y = @YDATA

    I'm assuming an appropriate DECLARE statement for each of the variables along with an assignment of a value, which all take place before the query.

    The dynamic SQL solution, on the other hand, is more like this:

    DECLARE @SQL1 AS varchar(MAX), @SQL2 AS varchar(MAX)

    SET @SQL1 = 'SELECT A, B, C

    FROM dbo.MYDATA

    WHERE '

    SET @SQL2 = 'GENDER = ''M'''

    EXEC (@SQL1 + @SQL2)

    Steve

    (aka smunson)

    :-):-):-)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • January 4, 2012 at 9:17 am

    #1428416

    Thanks very much... your reply helped me solve a major issue.

  • January 4, 2012 at 5:17 pm

    #1428760

    Thanks for the reply... It continues to amaze me how often something done years ago helps someone currently...

    Glad you were able to make use of it.

    Steve

    (aka sgmunson)

    :-):-):-)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • January 5, 2012 at 10:55 am

    #1429213

    Dynamic SQL works fine when the passed WHERE type parameter is for something you can't determine ahead of time. If the passed parameter is always for the same column in the table and you may get more than 1 parameter, take a look at this technique:

    http://www.sqlservercentral.com/articles/T-SQL/73838/

    Todd Fifield

Viewing 8 posts - 1 through 7 (of 7 total)

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