3

I have a requirement to retrieve data from a table based on the comma separated values sent in the parameters for stored procedure. As of now I have made the code work for one single value not sure how to make it work for multiple values.

Sample table:

CREATE TABLE [dbo].FinalStatus
(
    [ID] [int] Primary key IDENTITY(1,1) NOT NULL,
    [Col1] [varchar](15) NULL,
    [Col2] [varchar](15) NULL,
    [Col3] [varchar](100) NOT NULL,
    [LastUpdatedDate] [datetime] NOT NULL DEFAULT (getdate())
)

Test data:

Insert into FinalStatus (Col1, Col2, Col3) values ('10','ABC21','Msg1')
Insert into FinalStatus (Col1, Col2, Col3) values ('10','ABC21','Msg2')
Insert into FinalStatus (Col1, Col2, Col3) values ('11','C21','Some Msg1')
Insert into FinalStatus (Col1, Col2, Col3) values ('12','BC21','Some Msg2')

Stored procedure:

CREATE PROCEDURE [dbo].[FindResult]
    (@col1  VARCHAR(15) = NULL, 
     @col2 VARCHAR(15) = NULL)
AS
    SET NOCOUNT ON
BEGIN
    DECLARE @c1 VARCHAR(15)
    DECLARE @c2 VARCHAR(15)

    SET @c1 = @col1
    SET @c2 = @col2

    SELECT 
        Col2, Col1, 
        LastUpdatedDate, Col3
    FROM 
        dbo.FinalStatus
    WHERE 
        (Col1 = @c1 OR @c1 IS NULL)
        AND (Col2 = @c2 OR @c2 IS NULL)
    ORDER BY 
        LastUpdatedDate DESC
END

Execution script for single value (it works till here):

--To get all data
EXEC [dbo].[FindResult]

--passing first parameter alone
EXEC [dbo].[FindResult] @col1 = '10', @col2 = NULL

--passing second parameter alone
EXEC [dbo].[FindResult] @col1 = null , @col2 = 'c21'

Now how to make it return appropriate result even when we pass multiple values to the parameter?

Something like this:

EXEC [dbo].[FindResult] @col1 = '10,12', @col2 = NULL
EXEC [dbo].[FindResult] @col1 = null , @col2 = 'ABC21, c21'

The underlying table would minimum have 100000 records at any given point in time.

Did tried reading "Arrays and Lists in SQL Server 2008" by Erland Sommarskog but its way over my head. So looking for some help in modifying this stored procedure.

  • @srutzky nope I can't influence the input delimiter to be changed. – prasanth Jul 20 '16 at 16:19
7

There are several ways of doing it. Changing the data model may also be a better option if you can do it.

1. Comma separated parameters

If you want to stick with the comma separated string, you can find many string split functions online using CLR, CTE, XML, ... I am not going to put them all here and I won't benchmark them. But you must know that they all have their own issues. For more information you can look at this post from Aaron Bertrand: Split strings the right way – or the next best way

Sample query (using XML conversion):

DECLARE @c1 nvarchar(100) = N'10,15,13,14';
DECLARE @delimiter nvarchar(1) = N',';
SELECT v1 = LTRIM(RTRIM(vals.node.value('(./text())[1]', 'nvarchar(4000)')))
FROM (
    SELECT x = CAST('<root><data>' + REPLACE(@c1, @delimiter, '</data><data>') + '</data></root>' AS XML).query('.')
) v
CROSS APPLY x.nodes('/root/data') vals(node);

Output:

v1
10
15
13
14

Main query:

The idea is to convert it to a table. It can then be used in a correlated sub query:

DECLARE @c1 nvarchar(100) = N'10,15,13,14';
DECLARE @c2 nvarchar(100) = N'C21, B21';
DECLARE @delimiter nvarchar(1) = N',';

