Minimum of 2 values

[Colin Angus Mackay]

Hello, Is there anything in SQL Server 2000 that I can use to perform a calculation returning the minimum of 2 values. For example: SET @value = min(@a, @b) I realise that there is a min() aggregate function, but I don't want that as I am wanting to use this in a computed column. (I have a value calculated from other fields, but must be capped at some limit) Any ideas?

---

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar Coming soon: The Second EuroCPian Event

[Mike Ellison]

Hi Colin. How about building your own function?

CREATE FUNCTION MinValue(@val1 decimal, @val2 decimal)
RETURNS decimal
AS
BEGIN
declare @retValue decimal;

If (@val1 < @val2)
set @retValue = @val1
Else
set @retValue = @val2

Return(@retValue);
END

[Colin Angus Mackay]

Yeah! I've done something similar to that - I was just hoping that I'd missed a nice piece of documentation that says there is a built in function for it. C'est la vie.

---

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar Coming soon: The Second EuroCPian Event

[Mike Ellison]

Well, if you missed it, so did I :)

[Peet Schultz]

Hi There Please try this (Not sure if this is what you are looking for: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table1] GO CREATE TABLE [dbo].[Table1] ( [a] [int] NULL , [b] [int] NULL , [c] AS (isnull(case when (isnull([a],0) > isnull([b],0)) then (isnull([b],0)) else (isnull([a],0)) end,0)) ) ON [PRIMARY] GO insert into [table1] values (1,3) insert into [table1] values (22,3) insert into [table1] values (1,1) select [a],[b],[c] from [table1] Selecting [c] seems to always return the smallest of [a] or [b] Regards Peet Schultz YASP