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?

[NEWID()](https://learn.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sql?view=sql-server-ver15) returns a GUID, not a number or string. If you see `1`, it didn't come from `NEWID()`. A GUID is a binary value too, so using `LOWER` is meaningless. — Panagiotis Kanavos, Feb 05 '20 at 08:07
You also can't access temporary tables from within a function.... — Zohar Peled, Feb 05 '20 at 08:35

Panagiotis Kanavos · Answer 1 · 2020-02-05T08:26:47.737

3

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

edited Feb 05 '20 at 08:26

answered Feb 05 '20 at 08:09

Panagiotis Kanavos

120,703
13
188
236

i used it with variable because in user defined function you cannot use newid(), so the solution i found is to use with sending the newid() as input. but the problem is to random from a table because when i use this way with random not from table it work fine. – malka Feb 05 '20 at 08:25
@מלכיג'דה-חדיד that's the problem, not the solution. I explained how `NEWID()` works - a new GUID is generated for every row. You can't do that with the parameter value, you'll use the *same* value for every row. The server will realize this and simply remove the `ORDER BY` clause – Panagiotis Kanavos Feb 05 '20 at 08:28
so what you suggest instead? – malka Feb 05 '20 at 08:29
@מלכיג'דה-חדיד don't write a UDF for this. There are other SO questions that ask how to use `NEWID()` in a UDF by tricking the server. – Panagiotis Kanavos Feb 05 '20 at 08:30
but this is the only way o found because this function is called from case when then.(function) , i cannot use storeprocedure instead. because u cannot exec it inside case statement. – malka Feb 05 '20 at 08:32
One option is to use *nothing*. Put the query directly into the script or stored procedure that needs a random row. Another is to use tricks like those shown [in this question](https://stackoverflow.com/questions/772517/newid-inside-sql-server-function). – Panagiotis Kanavos Feb 05 '20 at 08:34
In any case, you didn't ask why `NEWID()` doesn't work in a UDF, you asked why your query returned the same row always. This is actually called [the XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem): people have a problem with X, think Y is the solution so you when that doesn't work they ask for Y, not X – Panagiotis Kanavos Feb 05 '20 at 08:35
@מלכיג'דה-חדיד `but this is the only way o found because this function is called from case when then.(function) , i cannot use storeprocedure instead. because u cannot exec it inside case statement` Please start a new question with your query, we will show you how to get one random row from a table. – Squirrel Feb 05 '20 at 08:43

score 3 · Answer 2 · answered Feb 05 '20 at 08:37

3

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.

answered Feb 05 '20 at 08:37

gotqn

42,737
46
157
243

This is a good workaround for the `newid()` "problem" - but the OP is also attempting to select from a temporary table - and the workaround for that would be to use a table valued parameter - which forces a much more cumbersome solution. – Zohar Peled Feb 05 '20 at 08:53
@ZoharPeled Yes, but as the OP said he is executing the function (and this is not possible using temporary table) I suggested the OP just try to create a usage example and the real one is not referring temp table. – gotqn Feb 05 '20 at 09:43

score -1 · Answer 3 · answered Feb 05 '20 at 08:38

-1

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

answered Feb 05 '20 at 08:38

Jay Rindani

30
3

3

Please don't post only code as an answer, but include an explanation what your answer does and how it solves the problem of the question. Answers with an explanation are generally of higher quality, and a more likely to attract upvotes. – Mark Rotteveel Feb 05 '20 at 11:16

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

3 Answers3