SELECT * 
FROM FinalStatus f
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT CAST('<root><data>' + REPLACE(@c1, @delimiter, '</data><data>') + '</data></root>' AS XML) AS x
    )t
    CROSS APPLY x.nodes('/root/data') vals(node)
    WHERE  LTRIM(RTRIM(vals.node.value('.[1]', 'nvarchar(4000)'))) = f.Col1
)
OR EXISTS (
    SELECT 1
    FROM (
        SELECT CAST('<root><data>' + REPLACE(@c2, @delimiter, '</data><data>') + '</data></root>' AS XML) AS x
    )t
    CROSS APPLY x.nodes('/root/data') vals(node)
    WHERE  LTRIM(RTRIM(vals.node.value('.[1]', 'nvarchar(4000)'))) = f.Col2
);

Only the WHERE clause has to be updated in your procedure. Parameters remains of varchar type with comma separated values.

Output:

ID  Col1    Col2    Col3        LastUpdatedDate
1   10      ABC21   Msg1        2016-07-20 09:06:19.380 => match c1
2   10      ABC21   Msg2        2016-07-20 09:06:19.390 => match c1
3   11      C21     Some Msg1   2016-07-20 09:06:19.390 => match c2

2. XML parameter(s)

If you can change the parameter' types, the XML data type can be used. It can be easily deserialized by the procedure and the query.

This query is pretty similar to the previous one. However it gives a better control over invalid values and special characters since there is no conversion or search and replace.

DECLARE @c xml = N'
    <root>
        <c1>10</c1><c1>15</c1><c1>13</c1><c1>14</c1>
        <c2>C21</c2><c2>B21</c2>
    </root>';
DECLARE @delimiter nvarchar(1) = N',';

SELECT * 
FROM FinalStatus f
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT x = @c.query('.')
    ) v
    CROSS APPLY x.nodes('/root/c1') val1(node)  
    WHERE  LTRIM(RTRIM(val1.node.value('.[1]', 'nvarchar(4000)'))) = f.Col1
)
OR EXISTS (
    SELECT 1
    FROM (
        SELECT x = @c.query('.')
    ) v
    CROSS APPLY x.nodes('/root/c2') val1(node)  
    WHERE  LTRIM(RTRIM(val1.node.value('.[1]', 'nvarchar(4000)'))) = f.Col2
);

The parameter' types have to be changed to xml. I used only one variable with c1 and c2 nodes but 1 variable for each node (c1, c2, ...) and different node names can also be used:

DECLARE @c1 xml = N'<root><data>10</data><data>15</data><data>13</data><data>14</data></root>';
DECLARE @c2 xml = N'<root><data>C21</data><data>B21</data></root>';
DECLARE @c3 xml = N'...';

The correct path and variables must be updated in the nodes part of the CROSS APPLY.

Using .Net, Powershell or other languages, an array or other types can easily be converted to xml and used as a parameter of the procedure.

...

3. User-Defined Table Types

Another option would be to create a Table Value Type that can be used by the Stored Procedure parameters.

Table Type

CREATE TYPE [dbo].[TableTypeCols] AS TABLE
(
    [col] varchar(15)
);

Stored Procedure with Table Type parameter(s)

You then update the Stored Procedure using this newly created type:

CREATE OR ALTER PROCEDURE [dbo].[FindResult]
    @c1 [dbo].[TableTypeCols] READONLY
    , @c2 [dbo].[TableTypeCols] READONLY
AS
    SELECT * -- fs.[...], fs.[...], ...
    FROM [dbo].[FinalStatus] fs
    WHERE 
        EXISTS (
            SELECT 1 FROM @c1 c1 WHERE c1.col = fs.Col1
        )
        OR EXISTS (
            SELECT 1 FROM @c2 c2 WHERE c2.col = fs.Col2
        );
GO;

READONLY is mandatory in the parameter declaration.

Stored Procedure call with Table Type parameter(s)

With SQL, you can call it the same way using a table variable:

DECLARE @tc1 [dbo].[TableTypeCols]; 
DECLARE @tc2 [dbo].[TableTypeCols];

INSERT INTO @tc1(col) VALUES('10'), ('15'), ('13'), ('14');
INSERT INTO @tc2(col) VALUES('C21'), ('B21');

EXEC dbo.FindResult @c1 = @tc1, @c2 = @tc2;

Output:

ID  Col1    Col2    Col3        LastUpdatedDate
1   10      ABC21   Msg1        2016-07-20 09:06:19.380 => match c1
2   10      ABC21   Msg2        2016-07-20 09:06:19.390 => match c1
3   11      C21     Some Msg1   2016-07-20 09:06:19.390 => match c2

Please note that if you are planning on using more than a couple rows for each parameters, you should run some performance test with real data and make sure it works properly on your system.

.Net C# Call

The procedure can the be call from you .Net code

// Create & Fill Parameter 1
DataTable dt1 = new DataTable();
dt1.Columns.Add("col", typeof (string));
DataRow row = dt1.NewRow();
row["col"] = ("10");
dt1.Rows.Add(row);
/*
    ... add more row ...
*/

// Create & Fill Parameter 2
DataTable dt2 = new DataTable();
dt2.Columns.Add("col", typeof (string));
DataRow row = dt2.NewRow();
row["col"] = ("C21");
dt2.Rows.Add(row);
/*
    ... add more row ...
*/

// Output dataset
DataSet ds = new DataSet("SQLDatabase");

using (SqlConnection conn = new SqlConnection(...))
{
    // Stored Procedure with table parameters
    SqlCommand sqlComm = new SqlCommand("dbo.FindResult", conn);
    sqlComm.CommandType = CommandType.StoredProcedure;

    // Parameter 1
    SqlParameter param = new SqlParameter("@c1", SqlDbType.Structured)
    {
        TypeName = "dbo.TableTypeCols",
        Value = dt1
    };
    sqlComm.Parameters.Add(param);

    // Parameter 2
    SqlParameter param = new SqlParameter("@c2", SqlDbType.Structured)
    {
        TypeName = "dbo.TableTypeCols",
        Value = dt2
    };
    sqlComm.Parameters.Add(param);

    // Call Stored Procedure
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = sqlComm;
    da.Fill(ds);
}

SqlDbType.Structured is mandatory.

Please note that I am not a .Net expert and there are other or better ways to write and use this code.

4. Json Type Parameters (Since SQL Server 2016)

With minimal changes, you can declare a NVARCHAR(MAX) parameters with json string.

json parameters

DECLARE @C1 NVARCHAR(MAX);
SET @jsonC1 =  
N'[
    { "col" : "10"}, { "col" : "15"}, { "col" : "13"}, { "col" : "14"}
]';
DECLARE @C2 NVARCHAR(MAX);
SET @jsonC2 =  
N'[
    { "col" : "C21"}, { "col" : "B21"}
]';

Query

SELECT * 
FROM dbo.FinalStatus f
WHERE 
EXISTS (
    SELECT 1 FROM OPENJSON(@C1) WITH(col nvarchar(15)) AS c1 WHERE c1.col = f.Col1
)
OR
EXISTS (
    SELECT 1 FROM OPENJSON(@C2) WITH(col nvarchar(15)) AS c2 WHERE c2.col = f.Col2 
);

Please note that OPENJSON requires SQL Server 2016.

| improve this answer | |
3

At some point, you've got to split that string. Either before calling your procedure as suggested by @MguerraTorres, or in the procedure. Here's a good example of a string splitter function: http://www.codeproject.com/Articles/7938/SQL-User-Defined-Function-to-Parse-a-Delimited-Str

That function returns a table data type. You can use that table to query for rows that are in the split table.

select what_i_want from my_table where value in (select value from split_table )

SQL Server 2016 has a built in STRING_SPLIT function.

| improve this answer | |
2

A quick solution here is to change your where clause from '=' to 'in' (as an example)

where col1 = @c1 --> where col1 in (@c1)

and change the @c1 to a value list with single quotes, for example

@c1 = '1,2,3' --> '1','2','3'

So here is the modified procedure script

CREATE PROCEDURE [dbo].[FindResult]
    (@col1  VARCHAR(15) = NULL, 
     @col2 VARCHAR(15) = NULL)
AS
    SET NOCOUNT ON
