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.
Best Answer
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):
Output:
Main query:
The idea is to convert it to a table. It can then be used in a correlated sub query:
Only the
WHERE
clause has to be updated in your procedure. Parameters remains of varchar type with comma separated values.Output:
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.
The parameter' types have to be changed to xml. I used only one variable with
c1
andc2
nodes but 1 variable for each node (c1, c2, ...) and different node names can also be used:The correct path and variables must be updated in the
nodes
part of theCROSS 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
Stored Procedure with Table Type parameter(s)
You then update the Stored Procedure using this newly created type:
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:
Output:
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
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
Query
Please note that OPENJSON requires SQL Server 2016.