Here's a really handy trick I picked up.
If you have a stored procedure and you would like to pass a set of values in as part of a filter, ie. A list of Identifiers, or Names that you want to filter on, then you can use a handy table that contains just a bunch of numbers. I'll show you.
-- Create the table
CREATE TABLE [dbo].[Nums](
[n] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[n] ASC
) ON [PRIMARY]
) ON [PRIMARY]
-- Now add the numbers
DECLARE @index int
SET @index = 1
WHILE (@index < @NumbersToAdd)
BEGIN
INSERT INTO [dbo].[Nums] (n)
VALUES (@index)
SET @index = @index +1
END
create a function to put the values into a table.
CREATE FUNCTION dbo.ufn_CommaValues(@List varchar(8000))
RETURNS @Array Table
(
pos integer,
val varchar(10)
)
AS
BEGIN
INSERT @Array
SELECT
n - LEN(REPLACE(LEFT(@arr, n), ',', '')) + 1 AS pos,
CAST(SUBSTRING(@arr, n, CHARINDEX(',', @arr + ',', n) - n) AS INT) AS val
FROM Nums --Uses the Nums table.
WHERE n <= LEN(@arr)
AND SUBSTRING(',' + @arr, n, 1) = ','
ORDER BY pos
RETURN
END
Now we have everything you can use it like this:
CREATE PROC dbo.usp_Example
@List varchar(8000) --This is a list of comma separated GUIDS
AS
SELECT *
FROM TableA
INNER JOIN dbo.ufn_CommaValues(@List) CSV ON TableA.Id = CSV.Val
Pretty useful if you have a list of records that you want to filter on, particularly for debugging.
