August 3, 2015 at 6:59 am
Help needed.
I've got to modifiy a function that basically should concatenate 2 names and then should add a random value.
I've tried:
1) Calling rand() from inside the function > produces error
2) Calling a stored procedure existing in the DB that generates a rand no. between 2 integers (again it fails, you can't call stored procedures from functions 🙁 ).
Any suggestions you can come up with? I'm really a newbie so any straight forward solutions much appreciated. Other suggestions welcome as far as they are easy to implement.
Thanks in advance. p.
ALTER FUNCTION [dbo].[myfunction]
(
@pmVolunteerID varchar(8),
@pmPhase int
)
RETURNS nvarchar(20)
AS
BEGIN
-- Declare the return variable here
DECLARE @return nvarchar(20)
DECLARE @alea int
--EXECUTE @alea=dbo.spRandBTW 100,999 CAN'T CALL this
--EXECUTE @alea=round(1000 * rand(),0) CAN'T USE this
IF @pmPhase = 2
BEGIN
-- Add the T-SQL statements to compute the return value here
SET @return = (
SELECT lower(left([FN],1)+left([SN],1)) + CAST(@alea as nvarchar)
FROM tblVolunteer
WHERE VolunteerID = @pmVolunteerID)
END
ELSE
BEGIN
SET @return = ''
END
-- Return the result of the function
RETURN @return
END
August 3, 2015 at 7:38 am
Why MS made it so difficult to write functions with indeterminate results, I'll never know. You'll need to do the following hack...
First, create this view. The name of the function stands for "indeterminate Functions".
CREATE VIEW [dbo].[iFunction] AS
/**********************************************************************************************************************
Purpose:
This view is callable from UDF's which allows us to indirectly get a NEWID() within a function where we can't do such
a thing directly in the function. This view also solves the same problem for GETDATE().
Usage:
SELECT MyNewID FROM dbo.iFunction; --Returns a GUID
SELECT MyDate FROM dbo.iFunction; --Returns a Date
Revision History:
Rev 00 - 06 Jun 2004 - Jeff Moden - Initial creation
Rev 01 - 06 Mar 2011 - Jeff Moden - Formalize code (all comments). No logic changes.
**********************************************************************************************************************/
SELECT MyNewID = NEWID(),
MyDate = GETDATE();
GO
Then you can write your function something like the following...
ALTER FUNCTION dbo.myfunction
(
@pmVolunteerID VARCHAR(8),
@pmPhase INT
)
RETURNS NVARCHAR(20) AS
BEGIN
DECLARE @Return NVARCHAR(20)
;
IF @pmPhase = 2
SELECT @Return = LOWER(LEFT(FN,1)+LEFT(SN,1))
+ (SELECT CAST(ABS(CHECKSUM(MyNewID))%900+100 AS NVARCHAR(10)) FROM dbo.iFunction)
FROM tblVolunteer
WHERE VolunteerID = @pmVolunteerID
;
ELSE
SELECT @Return = ''
;
RETURN @Return
;
END
;
GO
For more information on how to easily generate random numbers and dates, please see the following articles...
http://www.sqlservercentral.com/articles/Data+Generation/87901/
http://www.sqlservercentral.com/articles/Test+Data/88964/
EDIT! Added a missing parenthesis.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2015 at 8:01 am
Thanks Jeff, perfect solution. Testing it now.
I also found this explanation, pretty much in the same line, that also seems easy enough:
http://blog.sqlauthority.com/2012/11/20/sql-server-using-rand-in-user-defined-functions-udf/
August 3, 2015 at 9:56 am
a_ud (8/3/2015)
Thanks Jeff, perfect solution. Testing it now.I also found this explanation, pretty much in the same line, that also seems easy enough:
http://blog.sqlauthority.com/2012/11/20/sql-server-using-rand-in-user-defined-functions-udf/
Thanks for the feedback. The problem with RAND() even in that case is that its a fair bit slower than using NEWID() with the CHECKSUM conversion (which converts it to an INT) as a random source and still requires similar calculations to convert it to a domain constrained set of integers.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2015 at 3:55 pm
Jeff Moden (8/3/2015)
a_ud (8/3/2015)
Thanks Jeff, perfect solution. Testing it now.I also found this explanation, pretty much in the same line, that also seems easy enough:
http://blog.sqlauthority.com/2012/11/20/sql-server-using-rand-in-user-defined-functions-udf/
Thanks for the feedback. The problem with RAND() even in that case is that its a fair bit slower than using NEWID() with the CHECKSUM conversion (which converts it to an INT) as a random source and still requires similar calculations to convert it to a domain constrained set of integers.
And nevermind that RAND() really is not all that random, since it becomes deterministic when I pass it any non-null parameter. But yeah - the perf is also poor :).
----------------------------------------------------------------------------------
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?
August 3, 2015 at 4:14 pm
Matt Miller (#4) (8/3/2015)
Jeff Moden (8/3/2015)
a_ud (8/3/2015)
Thanks Jeff, perfect solution. Testing it now.I also found this explanation, pretty much in the same line, that also seems easy enough:
http://blog.sqlauthority.com/2012/11/20/sql-server-using-rand-in-user-defined-functions-udf/
Thanks for the feedback. The problem with RAND() even in that case is that its a fair bit slower than using NEWID() with the CHECKSUM conversion (which converts it to an INT) as a random source and still requires similar calculations to convert it to a domain constrained set of integers.
And nevermind that RAND() really is not all that random, since it becomes deterministic when I pass it any non-null parameter. But yeah - the perf is also poor :).
You could use the only pseudo-random number generator in SQL Server as the operand for RAND(). That would be NEWID() but the conversion can't implicitly be done so you have to convert it and now it's even slower. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply