SQL: select max value... In row, across columns

you will need a stored function for that, this version with 5 arguments:

as functions don't allow optional arguments, I would create a function with 15 arguments, and pass in your example 2 null values at the end.

create function dbo.Highest_05(@val1 sql_variant, @val2 sql_Variant, @val3 sql_variant, @val4 sql_variant, @val5 sql_Variant) returns sql_variant
as
begin
  declare @t table(data sql_Variant)
  declare @res sql_Variant
  insert into @t values(@val1)
  insert into @t values(@val2)
  insert into @t values(@val3)
  insert into @t values(@val4)
  insert into @t values(@val5)
  select @res = max(data) from @t
  return @res
end
go
select dbo.Highest_05(1,2,3,'A', null)
go
drop function dbo.Highest_05

Select allOpen in new window

try this create a function and use it as follows, i used 4 params you can expand it

Create FUNCTION [dbo].[Greatest]
(
@val1 int,
@val2 int,
@val3 int,
@val4 int
)
RETURNS int
AS
BEGIN
      -- Declare the return variable here
      DECLARE @ResultVar int

      -- Add the T-SQL statements to compute the return value here
      SELECT @ResultVar = max(MyVal) From
            (Select @val1 MyVal
            union Select @val2
            union Select @val3
            union Select @val4) A

      -- Return the result of the function
      RETURN @ResultVar

END


Select Col1,Col2,Col3,Col4,
dbo.Greatest(Col1,Col2,Col3,Col4)
from dbo.TestTab

select max(col1),max(col2),max(col3),max(col4),max(col5),max(col6) from yourtablename
 
must help.

sorry I misunderstood your need

this link will give how u can make use of nest the functions
<http://articles.techrepublic.com.com/5100-10878_11-6162188.html#>
<http://techrepublic.com.com/html/tr/sidebars/6162188-0.html>

Functions have fairly significant overhead anyway, so should always be written as efficiently as possible :-) .

Works perfectly, thank you!