Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : How to Create a Comma-Delimited List Without Using a Cursor
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

Since the NEWID() function is a non-deterministic function, this function cannot be called directly from a user-defined function.  One way to work around this limitation is to call the function indirectly through a view.

First create a view that calls the NEWID() function:

CREATE VIEW [dbo].[Random] 
AS
SELECT NEWID() AS [RandomID]

Since a view can be accessed from a user-defined function, a function can now be created that will indirectly call the NEWID() function through the view:

CREATE FUNCTION [dbo].[ufn_GenerateRandomNumber]()
RETURNS INT
AS
BEGIN
 DECLARE @RandomNumber   INT
 SELECT @RandomNumber = ABS(CAST(CAST([RandomID] AS VARBINARY) AS INT))
 FROM [dbo].[Random]
 RETURN @RandomNumber
END

Now to use this user-defined function that calls the NEWID() function indirectly:

SELECT [dbo].[ufn_GenerateRandomNumber]()

Back to Tip of the Day List Next Tip