Home What's at risk when we circumvent SQL Server's limitation for side effect operations in UDF?
Reply: 0

What's at risk when we circumvent SQL Server's limitation for side effect operations in UDF?

user1300
1#
user1300 Published in April 27, 2018, 6:51 am

Today I used the approach in this answer to great success, to replace names, insurance numbers and addresses with randomized garbage in multiple instances of the same database schema, depending on a "test" / "production" flag in the data.

Background: Trying to do

CREATE FUNCTION dbo.FailsToCreate() 
RETURNS uniqueidentifier 
AS 
BEGIN 
  RETURN NEWID()
END

inevitably fails with

Msg 443, Level 16, State 1, Procedure FailsToCreate, Line 6 [Batch Start Line 27] Invalid use of a side-effecting operator 'newid' within a function.

Now we can be badass enough to do

CREATE VIEW dbo.vwGuessWhat AS SELECT NEWID() Fooled

which surprisingly allows us to make it work with

CREATE FUNCTION dbo.SuddenlyWorks() 
RETURNS uniqueidentifier 
AS 
BEGIN 
  RETURN (SELECT Fooled FROM vwGuessWhat)
END

Documentation is silent about consequences. It merely lists the functions that cannot be used, and does not mention a possibility to bypass the limitation.

Can I safely continue to use this approach in production code, or is there a danger in bypassing SQL Server's validation that will cause it to malfunction?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.306813 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO