sql simple maths function - am i being thick? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

sql simple maths function - am i being thick?

Author

Topic

sengoku
Starting Member

29 Posts

Posted - 2003-06-18 : 09:22:53

hi there peeps!

i might be being really stupid here, but what i'm after are these functions :

MAXIMUM
Returns the maximum of the two values supplied.
num = MAXIMUM ( value1, value2 )

MINIMUM
Returns the minimum of the two values supplied.
num = MINIMUM ( value1, value2 )

in microsoft SQL server. do they exist at all? the reason is, i want to do a query like this :

update mytable set myvalue=maximum(myvalue*10,200)

is there any way to do this? or do i need 2 queries (1 to multiply myvalue*10, the other to update mytable set myvalue=200 where myvalue>200)?

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-18 : 09:36:43

UPDATE mytable SET myvalue =
case when myvalue * 10 > 200 then myvalue * 10 else 200 end

-------
Moo.

sengoku
Starting Member

29 Posts

Posted - 2003-06-18 : 09:45:34

wicked 'n' wild, thanks mr mist :)

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-06-18 : 10:19:54

Here is a function if you really wanted to use one!

CREATE FUNCTION DBO.COMPARE(@VALUE1 BIGINT, @VALUE2 BIGINT)
RETURNS BIGINT
AS
BEGIN
RETURN ( CASE WHEN @VALUE1 > @VALUE2 THEN @VALUE1 ELSE @VALUE2 END)
END

Have a good day.

Djiff
Starting Member

3 Posts

Posted - 2003-06-18 : 10:21:19

Mr Mist suggestion is very good but if you need to use a min/max function very often you may create a "user defined function" like:

CREATE FUNCTION GetMin
(
@value1 INT ,
@value2 INT
)
RETURNS INT
AS
BEGIN
DECLARE @MinValue INT
IF (@value1<@value2)

SET @MinValue = @value1

ELSE

SET @MinValue = @value2

RETURN (@MinValue)
END

this function can then be called from any sp
;)

Djiff
Starting Member

3 Posts

Posted - 2003-06-18 : 10:23:56

Amethystium is faster than me !
you post your solution while I was composing ...

and your function is also faster than mine !

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-06-18 : 10:32:55

quote:

Amethystium is faster than me !
you post your solution while I was composing ...

and your function is also faster than mine !

You provided GetMin. If you notice, my solution provides the maximum! So he has two functions to play with now!

I think your holder variable @MinValue is not needed because you can only return one or the other. Perhaps that's the reason your function is slower (by a fraction of a millisecond)

By the way, in Query Analyzer, instead of clicking on the 'New Query' button (directly under the File menu bar), you should click on the arrow next to the button. This will give you a drop-down list of various templates for creating (tables, triggers etc). One of the options is functions which has three types of templates, Inline, Scalar and table.

It is a quick and easy way to get started and saves you having to re-type the opening lines.

Good Luck!

Edited by - Amethystium on 06/18/2003 10:33:55

Edited by - Amethystium on 06/18/2003 10:42:06

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources