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()))
精彩评论