November 18, 2016 at 7:08 am
PLEASE tell me what needs to be done to make something like this work?
DECLARE @PARAM1 VARCHAR(2000) = 'Tom','JOE','BUDD','TIM' --< Obviously this wont work
Select name from employees where name in (@PARAM1)
November 18, 2016 at 7:17 am
You need to split the string to be able to use the individual values.
DECLARE @PARAM1 VARCHAR(2000) = 'Tom,JOE,BUDD,TIM'
SELECT firstname
FROM Employees
WHERE firstname in (SELECT Item FROM dbo.DelimitedSplit8K( @PARAM1, ',')s);
The code and explanation for the splitter can be found here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
If you're on 2008 or a more recent version, you can also use table valued parameters.
November 18, 2016 at 7:27 am
Your question is similar to this thread.
Btw: you start with the values as a single string with comma seperated values ('a,b,c') and not with multiple strings seperated by a comma ('a', 'b', 'c')
November 18, 2016 at 7:34 am
Thank you BOTH!!
November 18, 2016 at 11:40 am
Budd (11/18/2016)
PLEASE tell me what needs to be done to make something like this work?DECLARE @PARAM1 VARCHAR(2000) = 'Tom','JOE','BUDD','TIM' --< Obviously this wont work
Select name from employees where name in (@PARAM1)
This will also work.
DECLARE @PARAM1 VARCHAR(2000) = '|Tom|JOE|BUDD|TIM|';
Select name from employees where @PARAM1 like '%|' + name + '|%';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 18, 2016 at 12:01 pm
Eric M Russell (11/18/2016)
Budd (11/18/2016)
PLEASE tell me what needs to be done to make something like this work?DECLARE @PARAM1 VARCHAR(2000) = 'Tom','JOE','BUDD','TIM' --< Obviously this wont work
Select name from employees where name in (@PARAM1)
This will also work.
DECLARE @PARAM1 VARCHAR(2000) = '|Tom|JOE|BUDD|TIM|';
Select name from employees where @PARAM1 like '%|' + name + '|%';
I'm not sure I even understand that one, But you are (of course) correct.
I used this to test
DECLARE @employees TABLE (name VARCHAR(5))
DECLARE @PARAM1 VARCHAR(2000) = '|Tom|JOE|BUDD|TIM|';
INSERT INTO @employees
(name)
VALUES('Tom'),('JOE'),('BUDD'),('TIM'),('TED')
SELECT name FROM @employees WHERE @PARAM1 like '%|' + name + '|%';
SELECT name FROM @employees
November 18, 2016 at 1:17 pm
Eric M Russell (11/18/2016)
Budd (11/18/2016)
PLEASE tell me what needs to be done to make something like this work?DECLARE @PARAM1 VARCHAR(2000) = 'Tom','JOE','BUDD','TIM' --< Obviously this wont work
Select name from employees where name in (@PARAM1)
This will also work.
DECLARE @PARAM1 VARCHAR(2000) = '|Tom|JOE|BUDD|TIM|';
Select name from employees where @PARAM1 like '%|' + name + '|%';
The problem with that code is that it's non SARGable, because you're modifying the column. It will prevent an index seek and that would make it slower if the correct index exists.
Here's an improved implementation of the code with the splitter to prevent confusing the optimizer. It's using AdventureWorks2012.
DECLARE @PARAM1 VARCHAR(2000) = '|Xu|White|Williams|';
CREATE TABLE #Employees( LastName nvarchar(50));
INSERT INTO #Employees
SELECT Item
FROM Test.dbo.DelimitedSplit8K( @PARAM1, '|')s
WHERE Item <> '';
SELECT LastName
FROM Person.Person
WHERE LastName IN (SELECT LastName FROM #Employees);
Select LastName from Person.Person where @PARAM1 like '%|' + LastName + '|%';
GO
DROP TABLE #Employees;
November 18, 2016 at 1:57 pm
WOW!!!
So very many possibilities, and so much to consider..
November 20, 2016 at 8:24 am
Budd (11/18/2016)
WOW!!!So very many possibilities, and so much to consider..
Quick questions, can you use Table Variable Parameter? How wide are the largest values passed? How many values at the most would be passed? Have you thought of using dynamic SQL?
😎
November 21, 2016 at 6:42 am
Eirikur Eiriksson (11/20/2016)
Budd (11/18/2016)
WOW!!!So very many possibilities, and so much to consider..
Quick questions, can you use Table Variable Parameter? How wide are the largest values passed? How many values at the most would be passed? Have you thought of using dynamic SQL?
😎
Quick Answers;
Probably, had not attempted that.
12 at most.
Dynamic SQL is always my last resort.
November 21, 2016 at 7:33 am
Eirikur Eiriksson (11/20/2016)
Budd (11/18/2016)
WOW!!!So very many possibilities, and so much to consider..
Quick questions, can you use Table Variable Parameter? How wide are the largest values passed? How many values at the most would be passed? Have you thought of using dynamic SQL?
😎
I'd vote for using TVPs as well in this case, especially if the list of names can be large. It allows you to use set-based SQL constructs like JOINs and EXISTs to provide a solution rather than string manipulation.
November 21, 2016 at 8:02 am
Steve Thompson-454462 (11/21/2016)
Eirikur Eiriksson (11/20/2016)
Budd (11/18/2016)
WOW!!!So very many possibilities, and so much to consider..
Quick questions, can you use Table Variable Parameter? How wide are the largest values passed? How many values at the most would be passed? Have you thought of using dynamic SQL?
😎
I'd vote for using TVPs as well in this case, especially if the list of names can be large. It allows you to use set-based SQL constructs like JOINs and EXISTs to provide a solution rather than string manipulation.
I would be hesitant to recommend TVP for joining to other tables or at least carefully inspect the execution plan as the cardinality estimation may and often will be way off. Same goes for DelimitedSplit8K which will have either an estimation of 10 or 100 rows, depending on the SQL Server version.
😎
November 21, 2016 at 8:13 am
Eirikur Eiriksson (11/21/2016)
Steve Thompson-454462 (11/21/2016)
Eirikur Eiriksson (11/20/2016)
Budd (11/18/2016)
WOW!!!So very many possibilities, and so much to consider..
Quick questions, can you use Table Variable Parameter? How wide are the largest values passed? How many values at the most would be passed? Have you thought of using dynamic SQL?
😎
I'd vote for using TVPs as well in this case, especially if the list of names can be large. It allows you to use set-based SQL constructs like JOINs and EXISTs to provide a solution rather than string manipulation.
I would be hesitant to recommend TVP for joining to other tables or at least carefully inspect the execution plan as the cardinality estimation may and often will be way off. Same goes for DelimitedSplit8K which will have either an estimation of 10 or 100 rows, depending on the SQL Server version.
😎
In both cases, it would be a good idea to use a temp table (clustered) to store the values before using them in queries.
November 21, 2016 at 8:33 am
You have no idea how SQL works and you need to stop what you are doing, and take some time to do more research and studying. Look up what a first normal form is; understand what scaler values are.
SQL is compiled, but you want to treated as if it is an interpreted version of BASIC! Why are you doing this? If you want to have a set of scaler values in a query, then you need to put them in a table or a table constructor.
Please Google my two articles on the long parameter list as Redgate.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
November 21, 2016 at 8:34 am
You have no idea how SQL works and you need to stop what you are doing, and take some time to do more research and studying. Look up what a first normal form is; understand what scaler values are.
SQL is compiled, but you want to treated as if it is an interpreted version of BASIC! Why are you doing this? If you want to have a set of scaler values in a query, then you need to put them in a table or a table constructor.
Please Google my two articles on the long parameter list at Redgate.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply