creating function using newID()

Question

I keep getting this error: Any Ideas?

Invalid use of side-effecting or time-dependent operator in 'newid' within a function.

I am working with MS-SQL Server 2005. Here is the T-SQL statement:

Create Function [dbo].[GetNewNumber](  )
RETURNS int
AS
BEGIN

  Declare @code int

  set @code = (SELECT CAST(CAST(newid() AS binary(3)) AS int) )

  RETURN (@code)
END

Answer 1

You can't use NEWID() within a function.

A usual workaround (in my experience, it's more been the need for GETDATE()) is to have to pass it in:

Create Function [dbo].[GetNewNumber](@newid UNIQUEIDENTIFIER  )
RETURNS int
AS
BEGIN

  Declare @code int

  set @code = (SELECT CAST(CAST(@newid AS binary(3)) AS int) )

  RETURN (@code)
END

And call it like:

SELECT dbo.GetNewNumber(NEWID())

Answer 2

The function will not let you use the NewID, but this can be worked around.

Create View vwGetNewNumber
as
Select Cast(Cast(newid() AS binary(3)) AS int) as NextID

Create Function [dbo].[GetNewNumber] ( ) RETURNS int 
AS 
BEGIN
Declare @code int
Select top 1  @code=NextID from vwGetNewNumber
RETURN (@code) 
END

Then you can use select dbo.[GetNewNumber]() as planned.