CODEWITHSUNDEEP
sqlsql-server
John Grave
John Grave

Reputation: 13

Function to select random Id

I want to select a random id where my column Guy_Checked is not 1.

Here's my code:

CREATE FUNCTION dbo.get_rand_id()  
RETURNS int   
AS   
BEGIN  
    DECLARE @ret int;  
    set @ret = (select top 1 Guy_ID
    FROM SantaGuys
    WHERE Guy_Checked <> 1
    ORDER BY RAND())
RETURN @ret;  
END;

SQL Server returns error 443 with something like...

"Invalid use of the "rand" operator, which has side effects, in a function."

What am I doing wrong?

Upvotes: 1

Views: 949

Answers (3)

Jon Jaussi
Jon Jaussi

Reputation: 1296

I offer this answer which I adapted from this post...

-- Build the table
SELECT 123 as Guy_ID
      ,0 as Guy_Checked INTO SantaGuys;

INSERT INTO SantaGuys VALUES (234, 1);
INSERT INTO SantaGuys VALUES (456, 0);
INSERT INTO SantaGuys VALUES (567, 1);

GO


-- Create a view of RAND() to work around the Invalid use of side-effecting error
CREATE VIEW v_get_rand_id
AS
SELECT RAND() as rand_id;
GO


-- Build the function with parameters that will be in your SELECT query
CREATE FUNCTION dbo.get_rand_id(@my_Guy_ID as int, @my_Guy_Checked as int)  
RETURNS float    
AS   
BEGIN  
 DECLARE @my_rand_id float; 

 SET @my_rand_id = (SELECT CASE WHEN @my_Guy_Checked <> 1 
                                THEN v.rand_id 
                                ELSE 0 END as my_rand_id 
                      FROM v_get_rand_id v)

 RETURN @my_rand_id;  

END; 

GO

-- Run your query and enjoy the results
SELECT sg.Guy_ID
      ,sg.Guy_Checked
      ,dbo.get_rand_id(sg.Guy_ID, sg.Guy_Checked) as my_rand_id
  FROM SantaGuys sg;

Here is one result...

+--------+-------------+-----------------+
| Guy_ID | Guy_Checked |   my_rand_id    |
+--------+-------------+-----------------+
|    123 |           0 | 0.5537264103585 |
|    234 |           1 |               0 |
|    456 |           0 |  0.227823559345 |
|    567 |           1 |               0 |
+--------+-------------+-----------------+

Generate ASCII tables easily from this link. Hope this helps

Upvotes: 0

Ahmed Ghoniem
Ahmed Ghoniem

Reputation: 681

you can use newid() check this it will create a unique value of type uniqueidentifier.

CREATE FUNCTION dbo.get_rand_id()  
RETURNS int   
AS   
BEGIN  
    DECLARE @ret int;  
    set @ret = (select top 1 Guy_ID
    FROM SantaGuys
    WHERE Guy_Checked <> 1
    ORDER BY NEWID())
RETURN @ret;  
END;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269943

Use NEWID() instead:

CREATE FUNCTION dbo.get_rand_id()  
RETURNS int   
AS   
BEGIN  
    DECLARE @ret int;  
    SET @ret = (SELECT top 1 Guy_ID
                FROM SantaGuys
                WHERE Guy_Checked <> 1
                ORDER BY NEWID()
               )
    RETURN @ret;  
END;

RAND() is really a constant through the course of the query -- it is evaluated once before the query starts processing. NEWID() generates a different id each time it is called.

Upvotes: 2

Related Questions