creating function using newID()

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 i开发者_如何学Cnt

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

  RETURN (@code)
END

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())

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.

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

abs(checksum(newid()))