creating funtion 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 2005

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

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.

Answer 2

Scalar functions are required to be deterministic (e.g. given an input the same results always come back).

This means you may not call any non-deterministic functions like NewID or GetDate

Answer 3

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 4

Does this have to be done with a function call? Whenever I needs this functionality, I just use:

checksum(newid())

This will generate negative numbers -- if they must be positive, you could use

asb(checksum(newid()))