4

We have a function dbo.GENERATE_UUID does exactly what it says. I want to maintain the name for compatiblity, but would rather use the built in NEWID(). However, I cannot do this

alter function dbo.GENERATE_UUID ()
returns uniqueidentifier
as begin
   return (SELECT NEWID())
end

because "Invalid use of a side-effecting operator 'newid' within a function."

So I would like to instead

drop function dbo.GENERATE_UUID

and 

create synonym dbo.GENERATE_UUID for NEWID

but when I 

select dbo.GENERATE_UUID()

I get, "Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GENERATE_UUID", or the name is ambiguous."

I also tried variations on 

create synonym dbo.GENERATE_UUID for master.sys.NEWID

Is there anyway to create a SYNONYM for this sort of TSQL built in function? Am interested in any version of SQL Server post 2k5.

Karl Kieninger
  • 8,841
  • 2
  • 33
  • 49
  • 1
    Check [NewID in function](http://stackoverflow.com/questions/772517/newid-inside-sql-server-function) and [Accessing non-deterministic functions in UDF](http://www.sqlpointers.com/2006/07/accessing-non-deterministic-functions.html) – amit_g Jul 15 '14 at 21:42
  • @amit_g: I saw that. In fact that's basically what I want to replace. We don't actually call them within a function so I could just use NEWID(). However existing code use the custom wrapper AND the same custom wrapper is provided in our Oracle version. A synonym, if possible, would provide a clean transition. – Karl Kieninger Jul 16 '14 at 02:18
  • Not sure if this helps (but I found it interesting): executing `SELECT dbo.GENERATE_UUID()` generates the same error, whether you create the synonym or not. – Dave Mason Jul 18 '14 at 15:45
  • I was thinking about a view that selects NEWID() and having your `GENERATE_UUID` udf select the first row from the view to get around the "Invalid use of a side-effecting operator 'newid' within a function." issue...[But someone beat me to it.](http://stackoverflow.com/questions/772517/newid-inside-sql-server-function) – Dave Mason Jul 18 '14 at 16:09
  • Yes, we can get around the side affects that way. But in my usage I am not concerned about the side affecting. Specifically I work with an application that uses both Oracle and SQL Server and being able to synonym the function would allow me to keep a little more code the same between the two platforms. – Karl Kieninger Aug 04 '14 at 14:20

1 Answers1

-1

Depending on your version of SQL server it looks like yes. SQL Scalar function under remarks.

MSDN

Matt
  • 1,441
  • 1
  • 15
  • 29