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.