Need to create function for SQL Server

Question

I have this SQL code, but how to create a function using it?

DECLARE @tbl TABLE(RowId INT IDENTITY(1, 1), ItemName VARCHAR(15));

DECLARE @randomInt INT;

INSERT INTO @tbl(ItemName) 
VALUES ('JAMES'), ('JOHN'), ('ROBERT'), ('MICHAEL'), ('MARY');

SET @randomInt = FLOOR(RAND(CHECKSUM(NEWID())) * 5 + 0);

SELECT ItemName 
FROM @tbl 
WHERE RowId = @randomInt;

Tried that but does not work

CREATE FUNCTION U228_TEST_USER_1.mask_firstn () 
RETURNS VARCHAR(15) 
AS 
BEGIN
    DECLARE @tbl TABLE(RowId INT IDENTITY(1,1), ItemName VARCHAR(15));
    DECLARE @randomInt INT;

    DECLARE @result VARCHAR(15);

    INSERT INTO @tbl(ItemName) 
    VALUES ('JAMES'), ('JOHN'), ('ROBERT'), ('MICHAEL'), ('MARY');

    SET @randomInt = FLOOR(RAND(CHECKSUM(NEWID())) * 5 + 0);

    SELECT @result = ItemName, newid() 
    FROM @tbl;

    RETURN @result;
END;

Answer 1

Neither RAND or NEWID can be directly referenced in a function.

You can indirectly reference these via a view though. So you can use

CREATE VIEW dbo.RandomInt
AS
  SELECT CAST(CRYPT_GEN_RANDOM(4) AS INT) AS Number

GO

CREATE FUNCTION U228_TEST_USER_1.mask_firstn ()
RETURNS VARCHAR(15)
AS
  BEGIN
      DECLARE @OneToFive TINYINT = (SELECT 1 + ABS(Number % 5) FROM dbo.RandomInt);

      RETURN
        (SELECT CHOOSE(@OneToFive, 'JAMES', 'JOHN', 'ROBERT', 'MICHAEL', 'MARY'))
  END 

Scalar UDFs should generally be avoided but in this case it will probably be fine - inlining it makes it more likely that the result will only be evaluated once rather than per row - which is not desirable in the case of random data generation.

Answer 2

You can't use NEWID() in a function, see this for a couple workarounds.