I would like to retrieve certain users from a full list of a temp table #temptable.The query looks like this:
DECLARE @List varchar(max)SELECT @List = coalesce(@List + ',','') + '''' + StaffCode + ''''FROM tblStaffsSELECT UserNameFROM #temptableWHERE #temptable.StaffCode IN (@List)I can tell @List is in a right format:
'AAA','ABB','BBB','CCC','DDD','MMM'And if I change it to
WHERE #temptable.StaffCode IN ('AAA','ABB','BBB','CCC','DDD','MMM')It certainly works, then why not IN (@List)?
Best Answer
Create some split string function and convert the comma separated values to rows then you can use the converted rows IN clause
DECLARE @List VARCHAR(max)SELECT @List = COALESCE(@List + ',', '') +StaffCodeFROM tblStaffsSELECT UserNameFROM #temptableWHERE #temptable.StaffCode IN (SELECT split_valuesFROM dbo.Splitstring_function(@list)) Check here for various Split String function
If you dont want to create functions then you can also directly use the code instead of creating a new function(M.Ali's answer).
Another way of doing it is using dynamic query.
Declare @List varchar(max), @sql nvarchar(max)Select @List = coalesce(@List + ',','') + '''' + StaffCode + ''''From tblStaffsset @sql = 'Select UserNameFrom #temptableWhere #temptable.StaffCode IN ('+ @List + ')'--print @sqlexec (@sql)To debug the dynamic query always print the dynamic sql before executing.
Because the Variable has a string which IN operator reads as 'AAA'',''ABB'',''BBB' and it treats it as a single value.
In your query you should really use the query itself in the IN operator something like....
Select UserNameFrom #temptableWhere #temptable.StaffCode IN (SELECT StaffCode From tblStaffs)Anyway if there is a need to use variable and then read values inside the IN operator from that variable you can do something like this....
DECLARE @List VARCHAR(1000);Select @List = coalesce(@List + ',','') + StaffCode From tblStaffsSELECT *From #temptableWhere #temptable.StaffCode IN (SELECT t.c.value('.', 'VARCHAR(1000)')FROM (SELECT x = CAST('<t>' + REPLACE(@List , ',', '</t><t>') + '</t>' AS XML)) aCROSS APPLY x.nodes('/t') t(c))I would recommend that you not use a comma-delimited string at all. Consider a semi-join instead:
select [Temp].[UserName] from #temptable [Temp]whereexists (select 1 from [tblStaffs] where [tblStaffs].[StaffCode] = [Temp].[StaffCode]);