Invalid use of a side-effecting operator 'rand' within a function

• Little Nick

  SSCrazy

  Points: 2202

  More actions

  February 18, 2019 at 5:19 am

  #382760

  I want create a Function as follow,
  
CREATE function [dbo].[GetSmsId]()
returns INT as
BEGIN
  
---- Create the variables for the random number generation
DECLARE @random INT;
DECLARE @Upper INT;
DECLARE @Lower INT

  ---- This will create a random number between 1 and 999
SET @Lower = 10000 ---- The lowest random number
SET @Upper = 99999 ---- The highest random number
SELECT @random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
--SELECT @random
  
  return @random
end

  Unfortunately, I receive an error -- Invalid use of a side-effecting operator 'rand' within a function.

  Please help

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  February 18, 2019 at 5:42 am

  #2022265

  Little Nick - Monday, February 18, 2019 5:19 AM

  I want create a Function as follow,
  
  CREATE function [dbo].[GetSmsId]()
  returns INT as
  BEGIN
    
  ---- Create the variables for the random number generation
  DECLARE @random INT;
  DECLARE @Upper INT;
  DECLARE @Lower INT

  ---- This will create a random number between 1 and 999
  SET @Lower = 10000 ---- The lowest random number
  SET @Upper = 99999 ---- The highest random number
  SELECT @random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
  --SELECT @random
    
    return @random
  end

  Unfortunately, I receive an error -- Invalid use of a side-effecting operator 'rand' within a function.

  Please help

  Rand function can't be used directly along with UDF.

  
CREATE VIEW rndView
AS
SELECT RAND() rndResult;

  Run view and function separately.

  CREATE function [dbo].[GetSmsId]()
returns INT as
BEGIN

  ---- Create the variables for the random number generation
DECLARE @random INT;
DECLARE @Upper INT;
DECLARE @Lower INT

  ---- This will create a random number between 1 and 999
SET @Lower = 10000 ---- The lowest random number
SET @Upper = 99999 ---- The highest random number
SELECT @random = ROUND(((@Upper - @Lower -1) * rndResult + @Lower), 0)
from rndView
--SELECT @random

  return @random
end


  For more details kindly refer below link:

  https://blog.sqlauthority.com/2012/11/20/sql-server-using-rand-in-user-defined-functions-udf/

  Saravanan

• ScottPletcher

  SSC Guru

  Points: 100947

  More actions

  February 18, 2019 at 8:12 am

  #2022278

  Pass the RAND value in.  And, for efficiency, get rid of the local variables in the function.
  
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

  CREATE function [dbo].[GetSmsId](
  @rand float
)
returns INT as
BEGIN
RETURN (
    SELECT ROUND(((Upper - Lower - 1) * @rand - Lower), 0)
    FROM (
        SELECT Lower = 1000, Upper = 9999
    ) AS control_values
)
end
GO
SELECT dbo.GetSmsId(RAND())
SELECT dbo.GetSmsId(RAND())
SELECT dbo.GetSmsId(RAND())

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.