How to turn a query into a Function

I have 5 facilities and a cumulative probability distribution function  (PDF) associated with them.  The code I entered returns a facility code, chosen according to the PDF.

My question is one of syntax.  I want to populate a table of data using the law reflected in the code I wrote. I wanted a function so I can select it in a query.    Your solution will not return a FACILITY_CODE so I can't use it, and it doesn't use the distribution function (which I didn't show, it is numbers in a table.)   Here is an example of something I tried, but it won't run:  

CREATE FUNCTION RAND_FACILITY
()
RETURNS VARCHAR(50)
AS
BEGIN
      DECLARE @ResultVar as VARCHAR(50)

Declare @RAND as float
 
  Select @RAND  = 5*RAND(CAST( NEWID() AS varbinary ))
 
  ;with CTE_FAC(facility_code, CUM_WEIGHT)
  AS
  (
  select facility_code, CUM_WEIGHT from DBO.dist_facility where cum_weight > @RAND
  )
 
 SELECT @ResultVar =  TOP 1 facility_code FROM CTE_FAC ORDER BY CUM_WEIGHT ASC
      RETURN @ResultVar

END
GO

I also tried this and it had a sytax error as well

CREATE FUNCTION RAND_FACILITY
()
RETURNS VARCHAR(50)
AS
BEGIN
      DECLARE @ResultVar as VARCHAR(50)

Declare @RAND as float
 
  Select @RAND  = 5*RAND(CAST( NEWID() AS varbinary ))
 
  ;with CTE_FAC(facility_code, CUM_WEIGHT)
  AS
  (
  select facility_code, CUM_WEIGHT from DBO.dist_facility where cum_weight > @RAND
  )
 

RETURN  SELECT  TOP 1 facility_code FROM CTE_FAC ORDER BY CUM_WEIGHT ASC

END
GO

This runs and returns one random (according to my probability function) facility code:

Declare @RAND as float
 
  Select @RAND  = 5*RAND(CAST( NEWID() AS varbinary ))
 
  ;with CTE_FAC(facility_code, CUM_WEIGHT)
  AS
  (
  select facility_code, CUM_WEIGHT from DBO.dist_facility where cum_weight > @RAND
  )
 
SELECT  TOP 1 facility_code FROM CTE_FAC ORDER BY CUM_WEIGHT ASC

Sorry.  Was a little too rushed earlier.  Please try function code below:

SELECT ..., dbo.RAND_FACILITY(NEWID())
FROM ...
....

CREATE FUNCTION RAND_FACILITY (
     @rand uniqueidentifier
)
RETURNS VARCHAR(50)
AS
BEGIN
RETURN (
    SELECT TOP 1 
        facility_code
    FROM 
        DBO.dist_facility
    WHERE 
        cum_weight > @RAND
    ORDER BY
        CUM_WEIGHT ASC
)
END --FUNCTION
GO

Select allOpen in new window

This error message was generated:

Msg 206, Level 16, State 2, Procedure RAND_FACILITY, Line 7
Operand type clash: uniqueidentifier is incompatible with float

It doesn't like this line:     cum_weight > @RAND

I don't see how @RAND is a random number betweeen 0 and 5.

Given that the code below works correctly...

  Declare @RAND as float
 
  Select @RAND  = 5*RAND(CAST( NEWID() AS varbinary ))
 
  ;with CTE_FAC(facility_code, CUM_WEIGHT)
  AS
  (
  select facility_code, CUM_WEIGHT from DBO.dist_facility where cum_weight > @RAND
  )
 
SELECT  TOP 1 facility_code FROM CTE_FAC ORDER BY CUM_WEIGHT ASC

... I think my main problem is I don't know how to take the result from that last select statement and make it be the return value of a function.  Should be easy, something done all the time, but I can't get any syntax to work...

Sorry.  Try this:
...
cum_weight > 5 * RAND(CAST(@rand AS varbinary))
...

The big thing is to make sure SQL re-evaluates the function for every row; that's why I'm using NEWID() as the input param -- SQL "understands" that this value changes every time.

This also works and seems a step closer...

Declare @RAND as float
DECLARE @FACILITY_CODE AS VARCHAR(50)
 
  Select @RAND  = 5*RAND(CAST( NEWID() AS varbinary ))
 
  ;with CTE_FAC(facility_code, CUM_WEIGHT)
  AS
  (
  select facility_code, CUM_WEIGHT from DBO.dist_facility where cum_weight > @RAND
  ),
  CTE_FAC2
  AS
  (
  SELECT  TOP 1 facility_code FROM CTE_FAC ORDER BY CUM_WEIGHT ASC
  )
  SELECT @FACILITY_CODE = FACILITY_CODE FROM CTE_FAC2
 
  SELECT @FACILITY_CODE

This generates an error:

CREATE FUNCTION RAND_FACILITY (
)
RETURNS VARCHAR(50)
AS
BEGIN
  Declare @RAND as float
  DECLARE @FACILITY_CODE AS VARCHAR(50)
 
  Select @RAND  = 5*RAND(CAST( NEWID() AS varbinary ))
 
  ;with CTE_FAC(facility_code, CUM_WEIGHT)
  AS
  (
  select facility_code, CUM_WEIGHT from DBO.dist_facility where cum_weight > @RAND
  ),
  CTE_FAC2
  AS
  (
  SELECT  TOP 1 facility_code FROM CTE_FAC ORDER BY CUM_WEIGHT ASC
  )
  SELECT @FACILITY_CODE = FACILITY_CODE FROM CTE_FAC2
 
  RETURN(@FACILITY_CODE)
 END;

Msg 443, Level 16, State 1, Procedure RAND_FACILITY, Line 9
Invalid use of a side-effecting operator 'newid' within a function.
Msg 443, Level 16, State 1, Procedure RAND_FACILITY, Line 11
Invalid use of a side-effecting operator 'rand' within a function.

This works, but it's not a function so it can't be selected....

CREATE PROC RAND_FACILITY
AS
BEGIN
  Declare @RAND as float
  DECLARE @FACILITY_CODE AS VARCHAR(50)
 
  Select @RAND  = 5*RAND(CAST( NEWID() AS varbinary ))
 
  ;with CTE_FAC(facility_code, CUM_WEIGHT)
  AS
  (
  select facility_code, CUM_WEIGHT from DBO.dist_facility where cum_weight > @RAND
  ),
  CTE_FAC2
  AS
  (
  SELECT  TOP 1 facility_code FROM CTE_FAC ORDER BY CUM_WEIGHT ASC
  )
  SELECT FACILITY_CODE FROM CTE_FAC2
 
 END;
 
 exec rand_facility

Scott: I tried this, but got the error below

CREATE FUNCTION RAND_FACILITY3 (
     @rand uniqueidentifier
)
RETURNS VARCHAR(50)
AS
BEGIN
RETURN (
    SELECT TOP 1
        facility_code
    FROM
        DBO.dist_facility
    WHERE
        cum_weight > 5*RAND(CAST(@rand AS varbinary))

    ORDER BY
        CUM_WEIGHT ASC
)
END --FUNCTION

Msg 443, Level 16, State 1, Procedure RAND_FACILITY3, Line 15
Invalid use of a side-effecting operator 'rand' within a function.

You cannot use a non-deterministic function in a UDF.  Both NEWID and RAND are non-determinsitic.

If you notice Scott is passing in those values (NEWID() and/or RAND()) which sidesteps the whole problem.  You should be able to do the same.

ok, this works...


CREATE FUNCTION [dbo].[RAND_FACILITY] (

@RAND AS FLOAT
)
RETURNS VARCHAR(50)
AS
BEGIN

  DECLARE @FACILITY_CODE AS VARCHAR(50)
 
  ;with CTE_FAC(facility_code, CUM_WEIGHT)
  AS
  (
  select facility_code, CUM_WEIGHT from DBO.dist_facility where cum_weight > @RAND
  ),
  CTE_FAC2
  AS
  (
  SELECT  TOP 1 facility_code FROM CTE_FAC ORDER BY CUM_WEIGHT ASC
  )
  SELECT @FACILITY_CODE = FACILITY_CODE FROM CTE_FAC2
 
  RETURN(@FACILITY_CODE)

 END;

SELECT [ManagedCareDemo].[dbo].[RAND_FACILITY] ((SELECT SUM(WEIGHT) FROM DIST_FACILITY)*RAND(CAST( NEWID() AS varbinary )

acperkins:  OK, I get it, thanks for the clarification.  I Scott's original solution I couldn't see where any random value was generated.  the point is, you can't do that in a UDF, so you generate it in the query and pass it in as an argument.  I'm used to "normal" programming languages where this kind of restriction wouldn't occur.  

Scott: thanks for your help.

Oh....and now that I'm done I'll answer my own question:

Q. How do you turn a query into a function?
A.  We assume you have some code that ends with a select statement that returns a scalar: "VAL" This gives the idea:

CREATE FUNCTION F1 (@ARG1 AS FLOAT)
AS BEGIN
  DECLARE @RETURNVAL AS VARCHAR(50)
...other code...
  SELECT @RETURNVAL = VAL FROM TABLE1
RETURN(@RETURNVAL)
END

Then you can do this:

SELECT F1(4.00)

The code in the BEGIN block cannot have any non-deterministic functions in it, such as RAND, so if you need a random number, you'll pass it in as an input argument.

No problem.

Sorry, I was in a hurry and scrambled it up anyway.

Now that I think about it, probably best to use a table-valued function that returns the result set you need; you just pass in a parameter of  5 * RAND(CAST(NEWID() AS varbinary))  to the function.

>>I'm used to "normal" programming languages where this kind of restriction wouldn't occur.<<
If you have time try reading up on T-SQL; the restriction is in there for a reason, there is nothing arbitrary about it.