using NEWID() inside a function - as variable - but getting the same value every time

Question

I'm using NEWID() inside a function. this is my function:

CREATE FUNCTION dbo.myFunc (@newid NVARCHAR(50))
RETURNS int

AS BEGIN
    declare @rndValue int
    set @rndValue = (SELECT top 1 * FROM #temp ORDER BY lower(@newid))
    RETURN @rndValue 
END

i have #temp with values: '1','2','3' i want to random from this table using this function. i called this function like this:

dbo.myFunc (NEWID())

but i'm getting every time the same value ('1')

where is my error?

Answer 1

The function isn't using NEWID(). It's using the value of a string parameter named @newid

If you want to select a random row, use ORDER BY NEWID(). This generates a new non-sequential GUID for every row and orders the rows by it, effectively producing a random ordering, eg :

set @rndValue = (SELECT top 1 * FROM #temp ORDER BY newid())

The original query orders rows by a constant and thus has no effect. It's no different than using

set @rndValue = (SELECT top 1 * FROM #temp ORDER BY 'potato')

If you check the query's execution plan you'll see there's no SORT operation. The query optimizer simply removes the ineffective ORDER BY clause.

Finally, NEWID() produces a GUID, not a string. That's a 128-bit binary value. Using LOWER() serves no purpose

Answer 2

What you are doing looks strange, but if you need it in this format use the code below:

CREATE VIEW dbo.GetGUID
AS
SELECT NEWID() AS [NewID]

GO


CREATE TABLE dbo.temp
(
    id int
);

GO

INSERT INTO dbo.temp
VALUES (1), (2), (3);


GO

CREATE FUNCTION dbo.myFunc ()
RETURNS int

AS BEGIN
    declare @rndValue int
    set @rndValue = (SELECT top 1 id FROM dbo.temp CROSS APPLY  dbo.GetGUID ORDER BY [NewID])
    RETURN @rndValue 
END

GO

SELECT  dbo.myFunc()
SELECT  dbo.myFunc()
SELECT  dbo.myFunc()
SELECT  dbo.myFunc()
SELECT  dbo.myFunc()
SELECT  dbo.myFunc()

Basically, if you need to have NEWID() in function you need to use the view hack.

Answer 3

CREATE FUNCTION dbo.myFunc (@newid NVARCHAR(50))
RETURNS int

AS BEGIN
    declare @rndValue int
    set @rndValue = (SELECT top 1 * FROM #temp ORDER BY @newid)
    RETURN @rndValue 
END