Using NEWID() in an SQL Server user defined function returns an error message:
Msg 443, Level 16, State 1, ...
Invalid use of a side-effecting operator 'newid' within a function.
Here is a trick to get round this. Create a simple view that returns one row containing one column:
CREATE VIEW dbo.vu_newid
AS
SELECT NEWID() as mynewid
Test this out to prove it is working and returns a different NEWID each time.
Then in your function, use the view to return a NEWID or set of NEWIDs as follows:
To populate a variable:
DECLARE @mynewid uniqueidentifier
...
SELECT @mynewid = mynewid FROM dbo.vu_newid
To use in ORDER BY:
SELECT mt.*, vid.mynewid as mynewid
FROM mytable mt
CROSS JOIN dbo.vu_newid vid
ORDER BY vid.mynewid
To use in ROW_NUMBER():
SELECT ROW_NUMBER() OVER(ORDER BY vid.mynewid) as rn,
mt.*
FROM mytable mt
CROSS JOIN dbo.vu_newid vid
Happy coding!
