SQL Server - filter query by variable number of parameters

Question

The scenario:

I have a stored procedure (spFetch) that I want to return a list of results from a table.
The stored procedure (spFetch) defines a number of parameters that may or may not be passed in by the caller. If parameter values are not passed in by the caller they default to null.
If any of these parameters are not null, they should be used in the where clause of the select statement in (spFetch) to return filtered results.
I have a front end app that will act the caller, but it is not set in stone as to how the parameters should be passed (comma seperated list, explicit reference, etc)

The idea here is that, on the back end I can limit the possible query parameters, while still allowing a wide range of parameters.

How do i write spFetch so that it meets all the requirements of the above scenario?

There has to be a best practice out there for this scenerio, its crazy to think other devs arn't doing this already right?

Most aren't doing it with a comma separated list cause you'll have to then split that list. Look into table value parameters to go along with the kitchen sink link from @LukaszSzozda — S3S, Jun 13 '18 at 18:35
just a side note, you normally don't want to name stored procedures starting with sp as it _could_ conflict with a system one which starts with sp_ so just name yours usp_ or something — sniperd, Jun 13 '18 at 18:39
Kitchen sink is definitely the answer, awesome solution! @Lukasz Szozda, want to officially add it as an answer so I can mark right? — Jamie Marshall, Jun 13 '18 at 19:59
@JamieMarshall there are a bunch of built in stored procedures, such as sp_addarticle so you wouldn't want to make a user created stored procedure with the same name. So if you _always_ just name your stored procedures with usp_ or some other prefix you won't have a conflict. — sniperd, Jun 14 '18 at 12:37
@JamieMarshall I think you'd be OK with sp, I just like having it be very different in part so if I have to do mass updates or sorting it's easy. But yes it looks like sp is OK but perhaps not preferable: https://stackoverflow.com/questions/20530211/avoid-naming-user-stored-procedures-sp-or-sp — sniperd, Jun 14 '18 at 18:09

score 1 · Accepted Answer · edited Jun 13 '18 at 23:37

CREATE PROCEDURE [dbo].[p_Search] ( @Name sysname = NULL, @Objectid INT  = NULL, @schemaId INT  = NULL )
AS
BEGIN
    SELECT
        [name]
       ,[object_id]
       ,[principal_id]
       ,[schema_id]
       ,[parent_object_id]
       ,[type]
       ,[type_desc]
       ,[create_date]
       ,[modify_date]
       ,[is_ms_shipped]
       ,[is_published]
       ,[is_schema_published]
    FROM
        [sys].[objects]
    WHERE 1                                = 1
          AND [name]                         = ISNULL(@Name, [name])
          AND ISNULL(@Objectid, [object_id]) = [object_id]
          AND ISNULL(@schemaId, [schema_id]) = [schema_id];
END;

EXEC p_search @Name = 'sysallocunits'

Thanks @amy-b for the formatting ; still trying to figure out the formatting options — SQLApostle, Jun 14 '18 at 00:02

SQL Server - filter query by variable number of parameters

1 Answers1