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

Answer 1

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