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

Question

I have searched through SO, but not found anything to help resolve my issue. I am not sure how to interpret the error message with regards to my query.

My query is:

select AST_ID,
    case  when CRA_StatusID=1 then 'Wait on Info'
    when CRA_StatusID=2 then 'Wait PrePay'
    when CRA_StatusID=3 then 'Acquire Chart'
    when CRA_StatusID=4 then 'Copy Chart'
    when CRA_StatusID=5 then 'Need Invoice'
    when CRA_StatusID=6 then 'Wait Payment'
    when CRA_StatusID=7 then 'Ready for Delv' else 'Complete' end as AST_Status,
    case when AST_WOPrinted is null then '' else 'Y' end as AST_WOPrinted,
    case when AST_DeliveryDate is null then '' else 'Y' end as AST_Delivered,
    AST_PatientLName+'', ''+AST_PatientFName+' '+AST_PatientMName as PatientName,
    case when len(AST_RequestorName) > 0 then AST_RequestorName else AST_RequestorContact end as AST_RequestorName,
    AST_Created,AST_ProviderName
    from dbo.AST
    inner join dbo.fnASTCurrentStatus() on AST_ID=CRA_ASTID
    where ' + @WhereClause + '

    union all
    select AST_ID,
    case 
    when CRA_StatusID=1 then 'Wait on Info'
    when CRA_StatusID=2 then 'Wait PrePmt'
    when CRA_StatusID=3 then 'Aquire Chart'
    when CRA_StatusID=4 then 'Copy Chart'
    when CRA_StatusID=5 then 'Need Invc'
    when CRA_StatusID=6 then 'Wait Pmt'
    when CRA_StatusID=7 then 'Ready for Delv' else 'Complete'
    end as AST_Status,
    case when AST_WOPrinted is null then '' else 'Y' end as AST_WOPrinted,
    case when AST_DeliveryDate is null then '' else 'Y' end as AST_Delivered,
    AST_PatientLName+'', ''+AST_PatientFName+' '+AST_PatientMName as PatientName,
    case when len(AST_RequestorName) > 0 then AST_RequestorName else AST_RequestorContact end as AST_RequestorName,
    AST_Created,AST_ProviderName
    from dbo.Archive_AST 
    inner join dbo.fnArchiveASTCurrentStatus() on AST_ID=CRA_ASTID
    where ' + @WhereClause + '  



set @WhereClause=' AST_ProviderID in (select ProviderID from dbo.UserProvider where CSA_UserID = ' + convert(varchar,55) + ')'

error message reads:

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

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

How can I resolve the error?

` where ' + @WhereClause + ' ` is not a valid where clause..? — thebjorn
the @whereClause is defined separately within the stored procedure. — user3929962
what is the value of @where? just an example so we can understand what it looks like. But yes thebjorn is correct your where clause is invalid — SQLChao
Does that where change all the time or is it only the user ID that changes? You are doing dynamic sql but not correctly. You could change the whole query to a string to concatenate @whereclause and then exec the whole string. But maybe dynamic sql isn't what you are really trying to accomplish. — SQLChao

MaxiWheat MaxiWheat · Accepted Answer · 2014-09-23T20:03:52

You cannot do that this way. You are mixing a plain SQL Query with dynamic SQL. You must choose if you use or not dynamic SQL. If you do so, check the EXECUTE keyword or sp_executesql system Stored Procedure.

If it's dynamic SQL, your where clause variable should happen before building the query.

You query can easily be written like this :

Keep the whole query up to the WHERE clause and

...
WHERE AST_ProviderID in (select ProviderID from dbo.UserProvider where CSA_UserID = convert(varchar,55))

If you want, you can also use a variable or a parametrized query :

DECLARE @myvar INT = 55;
...
WHERE AST_ProviderID in (select ProviderID from dbo.UserProvider where CSA_UserID = convert(varchar, @myvar))

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

2 Answers