OR - 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

General SQL Server Forums

New to SQL Server Programming

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-17 : 23:33:50

hi all...

DECLARE @paytype varchar(50)
SET @paytype = 'W'

select t.*, c.description
from HrSetTaxTable as t
left join hrsettaxcodes as c on
t.taxcode = c.taxcode
WHERE t.taxpaytype = @paytype OR ....

my problem is if @paytype is null, it returns all the data in HrSetTaxTable.

'W' = weekly
'M' = Monthly
'D' = Daily
'' = retrieves all the data from the table

Thanks.

-Ron-

"If you can only access one site on the Internet, make it SQLTeam!"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-17 : 23:39:59

quote:
my problem is if @paytype is null, it returns all the data in HrSetTaxTable.
'W' = weekly
'M' = Monthly
'D' = Daily
'' = retrieves all the data from the table

You want to return all data when @paytype IS NULL or @paytype = '' ?

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-17 : 23:42:20

'' --> is it not a NULL ?

ok. both '' and null would retrieve all the data.

Thanks.

-Ron-

"If you can only access one site on the Internet, make it SQLTeam!"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-17 : 23:44:40

[code]WHERE
(
@paytype is null
or @paytype = ''
or (@paytype = 'D' and ... )
or (@paytype = 'W' and ... )
or (@paytype = 'M' and ... )
)[/code]

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-17 : 23:48:38

Doesn't work properly. it retrieves all the data even the paytype is 'W'.

-Ron-

"If you can only access one site on the Internet, make it SQLTeam!"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-17 : 23:50:47

can you post your full query, table DDL, sample data and expected result

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-17 : 23:53:36

WHERE t.taxpaytype = @paytype OR IsNull(@paytype, '') = ''

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-18 : 00:03:47

i think its complicated to use null/''. Ill change it to 'N'

table - HrSetTaxTable
taxcode paytype
111 W
222 W
333 M
444 M
555 D
666 D

--expected result: if @paytype = 'W'
taxcode paytype
111 W
222 W

--if @paytype = 'N'
taxcode paytype
111 W
222 W
333 M
444 M
555 D
666 D

thanks

-Ron-

"If you can only access one site on the Internet, make it SQLTeam!"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-18 : 00:09:58

try this


declare @HrSetTaxTable table
(
	taxcode int,
	paytype varchar(1)
)
insert into @HrSetTaxTable
select	111, 'W' union all
select	222, 'W' union all
select	333, 'M' union all
select	444, 'M' union all
select	555, 'D' union all
select	666, 'D'

declare	@paytype varchar(1)

-- select	@paytype = NULL
-- select	@paytype = ''
select	@paytype = 'W'


select	*
from	@HrSetTaxTable
where	
(
	@paytype	is null
or	@paytype	= ''
or	paytype		= @paytype
)

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-06-18 : 00:13:28

thanks to both of u. KH and Koji.

-Ron-

"If you can only access one site on the Internet, make it SQLTeam!"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-18 : 00:16:17

OR you can use these if you are not expecting @paytype = '' and your paytype column does not contain NULL


select	*
from	@HrSetTaxTable
where	paytype		= coalesce(@paytype, paytype)

If you do want to handle @paytype = ''


select	*
from	@HrSetTaxTable
where	paytype		= coalesce(nullif(@paytype, ''), paytype)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources