Help with IN clause and optional parameter in PROC

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with IN clause and optional parameter in PROC

Author

Topic

Netjunkie
Starting Member

17 Posts

Posted - 2012-03-21 : 13:23:22

Hello Everyone,

I need some help with optional comma separated value to be passed to a parameter.

CREATE PROCEDURE dbo.usp_XXXXXXXXX_Search
@distance int,
@first_name varchar(35) = NULL,
@last_name varchar(35)= NULL,
@OPTIONAL_CSV_PARAMETER VARCHAR(30) = NULL

AS

BEGIN

(SELECT TOP 100 ABC, XYZ, ... etc

from Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID

LEFT JOIN TABLE3 on Table2.ID = Table3.ID and Table3.ID in
(SELECT * FROM dbo.fn_Split( @OPTIONAL_CSV_PARAMETER ,','))
)

Now if the parameter is passed with a value like '10, 20, 30', this works fine.
But if the parameter is NULL, then it doesn't return anything. which is not the expected result if the parameter is optional.

Any thoughts on how these kind of scenarios should be handled?

I do not want to go with Dynamic SQL for performance and security reasons.
Please help me with your suggestions.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-21 : 13:31:42

what do you mean it fails? Do you get an error or now results?

If you get no results, are you suppose to get everything instead?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Netjunkie
Starting Member

17 Posts

Posted - 2012-03-21 : 13:46:37

I apologize if i wasnt clear..
I mean, it doesn't return any results when it should have

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-21 : 13:53:27

Right, so when it's null return everything, right?

IF @OPTIONAL_CSV_PARAMETER IS NULL
...code query WITHOUT IN
ELSE
...code query WITH in

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Netjunkie
Starting Member

17 Posts

Posted - 2012-03-21 : 14:01:32

Brett,

I had considered this as an option but the code block is ~ 400 lines and it will have to be written twice just for this optional parameter.
Any logic changes will have to be done in both code blocks.

So i was checking to see if there is any other better approach.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-21 : 14:07:29

Can you modify the split udf?

btw

I'm guessing that the udf...wait maybe chenge the udf, so that if the input is null, then change it to be a select distinct of Table3

Huh? Huh? Not a bad idea?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-21 : 19:55:21

It probably won't perform well, but you could change the join condition a little:

LEFT JOIN 
    TABLE3 
    on Table2.ID = Table3.ID 
    and 
    (
        Table3.ID in (SELECT * FROM dbo.fn_Split( @OPTIONAL_CSV_PARAMETER ,','))
        OR @OPTIONAL_CSV_PARAMETER IS NULL
    )

Netjunkie
Starting Member

17 Posts

Posted - 2012-03-26 : 05:41:05

Performance took a serious hit with the above approach.

I guess the only simple option will be to write 2 separate code blocks to execute based on

IF @OPTIONAL_CSV_PARAMETER is null

THEN
CODEBLOCK WITHOUT IN CLAUSE
ELSE
CODEBLOCK WITH IN CLAUSE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources