Parameters that are empty

Question

Post reply

Parameters that are empty

Richard Holloway

SSC Enthusiast

Points: 190
More actions
September 16, 2010 at 12:15 pm

#227997

I have multi selection boxes in my search page that a person can select some or not when submitting a search. It works if everybox is selected and a value passed in the parameter but wanted to see how I could resolve the parameter if that value is not filled. I am trying the included code in my where clause but can't seem to make it work either. @pm is passed as '' which I don't want but rather not include @pm in the where if its got nothing passed to it. Make sense????
AND
Len(IsNull(@PM,'')) = 0
OR
CHARINDEX( '~' + PM1.PM70.Projects.Project_Manager + '~', @pm) > 0

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Richard Holloway

It does help if you post your forum question including table definition(s),

some sample data, and your complete procedure. Follow the first link in my signature block to learn how you can do this quickly and easily.

With that information I am sure some one will give you a tested answer to your question.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

What's the environement? SSRS or "normal" application?

Richard Holloway SSC Enthusiast Points: 190 More actions · Answer 3

Normal environment. The @pm is a nvarchar(I set to 4000) just in case a person selected quite a few from the listbox. I can post the whole sp if needed and my test params I am using.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Richard Holloway (9/16/2010)
Normal environment. The @pm is a nvarchar(I set to 4000) just in case a person selected quite a few from the listbox. I can post the whole sp if needed and my test params I am using.

And you table definitions, sample data and desired results

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Richard Holloway SSC Enthusiast Points: 190 More actions · Answer 5

I think I got it figured out. Kind of using DUAL in Oracle. I found this thread while searching some more after posting. http://www.sqlservercentral.com/Forums/Topic776445-9-1.aspx and utilized the statement

AND PM1.PM70.Company_List.Company_Name = (case when @Company is null then PM1.PM70.Company_List.Company_Name else @Company end)

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Actually your original approach was more efficient:

AND (@Company is null OR PM1.PM70.Company_List.Company_Name = @Company)

_____________
Code for TallyGenerator

Richard Holloway SSC Enthusiast Points: 190 More actions · Answer 7

It would be but I am passing in a delimited parameter. These Params are filled from the user making multiple selections from the listbox or if no selection is made I just pass NULL.

@pm = '~JKB~~DMW~'

mtassin SSC-Insane Points: 23099 More actions · Answer 8

If the parameters are optional... dynamic SQL is your best bet.

http://www.sommarskog.se/dyn-search-2005.html

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

I've done this with SQL 2000, of course it's much easier with SQL2005+ because you can using nvarchar(max) to hold the query, but you can write some pretty complicated stuff with nvarchar(4000).

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Richard Holloway SSC Enthusiast Points: 190 More actions · Answer 9

Richard Holloway

SSC Enthusiast

Points: 190

September 17, 2010 at 10:25 am

#1223161

Cool will look into that. I am using 2005.