BEGIN
    DECLARE @c1 VARCHAR(15)
    DECLARE @c2 VARCHAR(15)
    declare @qry varchar(3000);


    SET @c1 = ''''+replace(@col1,',', ''',''')+'''' -- make @col1 to be a string in signle quote
    SET @c2 =''''+replace(@col2,',', ''',''')+''''  -- make @col1 to be a string in signle quote 
    select @c1=iif(@c1 is null, 'col1', @c1), @c2=iif(@c2 is null, 'col2', @c2);

    set @qry='
    SELECT 
        Col2, Col1, 
        LastUpdatedDate, Col3
    FROM 
        dbo.FinalStatus
    WHERE 
        (Col1 in ('+@c1+') OR 1=1)
        AND (Col2 in ('+@c2+') OR 2=2)
    ORDER BY 
        LastUpdatedDate DESC';

   if @c1 <> 'col1' -- i.e. @col1 parameter is not null, remove OR 1=1
     set @qry = replace(@qry, 'OR 1=1', '');
   IF @C2 <> 'col2' -- i.e. @col2 parameter is not null, remove OR 2=2
     set @qry = replace(@qry, 'OR 2=2', '');

    print @qry -- you can comment out this line, it is for debugging
    exec (@qry)
END

Now you can use multiple values in your parameters

EXEC [dbo].[FindResult] @col1 = '10,12', @col2 = NULL;
EXEC [dbo].[FindResult] @col1 = null , @col2 = 'ABC21,c21'; --note NO space before or after comma.
| improve this answer | |
1

One option that entails minimal change in the calling code is to pass in simplistic XML. This is very similar to Option 2 in @JulienVavasseur's answer, except that it keeps the two input parameters separate. The thought here is that it should be easy enough to convince the developer to change the existing String.Join(',', _SomeIntArray) to instead be something like: '<v>' + String.Join('</v><v>', _SomeIntArray) + '</v>'. Then you could use them in a query similar to the following, which I left as is since it is a runnable example:

DECLARE @Input1 XML,
        @Input2 XML;

SET @Input1 = '<v>231</v><v>175</v>';
SET @Input2 = '<v>256</v><v>4</v>';

-- Both commented out = 800
-- Only @Input2 commented out = 119
-- Only @Input1 commented out = 390
-- Neither commented out = 63

SELECT so.*, in1.col.value('./text()[1]', 'INT')
FROM   master.sys.columns so
LEFT JOIN @Input1.nodes('v') in1(col)
       ON in1.col.value('./text()[1]', 'INT') = so.[system_type_id]
LEFT JOIN @Input2.nodes('v') in2(col)
       ON in2.col.value('./text()[1]', 'INT') = so.[max_length]
WHERE (@Input1 IS NULL OR in1.col.value('./text()[1]', 'INT') IS NOT NULL)
AND   (@Input2 IS NULL OR in2.col.value('./text()[1]', 'INT') IS NOT NULL);
| improve this answer | |
0

It is not possible to make a stored procedure run twice for one single input.

I'd suggest a CURSOR or Loop which will Execute the stored procedure once for each desired row in the table.

Each loop iteration will: 1) Set two variables to be equal to Col1 and Col2 2) Run the procedure with the new Variables as Input Parameters.

--Loop Code Here
--For each record returned by your FinalStatus table:
SET @Value1 =Col1;
SET @Value2 =Col2;
--Then execute the stored proc with those values
EXEC [dbo].[FindResult] @Value1,@value2
--End Loop Code
| improve this answer | |
  • 1
    Thanks for your comment. I pasted my existing stored procedure to give an idea of what I am querying. I don't think running the same procedure n times for each parameter is a way to go here. I believe there should be a way to modify the existing proc to accommodate list of values? – prasanth Jul 19 '16 at 18:08
  • You can absolutely modify your existing proc to accommodate multiple values. This would involve adding logic that checks how many values were input into each single parameter, parsing those values out, then running a loop within the stored proc to apply the logic within the procedure for each of those values. I can honestly say I'd never do that as it is not very modular/easily maintainable. Your case seems a bit different so if you think it works best then full speed ahead. – MguerraTorres Jul 19 '16 at 18:13
-2

The simplest way I found was to use FIND_IN_SET:

FIND_IN_SET(column_name, values)

For example define values=(1,2,3) and execute:

SELECT name WHERE FIND_IN_SET(id, values)
| improve this answer | |
  • This is for T-SQL, that's MySQL syntax isn't it? – James Anderson Mar 30 '17 at 7:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.