Select or Return - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select or Return

Author

Topic

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 01:44:44

Hey guys and gals. I got a situation and I just want to know why something keeps happening. Here's the deal. I have a sproc which receives 2 number then returns a random number anywhere between the two provided (including the provided numbers.) Anyway, The thig is this, if I use select vs return everything if fine and dandy, but if instead i use return vs select, i get errors on some number but not on others. Time for some code:

CREATE procedure proc_RandomNumber
	@LowerBound bigint,
	@UpperBound bigint
as
begin
	declare @RetVal bigint
	select @RetVal = ((@UpperBound - @LowerBound + 1) * rand() + @LowerBound)
	
	return @RetVal --returns errors with 10 digit numbers
end
GO

now in QA use:

declare @id as bigint
exec @id = proc_randomnumber 1,9999999999
print @id

and you get:
Server: Msg 8115, Level 16, State 2, Procedure proc_RandomNumber, Line 9
Arithmetic overflow error converting expression to data type int.
The 'proc_RandomNumber' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
0

The good one:

CREATE procedure proc_RandomNumber
	@LowerBound bigint,
	@UpperBound bigint
as
begin
	declare @RetVal bigint
	select @RetVal = ((@UpperBound - @LowerBound + 1) * rand() + @LowerBound)
	
	select @RetVal --all fine and dandy
end
GO

I have everything declared as bigint. So why would I get the error?

Thanks to all.

- RoLY roLLs

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-29 : 01:53:16

You can only RETURN an integer value. It's normally used to return error codes and such. Don't use it for anything else. You're asking for trouble.
If you want to return something, use an OUTPUT parameter.

CREATE procedure proc_RandomNumber
@LowerBound bigint,
@UpperBound bigint,
@Retval bigint OUTPUT
as
begin
select @RetVal = ((@UpperBound - @LowerBound + 1) * rand() + @LowerBound)

end
GO

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 01:58:51

ah! got it! only returns int, no wonder! Thanks timmy!

- RoLY roLLs

Kristen
Test

22859 Posts

Posted - 2004-07-29 : 02:08:16

Just a thought: would a function be easier to incorporate into your code (rather than having to EXEC an SProc)?

Kristen

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 02:20:03

i would love to, but the rand() function isn't allowed in a UDF

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 02:23:20

speaking of which, how do i now set a variable to the resulted random number, withoutdeclaring an output param? if not possible, then i guess i'll add the output param

- RoLY roLLs

Kristen
Test

22859 Posts

Posted - 2004-07-29 : 05:53:54

"i would love to, but the rand() function isn't allowed in a UDF"

Ah, of course. Could you use NewID() as the basis of a big hexadecimal random number, or is that not allowed in UDF too?

Kristen

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 12:53:18

quote:
Originally posted by Kristen

"i would love to, but the rand() function isn't allowed in a UDF"

Ah, of course. Could you use NewID() as the basis of a big hexadecimal random number, or is that not allowed in UDF too?

Kristen

You guessed it, I just tested newid() and it's not allowed in a UDF.

- RoLY roLLs

Kristen
Test

22859 Posts

Posted - 2004-07-29 : 14:39:07

That's so annoying ...

I wish I wish I wish I could have SET ROWCOUNT in a UDF and then I could do really efficient ASP recordset paging in a function, but that aint allowed either.

Kristen

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 15:24:12

Any hopes of that in 'YUKON'?

- RoLY roLLs

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-29 : 15:28:47

Non-deterministic functions aren't allowed in a UDF, but you can always pass them into the UDF. So GETDATE() isn't allowed, but there isn't anything stopping you from doing this:

SELECT dbo.SomeFunction(GETDATE())

Tara

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 15:49:00

ooo, very nice tara! I may be able to use that!

- RoLY roLLs

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-29 : 18:04:02

You can use views to bypass non-deterministic rules for functions. (Did someone say that already?)


Create View MyRand As
Select Rand = rand() 

CREATE function proc_RandomNumber (
	@LowerBound bigint,
	@UpperBound bigint )
Returns BigInt
as
begin
	declare @RetVal bigint
	select @RetVal = ((@UpperBound - @LowerBound + 1) * rand + @LowerBound) FROM MyRand
	
	Return @RetVal --all fine and dandy
end

select dbo.proc_RandomNumber(100,50)

--Ken
Your Kung-Fu is not strong. -- 'The Core'

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 18:51:16

it works well too, ken, and I like it better Thanks. Anyone with other suggestions?

- RoLY roLLs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources