Quick Select statement w/ param Question

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 2005 Forums

Transact-SQL (2005)

Quick Select statement w/ param Question

Author

Topic

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-07-27 : 15:57:59

I have the below table. I am having trouble with a statement that includes a parameter (@ColA). Possible values for @ColA are: AZT, MGP or nothing at all. If nothing at all is entered I want to return every row. If AZT is select I want only the 1st row and if likewise for MGP (not the rows where ColA is blank).

ColA ColB ColC
AZT BBB CCC
YTO JAD
MGP BBC CBT
ADD MPD

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 16:06:24

[code]
...
WHERE ColA = @ColA
OR @ColA IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-27 : 16:06:29

[code]WHERE ( @ColA IS NULL OR @colA = ColA)[/code]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-27 : 16:07:20

yet another way: This assumes "nothing at all" means NULL. If it is an empty string rather than NULL then use the second option below:

where ColA = coalesce(@ColA, ColA)

where ColA = coalesce(nullif(@ColA,''), ColA)

Be One with the Optimizer
TG

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-07-27 : 16:14:30

WHERE ColA = @ColA
OR @ColA IS NULL

Will not allow me to return all rows. I need to be able to return all rows somehow.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 16:34:34

quote:
Originally posted by darms21

WHERE ColA = @ColA
OR @ColA IS NULL

Will not allow me to return all rows. I need to be able to return all rows somehow.

why?
what will you be passing as parameter when you say 'nothing at all'?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-27 : 17:06:47

Just in case.. the WHERE clause should be:

WHERE ( @ColA IS NULL OR @colA = ColA)

It should not be:

WHERE ( ColA IS NULL OR @colA = ColA)

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-07-30 : 11:02:32

Hmm. I am still not getting the results I want. I will be giving the user the ability to select from a few possibe values for @ColA, one for each value and one value that will return every row regardless of the value in ColA. How can I do that? What value should @ColA be inorder to return every row?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-30 : 11:40:39

If it is not working as you expect from the client program, try to run the same query in a SSMS window. You can try the two cases below, and they should return all rows.

----------------------------------------------------
-- 1. This will work if @ColA is NULL.
declare @colA varchar(32);
set @colA = NULL;

-- your Query here with the where clause being
-- WHERE ( ColA IS NULL OR @colA = ColA)

---------------------------------------------------
-- 2. TG's query - this will work if @ColA is null or
--    if it is empty string or empty spaces.
declare @colA varchar(32);
set @colA = '';

-- your Query here with the where clause being
-- where ColA = coalesce(nullif(@ColA,''), ColA)

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-07-30 : 12:03:58

I must not be conveying my needs properly. Lets say I have a drop down list that the end-user is going to select from. Whatever value is selected will become the @ColA parameter. Is it possible to create a value that when selected will return every row?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-30 : 12:23:43

Most of the drop down list type of controls allow you to include a blank row as one of the possible selections. You could use that. Alternatively, you can use an impossible value to signify that that is meant to "select all". For example, if your drop down list was like this:

Please select a state:
ALL STATES
Alabama
Alaska
Arizona
...
Wisconsin
Wyoming

Then your where clause should be like this:

WHERE (@ColA = 'ALL STATES' OR @ColA = ColA)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 12:54:36

quote:
Originally posted by darms21

I must not be conveying my needs properly. Lets say I have a drop down list that the end-user is going to select from. Whatever value is selected will become the @ColA parameter. Is it possible to create a value that when selected will return every row?

i think issue you're facing is not passing value correctly to db query or not writing query appropriately to bypass filter when your all row value is passed.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources