CODEWITHSUNDEEP
c#sqlsql-servert-sql
user3362053
user3362053

Reputation:

How to write tsql search function with a variable or unknown amount of parameters?

So I'm trying to write a t-sql function that will search a database for certain fields but only if the fields aren't empty. So in the query below, my c# call will pass these parameters in but if I don't want to pass in one of the parameters or one of them is null, I would like it to still search but only by the fields that aren't null (Basically excluded from the where clause). Is there an easy way to do this? or will i have to make separate queries for every combination of inputs?? 

CREATE FUNCTION [dbo].[Search]
(
    @firstname VarChar(50),
    @lastname VarChar(80),
    @userId VarChar(50),
    @vehicle VarChar(50)
)
RETURNS @returntable TABLE
(
    firstname VarChar(50),
    lastname VarChar(80),
    userId VarChar(50),
    vehicle VarChar(50),
    passengers VarChar(50)
)
AS
BEGIN
    INSERT @returntable
    SELECT * FROM Drivers
    Where firstname = @firstname and lastname = @lastname and 
          userid = @userId and vehicle = @vehicle
RETURN
END

Upvotes: 1

Views: 92

Answers (2)

ASh
ASh

Reputation: 35730

you can skip filters which are null

CREATE FUNCTION [dbo].[Search]
(   
    @firstname VarChar(50),
    @lastname VarChar(80),
    @userId VarChar(50),
    @vehicle VarChar(50)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT * FROM Drivers
    Where 
    (@firstname is null or firstname = @firstname)
    and (@lastname is null or lastname = @lastname)
    and (@userId is null or userid = @userId)
    and (@vehicle is null or vehicle = @vehicle)
)

Upvotes: 2

Steven
Steven

Reputation: 911

This will allow for ignoring the parameters that are set to NULL.

CREATE FUNCTION [dbo].[Search]
(
    @firstname VarChar(50),
    @lastname VarChar(80),
    @userId VarChar(50),
    @vehicle VarChar(50)
)
RETURNS @returntable TABLE
(
    firstname VarChar(50),
    lastname VarChar(80),
    userId VarChar(50),
    vehicle VarChar(50),
    passengers VarChar(50)
)
AS
BEGIN
    INSERT @returntable
    SELECT * FROM Drivers
    Where (@firstname IS NULL OR firstname = @firstname) AND 
          (@lastname IS NULL OR lastname = @lastname) AND  
          (@userId IS NULL OR userid = @userId) AND 
          (@vehicle IS NULL OR vehicle = @vehicle)
RETURN
END

Upvotes: 1

Related Questions