November 18, 2008 at 11:42 am
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.
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
-------------THIS IS THE PART THAT I CANT GET TO WORK----------
--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!!!!???????
----------------------------------------------------
order by station_number
END
November 18, 2008 at 11:55 am
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.
November 18, 2008 at 12:10 pm
Check out CASE in BOL (Books On Line).
THe basic idea is you do something like this (Uses AdventureWorks2000 as it is what I have available at the moment):
[font="Courier New"]DECLARE @a VARCHAR(3)
SET @a = 'J'
SELECT
*
FROM
dbo.Contact
WHERE
CASE
WHEN salutation = 'Ms.' THEN
CASE WHEN FirstName LIKE @a + '%' THEN 1 ELSE 0 END
WHEN salutation = 'Mr.' THEN
CASE WHEN MiddleName LIKE @a + '%' THEN 1 ELSE 0 END
WHEN salutation = 'Sr.' THEN
CASE WHEN FirstName LIKE @a + '%' THEN 1 ELSE 0 END
ELSE 0
END = 1
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply