• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

How to turn a query into a Function

I need to generate random data.  I have some that will generate one of the 5 FACILITY_CODEs at random:

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

It would be nice to package this into a neat little function so I can do something like this

Select RAND_FACILITY

How to wrap the above logic into a user defined function?

Thanks,

Cipriano
0
cipriano555
Asked:
cipriano555
  • 12
  • 4
  • 3
1 Solution
 
Scott PletcherSenior DBACommented:
SQL may circumvent you there.  It likely won't call the function multiple times with the same input; that is, you will get the same value back for every row from the function.

Since NEWID() is generated separately every time it is called, you might be able to "fool" SQL by passing in the NEWID().

So:

CREATE FUNCTION dbo.RAND_FACILITY (
    @rand_value uniqueidentifier
)
RETURNS varbinary(??)  --<<-- put desired length here!
AS
BEGIN
RETURN (
    SELECT 5 * CAST(@rand_value AS varbinary()) --<<-- put correct length here!
)
END --FUNCTION
0
 
cipriano555Author Commented:
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
0
 
cipriano555Author Commented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
cipriano555Author Commented:
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
0
 
Scott PletcherSenior DBACommented:
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

Open in new window

0
 
cipriano555Author Commented:
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.

0
 
cipriano555Author Commented:
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...

0
 
Scott PletcherSenior DBACommented:
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.
0
 
cipriano555Author Commented:
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
0
 
cipriano555Author Commented:
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.
0
 
cipriano555Author Commented:
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
0
 
cipriano555Author Commented:
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.
0
 
Anthony PerkinsCommented:
You cannot use a non-deterministic function in a UDF.  Both NEWID and RAND are non-determinsitic.
0
 
Anthony PerkinsCommented:
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.
0
 
cipriano555Author Commented:
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 )

0
 
cipriano555Author Commented:
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.
0
 
cipriano555Author Commented:
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.
0
 
Scott PletcherSenior DBACommented:
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.
0
 
Anthony PerkinsCommented:
>>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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now