Stored Procedure with multiple optional parameters

Question

0.00/5 (No votes)

See more:

Hello,

In my stored procedure, user can give input as only serviceId and some time both serviceid and personId,

I tired like this, But it is not coming, Please help me.

SQL

WHERE StatusId != 3 AND

(@ServiceId IS NULL OR ServiceId=@ServiceId)

AND (PersonId IS NULL OR PersonId=@PersonId)

Regards
Nagaraj.J

Posted 26-May-14 8:18am

Member 10454499

Updated 26-May-14 8:52am

Nelek

v2

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-05-26T08:23:00

Solution 1

Try another atsign.
Change

SQL

AND (PersonId IS NULL OR PersonId=@PersonId)

To

SQL

AND (@PersonId IS NULL OR PersonId=@PersonId)

Posted 26-May-14 8:23am

OriginalGriff

v2

Comments

Member 10454499 26-May-14 14:51pm

Now, only passing two values the data is coming.

Maciej Los · Answer 2 · 2014-05-26T11:22:00

Try dynamic query:

SQL

DECLARE @sql VARCHAR(MAX)

SET @sql = 'SELECT <FieldList> FROM TableName WHERE StatusId !=3'

IF (@ServiceId IS NULL) 
    SET @sql = @sql + ' AND ServiceId=' + @ServiceId
 
IF (@PersonId IS NULL) 
    SET @sql = @sql + ' AND PersonId=' + @PersonId

EXEC(@sql)

For furter information, please see:
Building Dynamic SQL In a Stored Procedure[^]
How to Generate T-SQL Code by Using Dynamic SQL?[^]
Dynamic ORDER BY with multiple criteria in T-SQL[^]

Stored Procedure with multiple optional parameters

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Stored Procedure with multiple optional parameters

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us