SQL Server - filter query by variable number of parameters

Question

The scenario:

1. I have a stored procedure (spFetch) that I want to return a list of results from a table.
2. 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.
3. 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.
4. 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?

Answer 1

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'