How to use NEWID() in SQL Server User Defined Functions (UDF)

NEWID() function cannot be called directly from a user-defined function.
If you try to create a function using the following code:

CREATE FUNCTION RandomFirstName()
RETURNS nvarchar(50)
AS
BEGIN
RETURN (SELECT TOP 1 FirstName FROM Person.Person ORDER BY NewId())
END
GO

...you will get an error message:

Msg 443, Level 16, State 1, Procedure RandomFirstName, Line 5
Invalid use of a side-effecting operator 'newid' within a function.

invalid_use_of_newid_error

To avoid the error create a view that calls the NEWID() function:

CREATE VIEW Get_NewID
AS
SELECT NEWID() AS MyNewID
GO

And use the view to call the NEWID function indirectly:

CREATE FUNCTION RandomFirstName()
RETURNS nvarchar(50)
AS
BEGIN
RETURN (SELECT TOP 1 FirstName FROM Person.Person
ORDER BY (SELECT [MyNewId] FROM Get_NewID))
END
GO

The function is now created successfully:

function_call_newid_from_view

To use this user-defined function that gives us a random first name from the Person.Person table:

SELECT dbo.RandomFirstName()

random_first_name

Read related blog post:
How to use RAND() in SQL Server User Defined Functions (UDF)

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">