SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 case statement question		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masterdineen
Aged Yak Warrior

United Kingdom
522 Posts
Posted - 11/06/2012 :  09:07:35  Show Profile  Reply with Quote
Hello there.

i have a sp with a parameter called @type.

the @parameter will except either. 1,2 or 3

i want a case that says 1 = First, 2 second. which is the easy bit.

but when 3 is entered i want the case to include a couple of items.

eg

set @type = (select case @typeSelection
when '3' then 'first', 'second', 'third' else @typeSelection end)

The result will end up in a where clause in another select statement ie

where selection in (@type )

is there a way round this?

Regards

Rob

webfred
Flowing Fount of Yak Knowledge

Germany
8529 Posts
Posted - 11/06/2012 :  09:11:51  Show Profile  Visit webfred's Homepage  Reply with Quote
Put the case expression into the WHERE clause.
where selection in (case @typeSelection when '3' then ' ''first'', ''second'', ''third'' ' else @typeSelection end)

Too old to Rock'n'Roll too young to die.
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
522 Posts
Posted - 11/06/2012 :  09:15:38  Show Profile  Reply with Quote
Thank you
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48117 Posts
Posted - 11/06/2012 :  12:16:03  Show Profile  Reply with Quote
how will values be present n your table column selection? is it comma separated or individual values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
522 Posts
Posted - 11/07/2012 :  04:01:48  Show Profile  Reply with Quote
i am using this for a search page in an asp.net website.
so some fields are freetext and couple are drop down lists.
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
522 Posts
Posted - 11/07/2012 :  06:11:25  Show Profile  Reply with Quote
I am defining a variable as %% if the required parameter is null or ''

how do i define a variable as %% but also with '' on the outside. eg '%%'

is there a way i can do this. does the character ' have an ascii value i could use?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48117 Posts
Posted - 11/07/2012 :  09:21:55  Show Profile  Reply with Quote
quote:
Originally posted by masterdineen

I am defining a variable as %% if the required parameter is null or ''

how do i define a variable as %% but also with '' on the outside. eg '%%'

is there a way i can do this. does the character ' have an ascii value i could use?

you dont have to do that you can simply add a OR condition like


OR @param=''

so that it bypasses filter when '' and gives you all the values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000