Sql Server procedure declare a list

Deep dive: Efficient list processing in SQL Server

The secret sauce of User-Defined Table Types

Few things are more satisfying than reusability and efficient querying in the SQL world, and user-defined table types bring both to the table:

CREATE TYPE dbo.ListType AS TABLE(Value INT);


Much like that favorite coffee mug that's just the right size and shape, this one-time structure definition becomes a trusty sidekick across multiple stored procedures. A real caped crusader for your SQL Server performance.

Going with the flow: Table valued parameters

Ever gotten stuck passing arrays to procedures? Table valued parameters are here to save your day, and your data:

CREATE PROCEDURE UseList 
    @ListAsParameter dbo.ListType READONLY
AS
BEGIN
    SELECT * FROM SomeTable WHERE ColumnName IN (SELECT Value FROM @ListAsParameter);
END;


This is like reducing the line at a rock concert entrance by only scanning tickets, not individual concert goers. Talk about reducing overhead!

The beauty of adapting: Dynamic SQL

When your SQL needs to be as flexible as a gymnast and include variable lists in a query, Dynamic SQL is your best bet:

DECLARE @SQL NVARCHAR(MAX), @Params NVARCHAR(MAX);
SET @SQL = N'SELECT * FROM SomeTable WHERE ColumnName IN (' + @Params + N')';
EXEC sp_executesql @SQL;


More versatile than a Swiss Army Knife, simply replace @Params with a concatenated string of list values to make your query fit like a glove.

Power tactics for handling your lists

Slicing and dicing: Splitting comma-separated values

When dealing with a string of values that's longer than the line at the DMV, it's time to enlist a function:

CREATE FUNCTION dbo.SplitList(@List nvarchar(MAX))
RETURNS @ReturnTable TABLE(Value nvarchar(50))
AS
BEGIN
    INSERT INTO @ReturnTable
    SELECT Value FROM STRING_SPLIT(@List, ','); -- Easier than cutting a bad date short.
    RETURN;
END;


Like your go-to karate chop, this will split your values on the fly for rapid-fire querying.

Quick and dirty: Using LIKE with wildcards

Dip your toe into pattern matching with the LIKE operator:

DECLARE @Pattern VARCHAR(100) = '%val%';
SELECT * FROM SomeTable WHERE ColumnName LIKE @Pattern; -- Always making sure we catch the juicy ones.


For when your list is raw and slightly volatile, this gives you a sneaky pass for swift filtering based on partial matches.

The smooth operator: CAST

You can get a little clever and streamline incorporating lists in your SELECT statements with everyone's friend - CAST:

SELECT * FROM SomeTable 
WHERE ColumnID IN (CAST('1,2,3' AS dbo.ListType)); -- Easy peasy lemon squeezy


This subtly converts a casual string list into a table for more formal get-togethers in your IN clause.