Can't get variable in where clause to work with my query

Question

I'm trying to use a variable in my where clause, but cant get it to work. When I fill in the actual value from the variable, it works.

I'm defining the variable by:

DECLARE @SalesorderTracking nvarchar(MAX)
SET @SalesorderTracking = ''; select @SalesorderTracking = CAST('''' + c_loadcarrier as Nvarchar(max)) + '''' + ','  + @SalesorderTracking from  [ApportMM].[dbo].[#SalesorderTracking]
SET @SalesorderTracking = LEFT(@SalesorderTracking, LEN(@SalesorderTracking) -1)
PRINT @SalesorderTracking

The print command returns this value: 'PL10275703','PL10275700','PL10269031','PL10269030','PL10271642'

Running the following query returns no records.

SELECT  [MyDB].[dbo].[t_Item_log].[c_id]
        ,[MyDB].[dbo].[t_Item_log].[c_Info]
        ,CASE [c_TypeRef]
            WHEN '1' THEN 'Loadcarrier From'
            WHEN '2' THEN 'Loadcarrier To'
        END AS [Type]
        ,[c_loadcarrier]
        ,[c_WorkerName]
        ,[c_Material]
        ,[c_Qty]
        ,[c_Created]     
        ,[c_ShelfLabelFrom]
        ,[c_WarehouseFrom]
        ,[c_WareHouseAreaFrom]
        ,[c_ZoneFrom]
        ,[c_TransactionId]
        ,[c_ItemRef]
        FROM dbo.t_Item_log INNER JOIN dbo.t_Item_log_LoadCarrier ON dbo.t_Item_log.c_id = dbo.t_Item_log_LoadCarrier.c_Item_LogRef
        where c_loadcarrier in (@SalesorderTracking) AND (c_TypeRef = '1' OR c_TypeRef = '2')
        order by [c_loadcarrier] desc, [Type] desc

But running the following query, where I have changed the @SalesorderTracking variable with the actual value returns the expected records.

SELECT  [MyDB].[dbo].[t_Item_log].[c_id]
        ,[MyDB].[dbo].[t_Item_log].[c_Info]
        ,CASE [c_TypeRef]
            WHEN '1' THEN 'Loadcarrier From'
            WHEN '2' THEN 'Loadcarrier To'
        END AS [Type]
        ,[c_loadcarrier]
        ,[c_WorkerName]
        ,[c_Material]
        ,[c_Qty]
        ,[c_Created]     
        ,[c_ShelfLabelFrom]
        ,[c_WarehouseFrom]
        ,[c_WareHouseAreaFrom]
        ,[c_ZoneFrom]
        ,[c_TransactionId]
        ,[c_ItemRef]
        FROM dbo.t_Item_log INNER JOIN dbo.t_Item_log_LoadCarrier ON dbo.t_Item_log.c_id = dbo.t_Item_log_LoadCarrier.c_Item_LogRef
        where c_loadcarrier in ('PL10275703','PL10275700','PL10269031','PL10269030','PL10271642')  AND (c_TypeRef = '1' OR c_TypeRef = '2')
        order by [c_loadcarrier] desc, [Type] desc

I really can't figure out what I'm doing wrong with that variable.

Any help and suggestions appreciated, thanks.

What's your SQL Server version? Version 14.0

A scalar variable is just that, a **scalar** value. You seem to be under the impression that if a variable has the value `'abc,def'`, that it's treated as *2* values; this is not true. `c_loadcarrier in (@SalesorderTracking)` would be a synonym of `c_loadcarrier = @SalesorderTracking`, which for the example I gave would be `c_loadcarrier = 'abc,def'`; you can see why this isn't working as you expect. Use a table variable. — Thom A, Feb 15 '22 at 15:36
`where c_loadcarrier in (@SalesorderTracking)` is looking for the value in a *list of values* - the list you have supplied is a single scalar string; `in` does not work this way. — Stu, Feb 15 '22 at 15:37
Does this answer your question? [SQL Server - In clause with a declared variable](https://stackoverflow.com/questions/2944511/sql-server-in-clause-with-a-declared-variable) — Thom A, Feb 15 '22 at 15:37
@Larnu OK, I did not know that. I guess it counts for variable value 'abc','def' too because that's actually the value of the variable, not 'abc,def' — OWB, Feb 15 '22 at 21:20

JBrooks · Accepted Answer · 2022-02-15T15:47:39.343

I think this should work:

    SELECT  [MyDB].[dbo].[t_Item_log].[c_id]
            ,[MyDB].[dbo].[t_Item_log].[c_Info]
            ,CASE [c_TypeRef]
                WHEN '1' THEN 'Loadcarrier From'
                WHEN '2' THEN 'Loadcarrier To'
            END AS [Type]
            ,[c_loadcarrier]
            ,[c_WorkerName]
            ,[c_Material]
            ,[c_Qty]
            ,[c_Created]     
            ,[c_ShelfLabelFrom]
            ,[c_WarehouseFrom]
            ,[c_WareHouseAreaFrom]
            ,[c_ZoneFrom]
            ,[c_TransactionId]
            ,[c_ItemRef]
            FROM dbo.t_Item_log 
            INNER JOIN dbo.t_Item_log_LoadCarrier 
                ON dbo.t_Item_log.c_id = dbo.t_Item_log_LoadCarrier.c_Item_LogRef
            where c_loadcarrier in 
                 (SELECT c_loadcarrier 
                 FROM [ApportMM].[dbo].[#SalesorderTracking]) 
            AND (c_TypeRef = '1' OR c_TypeRef = '2')
            order by [c_loadcarrier] desc, [Type] desc

I believe this address the OP's issue, just recommend replacing the OR in the WHERE clause with ```c_TypeRef IN ('1','2')``` — Stephan, Feb 15 '22 at 18:13

Can't get variable in where clause to work with my query

1 Answers1