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
CodePudding user response:
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