default value question - SQL Server Forums

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

Transact-SQL (2000)

default value question

Author

Topic

BitShift
Yak Posting Veteran

98 Posts

Posted - 2006-10-11 : 12:38:47

I have a stored procedure that takes two parameters. I want the 2nd to be optional.

Later this parameter is used in the where clause:
where... something = @param2

This @param2 can either be 'a' or 'b'

What id like to do is, if the parameter is null, then alter the where caluse like so:
where something = a or something = b

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-11 : 12:43:35

create proc a
@p1 char(1) ,
@p2 char(1) = null
as

where (@p2 is null and something in ('a','b'))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BitShift
Yak Posting Veteran

98 Posts

Posted - 2006-10-11 : 12:47:25

Will this let me pass in a value, as well as return for the whole list ( a and b) ?

In other words, I want to return the results for either the parameter value that was passed in, or if null, the whole thing which is for both

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-12 : 01:01:32

quote:
Originally posted by BitShift

Will this let me pass in a value, as well as return for the whole list ( a and b) ?

In other words, I want to return the results for either the parameter value that was passed in, or if null, the whole thing which is for both

where 1 = 	
		(case
			when @param2 is not null 
			then 	case
						when something = @param2 
						then 1 
						else 0 
					end
				else
					case
						when something in ('a', 'b') 
						then 1
						else 0
					end
		end)

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 02:49:49

Why complicate things?

DECLARE @Test TABLE (Something VARCHAR(2))

INSERT	@Test
SELECT	'A' UNION ALL
SELECT	'C' UNION ALL
SELECT	'B' UNION ALL
SELECT	'L' UNION ALL
SELECT	NULL UNION ALL
SELECT	'F'

declare @param1 varchar(2), @param2 varchar(2)

select	@param1 = 'a', @param2 = null

select * from @test
where something in (@param1, @param2)

Peter Larsson
Helsingborg, Sweden

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-12 : 10:17:01

You are getting several answers here because your question isn't clear

I think you are looking for something like this:

Define your param as:

@param2 char(1) = null

Write your predicate like this:

where
   ((@param2 = something and
     @param2 is not null) or
    (something in ('a','b') and
     @param2 is null))

Jay White

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 10:44:28

Hmmm?
@Param2 IN ('a', 'b') and @param2 is null

Peter Larsson
Helsingborg, Sweden

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-12 : 10:57:22

I have no idea what you are talking about :)

Jay White

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources