how to filter results ... - 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

how to filter results ...

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2010-01-06 : 12:42:15

Hi all,

I have a stored procedure in which I want that if value 2 is passed as parameter then all rows are shown other wise if parameter value is 1 then only those rows are shown which has that column 1. if parameter value is 0 then only those rows are shown which has that column 0.
How can i do this in sql.

Regards,
Asif Hameed

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-06 : 12:46:11

Try it, I'm not sure:
where nullif(@param,2) is null or column = @param

Assuming there comes no other value than 0, 1 or 2 or maybe null.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 12:51:13

or just
where column = coalesce(nullif(@param,2),column)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-06 : 12:53:53

No canonical for this?

No, you're never too old to Yak'n'Roll if you're too young to die.

sparrow37
Posting Yak Master

148 Posts

Posted - 2010-01-06 : 13:28:35

I have used above suggestions for vehicletype and quotetype but they are not working. my stored procedure is :
ALTER PROCEDURE [dbo].[SelectAll_Rates]
(
@Origin varchar(50),
@Dest varchar(50),
@vehType int,
@quoteType int
)
AS

SET NOCOUNT ON

SELECT
[RateID],
[dbo].[fn_GetStateName]([Origin])[Origin],
[dbo].[fn_GetStateName]([Destination])[Destination],
[VehicleType],
[QuoteType],
[Rate],
[CreatedOn],
[ModifiedOn]
FROM
[Rates]

WHERE
nullif(@Origin,'0') is null or [Origin] = @Origin
and (nullif(@Dest,'0') is null or [Destination] = @Dest)
and (nullif(@vehType,2) is null or [VehicleType] = @vehType)
and (nullif(@quoteType,2) is null or [QuoteType] = @quoteType)

ORDER BY
[Origin]

and here is how i am executing it

[SelectAll_Rates] '0', '0',1,1

and it is showing all records even with vehicleype = 0 and quotetype = 0

Please suggest me solution to it.

Regards,
Asif Hameed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 13:32:57

quote:
Originally posted by sparrow37

I have used above suggestions for vehicletype and quotetype but they are not working. my stored procedure is :
ALTER PROCEDURE [dbo].[SelectAll_Rates]
(
@Origin varchar(50),
@Dest varchar(50),
@vehType int,
@quoteType int
)
AS

SET NOCOUNT ON

SELECT
[RateID],
[dbo].[fn_GetStateName]([Origin])[Origin],
[dbo].[fn_GetStateName]([Destination])[Destination],
[VehicleType],
[QuoteType],
[Rate],
[CreatedOn],
[ModifiedOn]
FROM
[Rates]

WHERE
( nullif(@Origin,'0') is null or [Origin] = @Origin
)
and (nullif(@Dest,'0') is null or [Destination] = @Dest)
and (nullif(@vehType,2) is null or [VehicleType] = @vehType)
and (nullif(@quoteType,2) is null or [QuoteType] = @quoteType)

ORDER BY
[Origin]

and here is how i am executing it

[SelectAll_Rates] '0', '0',1,1

and it is showing all records even with vehicleype = 0 and quotetype = 0

Please suggest me solution to it.

Regards,
Asif Hameed

u missed a set of braces
see above

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-07 : 02:16:24

where @param=2 or column = @param

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources