If you’ve been doing this a while, you might remember having to parse out a string so you can add that to a dynamic sql statement so you can have a query with a dynamic IN? Or maybe you would parse it out to put it in a table variable to join to the table? Either way, using STRING_SPLIT, it now just got much easier.

I’ve already written a few articles about STRING_SPLIT, discussing what it is and using it with a CROSS JOIN. This will just go a bit further down the rabbit hole of this new function.

Run this to follow along as we create a stored procedure to return one or more of these rows.

create table products (product_id integer, name varchar(30), qty integer);

insert into products values (1, 'CD', 5);
insert into products values (2, 'DVD', 3);
insert into products values (3, 'USB Drive', 10);
insert into products values (4, '3.5 Diskette',1);
insert into products values (5, 'USB Keyboard', 3);
insert into products values (6, 'Telephones', 0);

We want to run a query like this:

SELECT*
FROM products
WHERE product_id in (1,3);

But we don’t always want it to return just 1 and 3.

What to Do

There are several wrong ways to do this and reasons why it won’t work, but let’s get to the point. Let’s roll another … procedure that will work. We create the procedure, then down below, we execute it to try it out.

CREATE PROCEDURE usp_GetProducts(
@productIds NVARCHAR(100)
)
AS
BEGIN

SELECT p.*
FROM products p
JOIN STRING_SPLIT(@productIds,',') ss
ON p.product_id = ss.value;

END;

GO

EXEC usp_GetProducts '1,3';

Things to Remember

Remember that the field returned by the STRING_SPLIT function, and used in your SELECTs and your JOINs, is “value”.

STRING_SPLIT is a function that returns a table, so use it like a table!

  • think of your comma-delimited string as if it were a table and how you need to deal with that table
  • JOIN it or APPLY it to other tables
  • give it an alias

 

Tagged: , , ,