How contact dynamic @where clause in SQL Server query?

Question

I have created a dynamic where clause based on an option selected in SQL Server but when I add this dynamic where string at end of my SQL query, I am getting this error message:

Failed when converting the varchar value 'Where..... to data type integer

This is my query:

lter PROCEDURE [dbo].[GetDailyLogSearchReportLegalNew]  @FilterBy varchar(100),@BranchFilter varchar(100), @Status varchar(100), @dtFrom date, @dtTo date                                    

AS                                 

-- exec GetDailyLogSearchReportLegalnew @FilterBy = 'BRANCH',@BranchFilter = 'CHICAGO',@Status ='B',  @dtFrom = '06-01-2015', @dtTo ='07-24-2015'                           

declare @Query NVARCHAR(Max)      

declare @Where varchar(1000)      

declare @Query1 NVARCHAR(Max)      

declare @Where1 varchar(1000)      

declare @Merge NVARCHAR(Max)      

if(@FilterBy = 'BRANCH' and @BranchFilter = 'ALL')                                                                        

BEGIN       
    if(@Status = 'B')      

    BEGIN      
        set @Where = '  Where     
        LG_BillTo=0   AND  dbo.LegalMain.LG_ReceivedDate  >= '+''''+ convert(varchar(25),Convert(date,@dtFrom)) +' '+'00:00:00.000'' AND  dbo.LegalMain.LG_ReceivedDate  <= '''+ convert(varchar(25),Convert(date,@dtTo)) +' '+'23:59:59.999'''      
    END      

    else if(@Status = 'C')      

    BEGIN      
        set @Where = 'WHERE       
        LG_BillTo=0   AND  dbo.LegalMain.LG_ActualDateCompleted  >= '+''''+ convert(varchar(25),Convert(date,@dtFrom)) +' '+'00:00:00.000'' AND  dbo.LegalMain.LG_ActualDateCompleted  <= '''+ convert(varchar(25),Convert(date,@dtTo)) +' '+'23:59:59.999'''      
    END      

    else if(@Status = 'I')      

    BEGIN      
        set @Where = 'WHERE       
        LG_BillTo=0   AND  dbo.LegalMain.LG_ReceivedDate  >= '+''''+ convert(varchar(25),Convert(date,@dtFrom)) +' '+'00:00:00.000'' AND  dbo.LegalMain.LG_ReceivedDate  <= '''+ convert(varchar(25),Convert(date,@dtTo)) +' '+'23:59:59.999'' And dbo.LegalMain.LG_ActualDateCompleted is null'      
    END       
END      


SELECT LEFT(LG_CreationTime,5) as 'Status', dbo.LegalMain.LG_LegalID AS ID,

    dbo.LegalMain.LG_ReceivedDate AS 'Received Date', Forwarder.CST_ShortName AS 'Customer', 

    dbo.City.CTY_CityName AS 'City', Shipper.CST_FullName AS 'Shipper',  

    dbo.LegalMain.LG_ReferenceNo AS 'Ref No',  dbo.Country.CNT_CountryName AS 'Country',

    dbo.LegalBranch.BR_BranchName AS Branch, ST_ServiceType AS 'Service Type',  

    dbo.LegalMain.LG_ActualDateCompleted  AS NewCompleted,   

    SN_SentTo as 'Sent To',LG_COPREP as CO, LG_InoviceNo AS 'Invoice No'    

FROM dbo.LegalBranch 

    RIGHT OUTER JOIN dbo.LegalMain ON dbo.LegalBranch.BR_ID = dbo.LegalMain.LG_BranchId 

    LEFT OUTER JOIN dbo.Country ON dbo.LegalMain.LG_CountryID = dbo.Country.CNT_CountryID 

    LEFT OUTER JOIN dbo.Customer AS Forwarder 

    LEFT OUTER JOIN dbo.City ON Forwarder.CST_CityID = dbo.City.CTY_CityID ON dbo.LegalMain.LG_ForwarderID = Forwarder.CST_CustomerID  

    LEFT OUTER JOIN dbo.LegalServiceType ON dbo.LegalMain.LG_ServiceTypeId = dbo.LegalServiceType.ST_ServiceTypeID  

    LEFT OUTER JOIN dbo.LegalSentTo ON dbo.LegalMain.LG_SentToId = dbo.LegalSentTo.SN_ID   

    LEFT OUTER JOIN dbo.Customer AS Shipper ON dbo.LegalMain.LG_ShipperID = Shipper.CST_CustomerID  & ' ' & cast(@Where as varchar(1000))

Thanks.

Whether columns are generated dynamically or Values to columns are passed dynamically — Pரதீப், Jul 29 '15 at 11:34
Why do you think you need a "dynamic where"? Dynamic SQL is a *very* strong smell and leads to performance and security problems for no gain. If you want to create queries with different criteria, do this on the client eg by using an ORM or parameterized queries — Panagiotis Kanavos, Jul 29 '15 at 11:56
The code is very hard to read but contains many problems, eg converting dates to text to compare with a date column ? String concatenation just makes it impossible to detect conversion or quoting errors. At the very least, create a *view* instead of joining all those tables in your statement. This will remove at least 11 lines — Panagiotis Kanavos, Jul 29 '15 at 12:00
The error is in last line & ' ' & cast(@Where as varchar(1000)) can't we fix it — Jim, Jul 29 '15 at 12:01
No you can't. You can't use a text variable as if it were a statement. — Panagiotis Kanavos, Jul 29 '15 at 12:04
BTW you are using non-standard text as date parameters. Your `where` statement will probably return the wrong results. Use date-typed parameters and parameterized queries instead — Panagiotis Kanavos, Jul 29 '15 at 12:07

score 2 · Answer 1 · edited May 23 '17 at 12:14

The issue is you're mixing code and content; variables containing code aren't converted to something executable automatically; the system doesn't understand your intention.

Instead, create the complete code to be run in a variable, then tell SQL to execute the code contained in that variable.

Declare @myWhere nvarchar(max)
, @mySql nvarchar(max)
--for the sake of illistration
set @myWhere = 'where ID < 200'

set @mySql = 'Select ID from myTable ' + @myWhere
exec sp_executesql @mySql
--or exec(@mySql)

See also:

A better illustration of the issue:

Declare @sql nvarchar(max) = ' * from myTable'
select @sql

What would you expect the output of the above to be?

Runnable version here: http://sqlfiddle.com/#!6/318db

UPDATE

Based on your revised question / code, the following will likely do what you're after without requiring dynamic SQL.

SELECT LEFT(LG_CreationTime,5) as 'Status'
, dbo.LegalMain.LG_LegalID AS ID
, dbo.LegalMain.LG_ReceivedDate AS 'Received Date'
, Forwarder.CST_ShortName AS 'Customer'
, dbo.City.CTY_CityName AS 'City'
, Shipper.CST_FullName AS 'Shipper'
, dbo.LegalMain.LG_ReferenceNo AS 'Ref No'
, dbo.Country.CNT_CountryName AS 'Country'
, dbo.LegalBranch.BR_BranchName AS Branch
, ST_ServiceType AS 'Service Type'
, dbo.LegalMain.LG_ActualDateCompleted  AS NewCompleted
, SN_SentTo as 'Sent To'
, LG_COPREP as CO
, LG_InoviceNo AS 'Invoice No'    
FROM dbo.LegalMain 
LEFT OUTER JOIN dbo.LegalBranch  
    ON dbo.LegalBranch.BR_ID = dbo.LegalMain.LG_BranchId 
LEFT OUTER JOIN dbo.Country 
    ON dbo.Country.CNT_CountryID = dbo.LegalMain.LG_CountryID
LEFT OUTER JOIN dbo.Customer AS Forwarder 
    ON Forwarder.CST_CustomerID = dbo.LegalMain.LG_ForwarderID 
LEFT OUTER JOIN dbo.City 
    ON dbo.City.CTY_CityID = Forwarder.CST_CityID 
LEFT OUTER JOIN dbo.LegalServiceType 
    ON dbo.LegalServiceType.ST_ServiceTypeID = dbo.LegalMain.LG_ServiceTypeId 
LEFT OUTER JOIN dbo.LegalSentTo 
    ON dbo.LegalSentTo.SN_ID = dbo.LegalMain.LG_SentToId 
LEFT OUTER JOIN dbo.Customer AS Shipper 
    ON Shipper.CST_CustomerID = dbo.LegalMain.LG_ShipperID
WHERE (NOT (@FilterBy = 'BRANCH' and @BranchFilter = 'ALL'))
OR
(
    LG_BillTo = 0
    AND
    (
        (
            @Status = 'B'
            AND dbo.LegalMain.LG_ReceivedDate between @dtFrom and DATEADD(second,-1,DATEADD(day,1,@dtTo))  
        )
        OR
        (
            @Status = 'C'
            AND dbo.LegalMain.LG_ActualDateCompleted between @dtFrom and DATEADD(second,-1,DATEADD(day,1,@dtTo))     
        )
        OR
        (
            @Status = 'I'
            AND dbo.LegalMain.LG_ReceivedDate between @dtFrom and DATEADD(second,-1,DATEADD(day,1,@dtTo)) 
            And dbo.LegalMain.LG_ActualDateCompleted is null  
        )
    )
)

NB: this code is untested and will likely perform badly as there are a lot of joins / conditions. I don't recommend using this code in a production environment, but rather getting a SQL developer to assist by specifying your functional requirements and providing them with a copy of the database against which this is to be run.

I just need a simple select with where clause in my query don't want to use exec of sp_executesql — Jim, Jul 29 '15 at 11:56
@Jim you *have* to use either `exec` or far better, `sp_executesql`. Better yet, just don't do this. An ORM with LINQ is a better option for creating queries with changing criteria — Panagiotis Kanavos, Jul 29 '15 at 11:57
The error is in last line & ' ' & cast(@Where as varchar(1000)) can't we fix it — Jim, Jul 29 '15 at 12:00
@Jim why do you think so? Most likely the @Where statement is invalid. Anyway, you simply *can't* use a variable instead of a WHERE statement. That's not how SQL works. You have to create a string and execute it either with `exec` or `sp_executesql` — Panagiotis Kanavos, Jul 29 '15 at 12:04
@Jim: please take a look at http://sqlfiddle.com/#!6/318db. Does it make sense to you why that gives the output it does / not the output some may expect? That's the same reason putting `@MyWhere` on the end of a non-dynamic query doesn't work... If on the other hand you want a dynamic query, that's what I've done in my example above (i.e. you have to use `sp_executesql` / `exec(...)`). As @PanagiotisKanavos points out though, generally dynamic SQL is a bad idea / there are better ways to solve such issues. — JohnLBevan, Jul 29 '15 at 12:25
I have no issues when I use exec my purpose is to use where as variable run simple sql without exec — Jim, Jul 29 '15 at 12:35

How contact dynamic @where clause in SQL Server query?

1 Answers1