Determining which query should i use....

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

Determining which query should i use....

Author

Topic

SD_Monkey
Starting Member

38 Posts

Posted - 2010-06-25 : 11:09:08

i have a table name TRAN_EMP
i create a user defined function but i cant figured out...

here my sample table


idno emp_from empto
---------------------
1    10        100
2    101      202
3    203      304
4    305       0

i use between but i find difficulty to identify the last row
where may condition should be >= 305

and the rest is between emp_from and emp_to

here is a sample if my parameter is @amount=99

Select * from TRAN_EMP where @amount between emp_from and emp_to

if my parameter amount is greater than or equal to 305...
condition should be like this one

Select * from TRAN_EMP where emp_from >=@amount

my problem is how can identify what query should i use in the user defined function...

hoping for quick response...

thank you..

A maze make you much more better

using MS SQL Server 2000

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-25 : 14:13:48

When EmpTo is zero that represetns a unknown value, so it should be considered, in essence, the MAX value that that data type can hold? For example:

DECLARE @TRAN_EMP TABLE(idno INT, emp_from INT, empto INT)
INSERT @Tran_emp 
SELECT 1,    10,        100
UNION ALL SELECT 2,    101,      202
UNION ALL SELECT 3,    203,     304
UNION ALL SELECT 4,    305,       0


DECLARE @amount INT 
SET @Amount = 1000


SELECT *
FROM @Tran_Emp
WHERE @Amount BETWEEN emp_from AND COALESCE(NULLIF(empto, 0), 2147483647)

SD_Monkey
Starting Member

38 Posts

Posted - 2010-06-27 : 11:00:39

what i mean i only send the parameter of amount AND range it to the emp_from and emp_to...

tnx for your reply... i figured it out...


DECLARE @IDNO MONEY


set @IDNO = (Select idno from TRAN_EMP where @AMOUNT BETWEEN EMP_FROM AND EMP_TO)

IF @IDNO IS NULL
BEGIN
SET @IDNO = (select idno from TRAN_EMP where EMP_FROM <= @AMOUNT)
END

TNX AGAIN....

A maze make you much more better

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-28 : 13:16:45

quote:
Originally posted by SD_Monkey

what i mean i only send the parameter of amount AND range it to the emp_from and emp_to...

tnx for your reply... i figured it out...

DECLARE @IDNO MONEY

set @IDNO = (Select idno from TRAN_EMP where @AMOUNT BETWEEN EMP_FROM AND EMP_TO)

IF @IDNO IS NULL
BEGIN
SET @IDNO = (select idno from TRAN_EMP where EMP_FROM <= @AMOUNT)
END

TNX AGAIN....

A maze make you much more better

Just incase you didn't know, your second query may return multiple rows and the variable will get assigned the last one (a random row). So, if you want to code the solution in that manner, you might want to consider using the MAX fucntion or a TOP 1 with an ORDER BY.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources