none
UDF Error: Invalid use of a side-effecting operator 'newid' within a function.

Answers

  • Please refer the below technique,

    CREATE VIEW [dbo].[Myview]
    AS
    SELECT     RAND(CAST(NEWID() AS VARBINARY)) AS RND
    ------------------------
    CREATE FUNCTION fnRandomFunction()
    RETURNS DECIMAL(10,2)
    AS
    BEGIN
    	DECLARE @RND DECIMAL(10,2)
    	set @RND= 0.0
    	SELECT @RND = RND FROM Myview
    	RETURN @RND
    END
    -------------------------
    SELECT dbo.fnRandomFunction() as Col


    Regards, RSingh

    Saturday, September 07, 2013 4:07 PM

All replies

  • I think it's not possible!

    Because if this could be possible, it breaks the definition of Function!


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog

    Saturday, September 07, 2013 3:40 PM
  • Please refer the below technique,

    CREATE VIEW [dbo].[Myview]
    AS
    SELECT     RAND(CAST(NEWID() AS VARBINARY)) AS RND
    ------------------------
    CREATE FUNCTION fnRandomFunction()
    RETURNS DECIMAL(10,2)
    AS
    BEGIN
    	DECLARE @RND DECIMAL(10,2)
    	set @RND= 0.0
    	SELECT @RND = RND FROM Myview
    	RETURN @RND
    END
    -------------------------
    SELECT dbo.fnRandomFunction() as Col


    Regards, RSingh

    Saturday, September 07, 2013 4:07 PM
  • Nice trick! It's not really within the function but it's awesome!

    sqldevelop.wordpress.com

    Saturday, September 07, 2013 6:59 PM