CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlstored-proceduresreporting-services
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3303

Using Optional parameters when incoming value is null?

I have a stored procedure that looks like the following.

Let's say I call the procedure usp_Exclusion 'Joe', null, 'Bob', what would I need to do in the stored procedure so that the null value changes to a string (in this case, '')? Unless it's the only way, I don't want to go through every parameter and set to a string if it's null.

ALTER procedure dbo.usp_Exclusion
(    
    @exclude1 varchar(100) = '',
    @exclude2 varchar(100) = '',
    @exclude3 varchar(100) = ''
)

as

declare @table table 
(
    EmpName varchar(20),
    EmpLast varchar(20)
)

insert into @table (empName, emplast) 
values 
('Joe', 'Smith'),
('Jim', 'Neumann'),
('Bob', 'Bright'),
('James', 'Dung')

SELECT * from @table where EmpName not in (isnull(@exclude1, ''), isnull(@exclude2, ''), isnull(@exclude3, ''))
--SELECT * from @table where EmpName not in (@exclude1, @exclude2, @exclude3)

The reason I ask is because this SP is being called from an SSRS report, and the parameters by default are empty so the report loads without any user interaction. The only way I can make it work is if I set each parameter in the report to '' (an empty space).

The only issue with that is that it's cumbersome when someone's going to type in something in the parameter; they don't know there's already a space there.

And the reason I'm doing this is because the IN clause can't have a null value.

Thanks.

I can always use ISNULL in the IN clause.

Upvotes: 1

Views: 620

Answers (2)

Lucky
Lucky

Reputation: 4493

EDIT: Better solution, thanks to pmbAustin

@exclude_new_1 = COALESCE(@exclude1, '');

OLD ANSWER:

Set new parameters with case statement

@exclude_new_1 = CASE WHEN (@exclude1 IS NULL) THEN '' ELSE @exclude1 END

use the new parameters in your select. Should do the job...

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

You can try something like below

SELECT * from @table T LEFT JOIN 
(VALUES (@exclude1),(@exclude2), (@exclude3)) as E(excludeName) ON
T.EmpName =isnull(E.excludeName, '')
WHERE E.excludeName is NULL

Sample output: enter image description here

Explanation: We use Values constructor syntax to create a dynamic table and LEFT JOIN onto @table to get all values with ISNULL clause, and then finally use WHERE to effectively use NOT IN criteria.

Upvotes: 0

Related Questions