February 12, 2008 at 1:54 pm
Hello everyone,
I have a function which when I parse it (checkmark) it doesn't return any errors. However when I actually try to
execute it I get an error. Here is my function:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
alter FUNCTION [dbo].[UniqueNumber]
(
@TableName varchar(30),
@ColumnName varchar(30)
)
RETURNS int
AS
BEGIN
DECLARE @mx int
DECLARE @mn int
DECLARE @intCounter int
DECLARE @SuccessFlag bit
DECLARE @RandNum int
DECLARE @HolderNum varchar(7)
SET @mn = 1
SET @mx = 9
SET @intCounter = 1
SET @SuccessFlag = 0
SET @HolderNum = ''
WHILE @SuccessFlag = 0
BEGIN
WHILE @intCounter <= 7
BEGIN
SET @RandNum = ROUND(@mn + (RAND() * (@mx-@mn)),0)
--SET @HolderNum = @HolderNum + Cast(ROUND(@mn + (RAND() * (@mx-@mn)),0) as varchar(1))
SET @HolderNum = @HolderNum + CAST(@RandNum AS varchar(1))
SET @RandNum = @HolderNum
--SELECT @HolderNum --Use for debugging
SET @intCounter = @intCounter + 1
END
--Take concatenated number and query for. Id it doesn't find, flip flag to true
IF NOT EXISTS(SELECT PhotoID FROM Production.Product WHERE PhotoID = @RandNum)
BEGIN
SET @SuccessFlag = 1
END
END
-- Return the result of the function
RETURN @RandNum
END
GO
This parses just fine. However, when I execute, I get this error:
Msg 443, Level 16, State 1, Procedure UniqueNumber, Line 30
Invalid use of side-effecting or time-dependent operator in 'rand' within a function.
The line it doesn't like is:
SET @RandNum = ROUND(@mn + (RAND() * (@mx-@mn)),0)
I bolded it above
If I take it out it executes fine, but I need this. Can anyone see the problem?
Thanks,
Strick
February 12, 2008 at 2:10 pm
Create a view with rand() in it, then pull from that instead of using rand() in the function. That usually works.
create view Random
as
select rand() as num
SET @RandNum = ROUND(@mn + (RAND() * (@mx-@mn)),0)
becomes:
select @RandNum =
(select num
from dbo.random)
SET @RandNum = ROUND(@mn + (@RandNum * (@mx-@mn)),0)
You'll need to change the definition of @RandNum to float for this to work, or create another variable for the initial assignment from the view.
(I've used this kind of thing before, but it's been a long time, and I think it was in SQL 2000. Let me know if it works/doesn't work in 2005.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply