February 9, 2021 at 7:12 pm
I need to return records that contain one of several primary key Id's (these Id's are INT data types). The id's will be passed into the stored procedure via a varchar containing the id's separated by commas. I'd like to then use these Id's within my WHERE clause but I'm not sure how.
Ex.
DECLARE @myIds varchar(20)
SET @myIds = '123, 999, 555'
SELECT * FROM dbo.MyTable WHERE id IN (123, 999, 555)
Thanks
February 9, 2021 at 7:52 pm
No. Unless you use dynamic SQL, you will need to insert into a temp table or table variable -- e.g., via STRING_SPLIT() function -- and join to that table.
With STRING_SPLIT, if you can't eliminate the spaces first , you'll need to use replace to eliminate them -- STRING_SPLIT only handles single-character delimiters.
DECLARE @myIds varchar(20)
SET @myIds = '123, 999, 555';
DECLARE @IDTable TABLE (id INT NOT NULL);
SET @myIds = REPLACE(@myIds,' ','');
INSERT INTO @IDTable
SELECT * FROM STRING_SPLIT(@myIds,',') myIds;
SELECT * FROM dbo.MyTable
INNER JOIN @IDTable myIds ON myIds.id = MyTable.id
February 9, 2021 at 8:23 pm
I'd stick with temp tables. And it can't hurt -- and could help -- to use a PK in the temp table (or table variable).
DROP TABLE IF EXISTS #ids
CREATE TABLE #ids ( id int PRIMARY KEY );
INSERT INTO #ids
SELECT DISTINCT value
FROM SPLIT_STRING(REPLACE(@myids, ' ', ''), ',')
--Then your main SELECT, with an INNER JOIN on the temp table from the main table
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 9, 2021 at 8:24 pm
You don't have to use replace - the string will be converted (implicitly) to an integer for comparison so any leading/trailing blanks will not affect that conversion.
You can also use STRING_SPLIT directly in the query:
DECLARE @myIds varchar(20)
SET @myIds = '123, 999, 555'
SELECT * FROM dbo.MyTable WHERE id IN (SELECT ss.value FROM STRING_SPLIT(@myIds, ',') ss)
You can avoid the implicit conversion with an explicit conversion:
Declare @testTable table (ID int, MyValue varchar(10));
Insert Into @testTable (ID, MyValue)
Values (111, 'Not Found')
, (123, 'Found')
, (523, 'Not Found')
, (555, 'Found')
, (900, 'Not Founc')
, (999, 'Found');
Select *
From @testTable tt
Where tt.ID In (Select cast(ss.value As int)
From string_split('123, 999, 555', ',') ss);
If your string might contain blanks - or values that cannot be converted then use try_cast. If you want to use the string with a NOT IN and invalid values could be returned - then you must also use COALESCE or ISNULL to return a valid non-null value, or the results will not be as expected because of the NULL values.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 9, 2021 at 8:34 pm
You don't have to use replace - the string will be converted (implicitly) to an integer for comparison so any leading/trailing blanks will not affect that conversion.
You can also use STRING_SPLIT directly in the query:
(1) Unless a stray blank somehow got between the digits in an id (maybe not worth considering)
(2) Yes, but then SQL will have to weed out potential dups at run time and you won't get the advantage of a keyed list in case it ends up being a long list (again, maybe not worth considering, depending on your preferences).
Also, personally I find it often useful to be able to call a proc with this type of logic with an existing list of ids, without having to put them into a string first. Again, how useful you think that could be is up you.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 9, 2021 at 8:45 pm
Jeffrey Williams wrote:You don't have to use replace - the string will be converted (implicitly) to an integer for comparison so any leading/trailing blanks will not affect that conversion.
You can also use STRING_SPLIT directly in the query:
(1) Unless a stray blank somehow got between the digits in an id (maybe not worth considering)
(2) Yes, but then SQL will have to weed out potential dups at run time and you won't get the advantage of a keyed list in case it ends up being a long list (again, maybe not worth considering, depending on your preferences).
Also, personally I find it often useful to be able to call a proc with this type of logic with an existing list of ids, without having to put them into a string first. Again, how useful you think that could be is up you.
I don't follow item 1 - if you have a blank space in the ID such as '123, 23 4,555' then the value ' 23 4' cannot be converted to an integer whether that is put into a table variable or referenced directly. STRING_SPLIT still has to be used to parse the string...
For item 2 - duplicates are not an issue. IN doesn't care about duplicates...but if you have a very large list then you probably don't want to pass in a string anyways. A table parameter would be much better for very large lists.
Not sure what you are trying to say with the last statement...why would you need to 'put' them in a string first?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 9, 2021 at 8:52 pm
ScottPletcher wrote:Jeffrey Williams wrote:You don't have to use replace - the string will be converted (implicitly) to an integer for comparison so any leading/trailing blanks will not affect that conversion.
You can also use STRING_SPLIT directly in the query:
(1) Unless a stray blank somehow got between the digits in an id (maybe not worth considering)
(2) Yes, but then SQL will have to weed out potential dups at run time and you won't get the advantage of a keyed list in case it ends up being a long list (again, maybe not worth considering, depending on your preferences).
Also, personally I find it often useful to be able to call a proc with this type of logic with an existing list of ids, without having to put them into a string first. Again, how useful you think that could be is up you.
I don't follow item 1 - if you have a blank space in the ID such as '123, 23 4,555' then the value ' 23 4' cannot be converted to an integer whether that is put into a table variable or referenced directly. STRING_SPLIT still has to be used to parse the string...
For item 2 - duplicates are not an issue. IN doesn't care about duplicates...but if you have a very large list then you probably don't want to pass in a string anyways. A table parameter would be much better for very large lists.
Not sure what you are trying to say with the last statement...why would you need to 'put' them in a string first?
It can be converted to an int you remove the space first, right?
I thought IN had to remove duplicates, esp. for use with an INNER JOIN, so in that case it does "care about dups".
If the code uses STRING_SPLIT(@ids, ...) to get the id values, then to pass in values to that code I must use a string, right? I'm not sure what your objection is to my statement.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 9, 2021 at 9:05 pm
Jeffrey Williams wrote:ScottPletcher wrote:Jeffrey Williams wrote:You don't have to use replace - the string will be converted (implicitly) to an integer for comparison so any leading/trailing blanks will not affect that conversion.
You can also use STRING_SPLIT directly in the query:
(1) Unless a stray blank somehow got between the digits in an id (maybe not worth considering)
(2) Yes, but then SQL will have to weed out potential dups at run time and you won't get the advantage of a keyed list in case it ends up being a long list (again, maybe not worth considering, depending on your preferences).
Also, personally I find it often useful to be able to call a proc with this type of logic with an existing list of ids, without having to put them into a string first. Again, how useful you think that could be is up you.
I don't follow item 1 - if you have a blank space in the ID such as '123, 23 4,555' then the value ' 23 4' cannot be converted to an integer whether that is put into a table variable or referenced directly. STRING_SPLIT still has to be used to parse the string...
For item 2 - duplicates are not an issue. IN doesn't care about duplicates...but if you have a very large list then you probably don't want to pass in a string anyways. A table parameter would be much better for very large lists.
Not sure what you are trying to say with the last statement...why would you need to 'put' them in a string first?
It can be converted to an int you remove the space first, right?
I thought IN had to remove duplicates, esp. for use with an INNER JOIN, so in that case it does "care about dups".
If the code uses STRING_SPLIT(@ids, ...) to get the id values, then to pass in values to that code I must use a string, right? I'm not sure what your objection is to my statement.
You are correct - if you remove spaces first it could be converted...but then the question is whether that is correct or not. This can still be accomplished without having to put anything into a table variable - just put the replace on the results from string_split.
IN does not have to remove duplicates and does not remove duplicates depending on how it is used. And - I am not sure what you mean when using with an INNER JOIN. If you mean something like: JOIN table ON column IN (some list) - IN by itself will not remove duplicates and if there are duplicates you will get multiple (duplicate) rows returned from the join.
I am still confused - if you have a procedure that accepts a list of values, of course it has to be a string. What other option is there for providing a list of id's? What construct are you going to use to pass a list of id's to a procedure if not using a string?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 9, 2021 at 9:20 pm
ScottPletcher wrote:Jeffrey Williams wrote:ScottPletcher wrote:Jeffrey Williams wrote:You don't have to use replace - the string will be converted (implicitly) to an integer for comparison so any leading/trailing blanks will not affect that conversion.
You can also use STRING_SPLIT directly in the query:
(1) Unless a stray blank somehow got between the digits in an id (maybe not worth considering)
(2) Yes, but then SQL will have to weed out potential dups at run time and you won't get the advantage of a keyed list in case it ends up being a long list (again, maybe not worth considering, depending on your preferences).
Also, personally I find it often useful to be able to call a proc with this type of logic with an existing list of ids, without having to put them into a string first. Again, how useful you think that could be is up you.
I don't follow item 1 - if you have a blank space in the ID such as '123, 23 4,555' then the value ' 23 4' cannot be converted to an integer whether that is put into a table variable or referenced directly. STRING_SPLIT still has to be used to parse the string...
For item 2 - duplicates are not an issue. IN doesn't care about duplicates...but if you have a very large list then you probably don't want to pass in a string anyways. A table parameter would be much better for very large lists.
Not sure what you are trying to say with the last statement...why would you need to 'put' them in a string first?
It can be converted to an int you remove the space first, right?
I thought IN had to remove duplicates, esp. for use with an INNER JOIN, so in that case it does "care about dups".
If the code uses STRING_SPLIT(@ids, ...) to get the id values, then to pass in values to that code I must use a string, right? I'm not sure what your objection is to my statement.
You are correct - if you remove spaces first it could be converted...but then the question is whether that is correct or not. This can still be accomplished without having to put anything into a table variable - just put the replace on the results from string_split.
IN does not have to remove duplicates and does not remove duplicates depending on how it is used. And - I am not sure what you mean when using with an INNER JOIN. If you mean something like: JOIN table ON column IN (some list) - IN by itself will not remove duplicates and if there are duplicates you will get multiple (duplicate) rows returned from the join.
I am still confused - if you have a procedure that accepts a list of values, of course it has to be a string. What other option is there for providing a list of id's? What construct are you going to use to pass a list of id's to a procedure if not using a string?
I'm going to pass the temp table that the code uses for the join in my method. Again, that's another advantage of using that method.
You will NOT get dup rows from dup values in an IN clause. Easy enough to verify:
CREATE TABLE #t1 ( col1 int NOT NULL )
INSERT INTO #t1 VALUES( 1 )
SELECT *
FROM #t1
WHERE col1 IN (1, 1, 1, 1, 1, 1, 1)
DROP TABLE #t1
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 9, 2021 at 9:39 pm
You will NOT get dup rows from dup values in an IN clause. Easy enough to verify:
CREATE TABLE #t1 ( col1 int NOT NULL )
INSERT INTO #t1 VALUES( 1 )
SELECT *
FROM #t1
WHERE col1 IN (1, 1, 1, 1, 1, 1, 1)
DROP TABLE #t1
You just changed your argument...and I already stated that IN does not care about duplicates. You stated that SQL would need to 'weed' out duplicates and in relation to INNER joins:
I thought IN had to remove duplicates, esp. for use with an INNER JOIN, so in that case it does "care about dups".
Now you are saying IN doesn't care about dupes? Of course it doesn't...which is what I already stated.
So yes, if you use a table variable or temp table and are going to JOIN to that table you do have to be concerned with duplicates. Using IN in the WHERE clause you do not have to worry about duplicates.
Using a temp table and JOIN - you *might* see better performance, but probably won't realize any real benefit unless you are using a very large list.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 9, 2021 at 9:51 pm
ScottPletcher wrote:You will NOT get dup rows from dup values in an IN clause. Easy enough to verify:
CREATE TABLE #t1 ( col1 int NOT NULL )
INSERT INTO #t1 VALUES( 1 )
SELECT *
FROM #t1
WHERE col1 IN (1, 1, 1, 1, 1, 1, 1)
DROP TABLE #t1You just changed your argument...and I already stated that IN does not care about duplicates. You stated that SQL would need to 'weed' out duplicates and in relation to INNER joins:
I thought IN had to remove duplicates, esp. for use with an INNER JOIN, so in that case it does "care about dups".
Now you are saying IN doesn't care about dupes? Of course it doesn't...which is what I already stated.
So yes, if you use a table variable or temp table and are going to JOIN to that table you do have to be concerned with duplicates. Using IN in the WHERE clause you do not have to worry about duplicates.
Using a temp table and JOIN - you *might* see better performance, but probably won't realize any real benefit unless you are using a very large list.
Actually, you're the one changing what you said, which was: "If you mean something like: JOIN table ON column IN (some list) - IN by itself will not remove duplicates and if there are duplicates you will get multiple (duplicate) rows returned from the join." But that is NOT true, as I demonstrated.
But "does not care" is also not correct. If SQL "did not care", it would ignore dups, right? But since SQL does remove dups, it DOES care. I never changed that. I said SQL would have to remove the dups, which I still believe it does do for IN lists.
As to performance, you could get noticeable gains even without a really large main table, since with a lookup table join to a PK, SQL would "know" that the values are in ascending order. On a loop join, that could be very helpful and help short-circuit searches.
The other major advantage of using a table for the list, as I mentioned above, is that one can pass a table in directly, without being forced to convert it to a comma-delimited string first. If you do end up with (very) large list to pass in, that is much more convenient.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 9, 2021 at 11:53 pm
Please look up these two articles for help.
https://www.red-gate.com/simple-talk/sql/learn-sql-server/values-and-long-parameter-lists-part-i/
https://www.red-gate.com/simple-talk/sql/learn-sql-server/values-and-long-parameter-lists-part-ii/
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply