IN-clause with optional parameter SQL

Question

I have a stored procedure that returns me a set of data based on 2 input parameters. One of the parameter is optional so I am using

WHERE 
(tbl_Process.ProjectID = @ProjectID)
AND 
(tbl_AnalysisLookup.AnalysisCodeID = 7)
AND
(tbl_ProcessSubStep.ProcessID = ISNULL(@ProcessID,tbl_ProcessSubStep.ProcessID))

The @ProcessID is optional parameter so the user may/may not provide it. Now I need to change my stored procedure to accommodate multiple ProcessId's i.e. the user can now select a list of multiple ProcessId's, Single ProcessID or No ProcessID and the stored proc should handle all these scenarios. What is the best way to achieve this without using Dynamic queries unless absolutely required. In a nutshell, I wanted my stored proc to handle optional parameters with multiple values(WHERE IN Clause). The solution and relative link to the webpage I got it from has been provided below. It's a very good article and will help you to choose the right solution based on your requirements.

What should your stored procedure return when the input has (a) one ProjectID (b) many ProjectIDs (c) no ProjectID ? — Iain Samuel McLean Elder, Nov 26 '12 at 00:16
I think you need to read the question again there are two parameters, ProjectID and ProcessID. ProjectID is not optional where as ProcessID is. Also, user can select from a list all the ProcessId's, One ProcessID or none. Read and Understand the question before you downvote> — Abhi.Net, Nov 26 '12 at 00:30
Sorry, I got confused. It's still not clear what your query should do when the input has (a) one ProcessID (b) many ProcessIDs (c) no ProcessIDs. — Iain Samuel McLean Elder, Nov 26 '12 at 00:45
What the query should do is, return data for One ProcessID if only one is selected, Return data for multiple ProcessID's if multiuple are selected and If none is slected return complete dataset. Now in my question I have deliberately missed the rest of the query cos I only wanted to concentrate on the Where Clause with optional parameters. Also would really appreciate if you could remove your downvote so that more people can see this question and give their inputs. — Abhi.Net, Nov 26 '12 at 01:02

score 1 · Accepted Answer · edited Dec 05 '13 at 21:17

I have finally figured out how to achieve this. There are a couple of ways to do this, what I am using now is a function to split a string of ProcessID's based on delimiter and Then Inserting them into a table. Then using that table in my stored proc. Here is the code and the link to the webpage.

http://www.codeproject.com/Articles/58780/Techniques-for-In-Clause-and-SQL-Server

CREATE FUNCTION [dbo].[ufnDelimitedBigIntToTable]
(
@List varchar(max), @Delimiter varchar(10)
)
RETURNS @Ids TABLE
(Id bigint) AS
BEGIN
DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)
SET @List = LTRIM(RTRIM(@List)) + @Delimiter
SET @pos = CHARINDEX(@Delimiter, @List, 1)
WHILE @pos > 0
    BEGIN
    SET @list1 = LTRIM(RTRIM(LEFT(@List, @pos - 1)))
    IF @list1 <> ''
        INSERT INTO @Ids(Id) VALUES (CAST(@list1 AS bigint))
    SET @List = SUBSTRING(@List, @pos+1, LEN(@List))
    SET @pos = CHARINDEX(@Delimiter, @list, 1)
END
RETURN 
END

Once made, the table-function can be used in a query:

 Collapse | Copy Code
CREATE PROCEDURE [dbo].[GetUsingDelimitedFunctionTable]
@Ids varchar(max)
AS
BEGIN
SET NOCOUNT ON
SELECT s.Id,s.SomeString 
FROM SomeString s (NOLOCK)
WHERE EXISTS ( SELECT *
               FROM ufnDelimitedBigIntToTable(@Ids,',') Ids
               WHERE s.Id = Ids.id )
END

The Link also provides more ways to achieve this.

The good thing about using a function is that chances are you might have to do something similar in the future and having a function would make it a lot easier than writing redundant code. — Abhi.Net, Nov 26 '12 at 23:42
I'm using a very similar approach but when I pass `NULL` or `''` as the Ids, I don't get any results and I expect to get everything. What am I'm missing? — empz, Mar 08 '19 at 20:47

score 0 · Answer 2 · answered Nov 25 '12 at 23:23

0

Not the best, but one way is to convert both sides to "varchar" and use "Like" operator to compare them. It doesn't need any huge modifications, just change the datatype of your parameter to "varchar". Something like the code below:

'%[,]' + Convert(varchar(10), tbl_ProcessSubStep.ProcessID) + '[,]%' Like @ProcessIDs

