IF or CASE STATEMENT IN WHERE CLAUSE - SPROC

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)

IF or CASE STATEMENT IN WHERE CLAUSE - SPROC

Author

Topic

dbenoit64
Starting Member

36 Posts

Posted - 2008-11-18 : 13:43:48

CREATE PROCEDURE sp_get_data

(
@station_no varchar(255) = NULL,
@meas_compare_val int(255) = NULL,
@meas_compare_type varchar(255) = NULL
)

AS

BEGIN

SET @station_list = COALESCE(RTRIM(LTRIM(@station_list)),'')

select

station_number, meas_value

from

v_extract

where

((LEN(@station_no) = 0) OR (station_number IN (SELECT * FROM udf_SplitStrings(@station_no, ',')))) AND

--all optional values are (LT, GT, EQ, LTEQ, GTEQ, NEQ)

if meas_compare_type == 'GT' Then

meas_value > @meas_compare_val

if meas_compare_type == 'LTEQ' then

meas_valu <= @meas_compare_val

IF @meas_compare_type = NULL

--ignore this statement alltogether!!!!???????

IF @meas_compare_type = ''

order by station_number

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 13:48:30

[code] CREATE PROCEDURE sp_get_data

(
@station_no varchar(255) = NULL,
@meas_compare_val int(255) = NULL,
@meas_compare_type varchar(255) = NULL
)

AS

BEGIN

SET @station_list = COALESCE(RTRIM(LTRIM(@station_list)),'')

select

station_number, meas_value

from

v_extract

where

((LEN(@station_no) = 0) OR (station_number IN (SELECT * FROM udf_SplitStrings(@station_no, ',')))) AND

--all optional values are (LT, GT, EQ, LTEQ, GTEQ, NEQ)

((meas_compare_type <> 'GT' OR meas_value > @meas_compare_val)

AND ( meas_compare_type <> 'LTEQ' OR meas_valu <= @meas_compare_val)

OR NULLIF(@meas_compare_type,'') IS NULL )

order by station_number

END
[/code]

whats the purpose of @station_list by the way?

dbenoit64
Starting Member

36 Posts

Posted - 2008-11-18 : 13:55:23

I have to write a stored procedure that does a numeber of things but the first issue I cant figure out is as follows:

take a value and a compare value (>, =, >= etc) but in words and compare a value depeding on what that is and ignore if null. The problem Im having is at the 2nd part of the where clause (i have it written in random code... not valid sql server). im not even sure if this is where it should be done.

dbenoit64
Starting Member

36 Posts

Posted - 2008-11-20 : 13:26:42

Brilliant. thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources