March 16, 2016 at 8:08 am
I am trying to set up a variable @Location, for use in a where .. IN, but can not seem to get it to work..
declare @Location as varchar(8000)
set @Location = ' '531','410','403' '
Select ...
from ...
WHERE MOMM.PlantCode IN (@Location)
March 16, 2016 at 8:12 am
You can do that with dynamic SQL, or you can insert the values individually into a temp table or table variable and join to that.
John
March 16, 2016 at 8:15 am
No, because IN doesn't work like that.
Column IN (@Var) is equivalent to Column = @Var
You need dynamic SQL, a string split function or a different design.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2016 at 8:16 am
DON'T use dynamic SQL for this, it's a big risk for SQL injection in this case.
You can use a splitter to separate the values in order to use IN.
declare @Location as varchar(8000)
set @Location = '531,410,403'
Select ...
from ...
WHERE MOMM.PlantCode IN (SELECT Item FROM dbo.DelimitedSplit8k(@Location, ',') s);
Get the code and explanation from the splitter in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
You could also insert the separate values in a table instead of using the function directly in the query.
March 16, 2016 at 8:18 am
Or use a table valued parameter or even a string splitter.
--EDIT--
It seems a couple new posts happened while I was posting. I got sidetracked and went for my coffee. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 16, 2016 at 11:23 am
Thanks folks for the help... you would think this would be simple.. oh well. I sort of hate to use a table.. since I would have to have code to detect if the table has been dropped and thus rebuild.. can be done, just hate that type of code. Will try and use a string function, as suggested.
March 16, 2016 at 11:55 am
dwilliscp (3/16/2016)
Thanks folks for the help... you would think this would be simple.. oh well. I sort of hate to use a table.. since I would have to have code to detect if the table has been dropped and thus rebuild.. can be done, just hate that type of code. Will try and use a string function, as suggested.
You could use a temp table. Not sure how you are using this entirely but the scope is relatively small. Also table valued parameters are super simple. You just have to create the type first. Then you create a variable of that type and insert data to it. You can treat it just like any other table (except is readonly when passed as a parameter). I have some generic type table types defined for just this type of thing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 21, 2016 at 8:58 pm
In our SQL environment my observation always had been that TVP outperformed all the custom TSQL splitter functions and CLR methods.In some instances the TVP performance was on par with Jeff Moden's splitter function but it could never outperform it but it definitely outperformed other TSQL splitter functions.
If you go down the TVP route make sure you have a primary key defined on the TVP column.We had this one instance where the front end code used to send multiple duplicates values in the parameter.Having a primary key in the TVP would prevent that and also bring in some performance benefits.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 22, 2016 at 12:13 pm
Sachin Nandanwar (3/21/2016)
In our SQL environment my observation always had been that TVP outperformed all the custom TSQL splitter functions and CLR methods.In some instances the TVP performance was on par with Jeff Moden's splitter function but it could never outperform it but it definitely outperformed other TSQL splitter functions.If you go down the TVP route make sure you have a primary key defined on the TVP column.We had this one instance where the front end code used to send multiple duplicates values in the parameter.Having a primary key in the TVP would prevent that and also bring in some performance benefits.
Good note... thanks for the heads up.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply