Use NEWID() inside function in SQL Server

In SQL Server, if we use the NEWID() function inside an user-defined function, it throws an error as “Invalid use of side-effecting or time-dependent operator in ‘newid()’ within a function.”

For any reason, if we need to use the NEWID() function inside an user-defined function, we need to do some work around. In this post, we will discuss few tips which can be used to deal with such kind of scenarios.

To demonstrate this problem, first, let’s create a user defined function which uses NEWID() function inside. Though in production environment we might be using more complex logic with the NEWID value, but in this demo, to keep it simple, we will be using the LOWER() function which converts the given value to the lower case.

IF OBJECT_ID('dbo.fn_TestNEWID') IS NOT NULL
DROP FUNCTION dbo.fn_TestNEWID
GO
CREATE FUNCTION dbo.fn_TestNEWID()
RETURNS NVARCHAR(50)
AS
BEGIN
--do some stuff with NEWID and return the value
DECLARE @val NVARCHAR(50)
SELECT @val = LOWER(NEWID())
RETURN (@val)
END
GO

When we will try to create this function we will get below error message:

How to use NEWID() in a function

As we have already discussed that to use the NEWID() function inside an user-defined function, we need to do some work around, let’s discuss these workarounds here.

Method 1 – Passing NEWID as a parameter to the user-defined function

In this method, we will be passing this NEWID() function’s value as a function parameter and then the user-defined function will apply some logics on that value and will return the transfomrmed value to the user. Let’s have a look at the below sample code:

IF OBJECT_ID('dbo.fn_TestNEWID') IS NOT NULL
 DROP FUNCTION dbo.fn_TestNEWID
GO
CREATE FUNCTION dbo.fn_TestNEWID
(
 @newid NVARCHAR(50)
)
RETURNS NVARCHAR(50)
AS
BEGIN
 --do some stuff with NEWID and return the value
 DECLARE @val NVARCHAR(50)
 SELECT @val = LOWER(@newid)
 RETURN (@val)
END
GO

In this way, we will be able to create this function and then we can call this function as below:

--Call this function as below
SELECT dbo.fn_TestNEWID(NEWID())
GO

Output:

0ed1e391-4401-47ff-9f3e-1d09aca72ec5

Method 2 – Use a view which returns NEWID to the user-defined function

Now, let’s have a look on this method which uses a view for wrapping the NEWID function inside it. First, create a view as below:

IF OBJECT_ID('dbo.vw_GenerateNEWID') IS NOT NULL
DROP VIEW dbo.vw_GenerateNEWID
GO
CREATE VIEW dbo.vw_GenerateNEWID
AS
SELECT NEWID() AS val
GO

Now, call this view inside the function where NEWID value is required.

IF OBJECT_ID('dbo.fn_TestNEWID') IS NOT NULL
DROP FUNCTION dbo.fn_TestNEWID
GO
CREATE FUNCTION dbo.fn_TestNEWID()
RETURNS NVARCHAR(50)
AS
BEGIN
--do some stuff with NEWID and return the value
DECLARE @val NVARCHAR(50)
SELECT @val = LOWER(val) FROM dbo.vw_GenerateNEWID
RETURN (@val)
END
GO

Now, we can call this function as below:

--Call this function as below
SELECT dbo.fn_TestNEWID()
GO

Output:

9593714a-655f-4cc9-9e79-37e143932582

Thank you for the reading. Please share your input in the comment section.