getting one random row from a table inside a function- sql server

Question

i have this procedure in sql. the requirment is: if condition (for example i put 1=1 ..) then [run another procedure or function that will random from table with values one value and return it.] else 1 for example:

 case when 1=1 then (dbo.myFunc @input) else 1

this is myTable: '1','2','3' and i want this function to random from it one value

CREATE FUNCTION dbo.myFunc (@input int)
RETURNS int
AS BEGIN
    declare @rndValue int
    set @rndValue = (SELECT top 1 * FROM myTable ORDER BY newID())
    RETURN @rndValue 
END

the problem is that in function i cannot run newID(), and if i want to use procedure instead of function i cannot run it from case statement.

what are u suggest.? thanks alot.

Answer 1

I think you need a code like this:

set x = case when 1=1 then (select top(1) val from myTable order by newid()) else 1 end;

SQL Fiddle