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… More