StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>call a stored procedure, and pass in a CVS list of values, using this method:</p> <p>Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:</p> <pre><code>CREATE TABLE Numbers (Number int NOT NULL, CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] DECLARE @x int SET @x=0 WHILE @x<8000 BEGIN SET @x=@x+1 INSERT INTO Numbers VALUES (@x) END </code></pre> <p>use this function to split your string, which does not loop and is very fast:</p> <pre><code>CREATE FUNCTION [dbo].[FN_ListToTable] ( @SplitOn char(1) --REQUIRED, the character to split the @List string on ,@List varchar(8000) --REQUIRED, the list to split apart ) RETURNS @ParsedList table ( ListValue varchar(500) ) AS BEGIN /** Takes the given @List string and splits it apart based on the given @SplitOn character. A table is returned, one row per split item, with a column name "ListValue". This function workes for fixed or variable lenght items. Empty and null items will not be included in the results set. Returns a table, one row per item in the list, with a column name "ListValue" EXAMPLE: ---------- SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B') returns: ListValue ----------- 1 12 123 1234 54321 6 A * ||| B (10 row(s) affected) **/ ---------------- --SINGLE QUERY-- --this will not return empty rows ---------------- INSERT INTO @ParsedList (ListValue) SELECT ListValue FROM (SELECT LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue FROM ( SELECT @SplitOn + @List + @SplitOn AS List2 ) AS dt INNER JOIN Numbers n ON n.Number < LEN(dt.List2) WHERE SUBSTRING(List2, number, 1) = @SplitOn ) dt2 WHERE ListValue IS NOT NULL AND ListValue!='' RETURN END --Function FN_ListToTable </code></pre> <p>you can use this function as a table in a join:</p> <pre><code>SELECT Col1, COl2, Col3... FROM YourTable INNER JOIN FN_ListToTable(',',@YourString) s ON YourTable.ID = s.ListValue </code></pre> <p>so for Linq, create a stored procedure:</p> <p>CREATE PROCEDURE YourProcedure (</p> <pre><code> @param1 int ,@param2 varchar(8000) --csv list is here </code></pre> <p>) as</p> <pre><code>SELECT Col1, COl2, Col3... FROM YourTable INNER JOIN FN_ListToTable(',',@param2 ) s ON YourTable.ID = s.ListValue WHERE Col1=@param1 </code></pre> <p>go</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload