CODEWITHSUNDEEP
sql-servert-sql
Ricky
Ricky

Reputation: 35843

TSQL: Why cannot use newid() inside a scalar-valued function

I try to implement the following link with a scalar-valued function, the SQL Server 2000 return an error msg: Invalid use of 'newid' within a function.

http://www.bennadel.com/blog/310-Ask-Ben-Getting-A-Random-Date-From-A-Date-Range-In-SQL.htm

Is there any way to get around this?

Upvotes: 1

Views: 1193

Answers (4)

Rik V
Rik V

Reputation: 1

You can use this (in SQL SERVER )

-- ===============================================
-- SELECT [dbo].[GenerateOTP](Newid())
-- ===============================================
CREATE FUNCTION [dbo].[GenerateOTP]( 
    @Newid uniqueidentifier 
)
RETURNS VARCHAR(10)
BEGIN


    DECLARE @OTP varchar(10) =null

    SET @OTP = (SELECT Cast(CAST(CHECKSUM(CAST(@Newid AS varbinary(64))) AS int) % 1000000000 + 10000000000 AS varchar(15)));

    return @OTP

END

And You Can Call Function AS below

SELECT dbo.GenerateOTP(Newid())

Upvotes: 0

gbn
gbn

Reputation: 432210

Why not pass in NEW_ID() as a parameter all the time? It's overkill to use a view, IMHO

ALTER FUNCTION dbo.MyFunc (
    @param1 int, 
    @param2 datetime,
    @GUID uniqueidentifier
)
RETURNS int
AS
....



SELECT dbo.MyFunc (1, '20091221', NEWID())

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166376

The only way i have found to do this is by creating a view that selects the NEWID() value, and then use this view inside the function.

Upvotes: 2

Rob Farley
Rob Farley

Reputation: 15849

In SQL 2000 you couldn't use non-deterministic functions inside other functions.

The best way of doing this is to use a subquery inside your outer query. It will perform better than a scalar function anyway, as per my blog at: http://msmvps.com/blogs/robfarley/archive/2009/12/05/dangers-of-begin-and-end.aspx

Upvotes: 4

Related Questions