Invalid use of 'newid' within a function.

Question

Invalid use of 'newid' within a function.

SQL STEWIE

SSCommitted

Points: 1837
More actions
February 22, 2008 at 12:07 pm

#62743

I have this error
Server: Msg 443, Level 16, State 1, Procedure fnGetFullWHInformation, Line 71
Invalid use of 'newid' within a function.
How can I use newid() in a user defined function properly?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

I am not sure you ever will be able to. SQL Server 2000 had a specific prohibition against allowing you to use anything non-deterministic in creating a user-defined-function. non-deterministic = the result changes every time you run it, which is essentially the definition of what NEWID() does....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Heh... this is where I cheat like hell... everything is possible if you have the imagination for it... remember, before you can think outside the box, you must first realize that you're in a box 😉

Here's how to trick SQL into doing just exactly what you want with NEWID() in a function. Works for GETDATE(), too...

--===== Create a view that gets a new ID

CREATE VIEW MyNewID

AS

SELECT NEWID() AS MyNewID

GO

--===== Create a test function to show that we can get a NEWID

-- using the "trick" view

CREATE FUNCTION NewIDTest ()

RETURNS uniqueidentifier

AS

BEGIN

RETURN (SELECT MyNewID FROM dbo.MyNewID)

END

GO

--===== Show that the function with the NEWID does actually work

SELECT TOP 100

dbo.NewIDTest()

FROM dbo.SysObjects

Send beer, please. I already have enough pretzels 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 3

souLTower

SSCarpal Tunnel

Points: 4026

March 4, 2008 at 5:19 am

#784855

Jeff, amazing as always.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

ST! Man, good to see you're still around! I thought you dropped off the face of the planet.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ian Yates SSCoach Points: 19738 More actions · Answer 5

I haven't looked into it too far, but does MS SQL use some smarts (in 2000) that assumes that a function is deterministic given its parameters and thus executes it only once in a query even though it could be used twice in that query? Actually I'll try it in a minute and write back 😀

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

I think you'll find that NEWID() is the exception to many rules that apply to other functions. Strange as it seems, it's the only true random number generator in all of SQL. Even RAND can't do what it does when it comes to distribution of numbers over a range (using CHECKSUM and MOD to build the range from it).

I don't know what I'd do for testing if NEWID() wasn't available. I use it exclusively when generating randomized million row test tables with anything from random alpha columns, random ints/money, to random dates... all range controlled.

Actually using NEWID() for it's intended purpose is a harsh 😉 You try comparing two 36 character hex based identifiers at two in the morning with just the ol' peepers and see how much fun you have 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ian Yates SSCoach Points: 19738 More actions · Answer 7

Silly me, if I paid enough attention to Jeff's example I'd see that he's executing it 100 times already and returning different results 🙂 And I'd seen similar things work previously but it's late here and perhaps it'd just be easier to delete the previous post! I'll investigate before I speculate and post in future. :w00t:

Ian Yates SSCoach Points: 19738 More actions · Answer 8

And you read my silly first post before I posted the sheepish "oops" one. 🙂

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

Heh... It's always good to have someone else confirm the test in their own way. Thanks, Ian.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

joe-799481 Grasshopper Points: 20 More actions · Answer 10

First off, I want to thank you for your clever solution to this problem.

The issue that I am running into here is that I'm getting the same results from each query of the NewID view when I'm asking it for a NewID() more than once in a query.

We did have code like this to generate a password inside a sproc:

SELECT @SomePass= substring(cast(abs(cast(newid() as binary(3))^cast(substring(cast(newid() as binary(3)),8,3) as int)) as nvarchar) + cast(abs(cast(newid() as binary(3))^cast(substring(cast(newid() as binary(3)),8,3) as int)) as nvarchar),1,@PasswordLength)

Since this is inside a sproc, it's forcing us to use a cursor where we really don't want to. So I find your wonderful post here, create the view, replace all of the "newid()" instances with "(SELECT GetNewID FROM dbo.GetNewID)" (my view is named differently).

The problem is that I'm getting the same uniqueidentifier each of the four times it is called in that one query. This can be demonstrated by running these queries:

SELECT GetNewID as a,

GetNewID as b

FROM dbo.GetNewID

select newid() as a,

newid() as b

The first returns the same thing twice, the second returns two different uids. Is there anyway to modify this to return unique results?

I've tried to think "outside the box", but I think I need someone to take the tape off of the box first.

Thanks!!

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

Of course the following is returning both values as the same ID...

SELECT GetNewID as a,

GetNewID as b

FROM dbo.GetNewID

GetNewID returns just one row and you are assigning "a" and "b" to the same row. If you do a simple...

SELECT *

FROM dbo.GetNewID

... how many rows do you get? One...

Your other query...

select newid() as a,

newid() as b

... returns two different ID's... as expected.

So far as you first very wide formula (a Cr here and there would have been appreciated) it returns a unigue and different number each time it's executed. The only thing I see a problem with is where the same ID is returned twice... and, like I said, that's expected.

So, what is the actual problem you're having and why do you think you need a cursor for anything?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

joe-799481 Grasshopper Points: 20 More actions · Answer 12

Thanks for your reply.

The cursor is needed in the case of a bulk insert of 'individuals' (This code is part of a trigger that fires whenever an individual is added to the database).

The reason I wrote those two example queries (which I now realize are not as relevant as I thought) is because I was trying to explain that I need to generate 4 different uniqueidentifiers in the line of code that generates the password, which I couldn't get to happen with your method.

So, the question is: Can I get more than one different uniqueidentifier from your view in one query?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 13

I've already proven that it will return more than one NEWID()... just not the way you did it because the view only returns one row. Go back and look at my earlier example... it returns 100 different ID's from a single select.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

And if you need two random values on the same row, either select the View or function twice, or change the view to have two columns, each calling NewID().

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]