Hope it helps.

answered Nov 25 '12 at 23:23

Rikki

3,338
1
22
34

1

Treating strings as integers is probably the wrong way to go. But if you are tempted, see Joel Spolsky's [technique to construct a list of search values](http://stackoverflow.com/a/337817/111424), with all its caveats. – Iain Samuel McLean Elder Nov 26 '12 at 00:00
@isme Thank you for mentioning that. As I said it's not the best way, but it has the smallest amount of modifications. Cheers – Rikki Nov 26 '12 at 05:25

score 0 · Answer 3 · answered Nov 26 '12 at 23:51

You didn't specify your database product in your question, but I'm going to guess from the @Pararemter naming style that you're using SQL Server.

Except for the unusual requirement of interpreting empty input to mean 'all', this a restatement of the problem of Arrays in SQL, explored throughly by Erland Sommarskog. Read all his articles on the subject for a good analysis of all the techniques you can use.

Here I'll explain how to use a table-valued parameter to solve your problem.

Execute the following scripts all together to set up the test environment in an idempotent way.

Creating a sample solution

First create a new empty test database StackOverFlow13556628:

USE master;
GO

IF DB_ID('StackOverFlow13556628') IS NOT NULL
BEGIN
  ALTER DATABASE StackOverFlow13556628 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DROP DATABASE StackOverFlow13556628;
END;
GO

CREATE DATABASE StackOverFlow13556628;
GO

USE StackOverFlow13556628;
GO

Next, create a user-defined table type PrinciapalList with one column principal_id. This type contains the input values with which to query the system table sys.database_principals.

CREATE TYPE PrincipalList AS TABLE (
  principal_id INT NOT NULL PRIMARY KEY
);
GO

After that, create the stored procedure GetPrincipals which takes a PrincipalList table-valued parameter as input, and returns a result set from sys.database_principals.

CREATE PROCEDURE GetPrincipals (
  @principal_ids PrincipalList READONLY
)
AS
BEGIN
  IF EXISTS(SELECT * FROM @principal_ids)
  BEGIN
    SELECT *
    FROM sys.database_principals
    WHERE principal_id IN (
      SELECT principal_id
      FROM @principal_ids
    );
  END
  ELSE
  BEGIN
    SELECT *
  FROM sys.database_principals;
  END;
END;
GO

If the table-valued parameter contains rows, then the procedure returns all the rows in sys.database_principals that have a matching principal_id value. If the table-valued parameter is empty, it returns all the rows.

Testing the solution

You can query multiple principals like this:

DECLARE @principals PrincipalList;

INSERT INTO @principals (principal_id) VALUES (1);
INSERT INTO @principals (principal_id) VALUES (2); 
INSERT INTO @principals (principal_id) VALUES (3);

EXECUTE GetPrincipals
  @principal_ids = @principals;
GO

Result:

principal_id    name
1   dbo
2   guest
3   INFORMATION_SCHEMA

You can query a single principal like this:

DECLARE @principals PrincipalList;

INSERT INTO @principals (principal_id) VALUES (1);

EXECUTE GetPrincipals
  @principal_ids = @principals;
GO

Result:

principal_id    name
1   dbo

You can query all principals like this:

EXECUTE GetPrincipals;

Result:

principal_id    name
0   public
1   dbo
2   guest
3   INFORMATION_SCHEMA
4   sys
16384   db_owner
16385   db_accessadmin
16386   db_securityadmin
16387   db_ddladmin
16389   db_backupoperator
16390   db_datareader
16391   db_datawriter
16392   db_denydatareader
16393   db_denydatawriter

Remarks

This solution is inefficient because you always have to read from the table-valued parameter twice. In practice, unless your table-valued parameter has millions of rows, it will probably not be the major bottleneck.

Using an empty table-valued parameter in this way feels unintuitive. A more obvious design might simply be to have two stored procedures - one that returns all the rows, and one that returns only rows with matching ids. It would be up to the calling application to choose which one to call.

I actually do have 2 stored Procedures. My project is rather a complex one so I cant explain all the options user has(Multiple dropdowns that are inter-related),but I am using my original stored proc if there is only one ProcessID, in case the user selects multiple processID and then certain options in other dropdown lists , I am using this stored proc with the function. But like I said it all depends upon your requirements. There are other options in that link. Also, I haven't gone through your solution and comments in detail yet,Once I do I'll publish my remarks.BTW Thank you for your input. — Abhi.Net, Nov 27 '12 at 00:01

IN-clause with optional parameter SQL

3 Answers3

Creating a sample solution

Testing the solution

Remarks