CODEWITHSUNDEEP
sql-serverfunctioncallscalar
PinkMachine
PinkMachine

Reputation: 1

How to use Function in the INSERT INTO Statement [SQL SERVR 2016]

The title says it all. I need to create a table(ID, FirstName, LastName) that will be populated with randomly generated 'words' that have a random length and are created from my 'alphabet'. Each word needs to be randomly generated by the DB. The whole table should have 1,000,000 rows.

Let me fill you in what I have done so far.

  1. Created a VIEW that generates a random number:

    CREATE VIEW [dbo].[RANDOM] AS SELECT RAND() RandomResult

  2. Created a SCALAR FUNCTION that generates a random lengh 'word' from the set of determined 'letters' :

      CREATE FUNCTION [dbo].[WordGenerator] (@RandomWord VARCHAR(MAX))
  RETURNS VARCHAR(MAX)
  AS BEGIN

   DECLARE @Alphabet VARCHAR(33) = 'abcdefghijklmnoprstuówxyzęąśłżźćń',
           @StrLength INT,
           @LoopCount INT,
           @RandomString VARCHAR(MAX),
           @AlphabetLength INT;

    SELECT @StrLength = (SELECT RandomResult FROM dbo.Random) * 4 + 7, @LoopCount = 0, @RandomString = '', @AlphabetLength = LEN(@Alphabet);

     WHILE (@LoopCount < @StrLength)
            BEGIN
              SELECT @RandomString = @RandomString + SUBSTRING(@Alphabet, CONVERT(INT, (SELECT RandomResult FROM dbo.Random) * @AlphabetLength), 1)
              SET @LoopCount = @LoopCount + 1;  
            END

   RETURN @RandomString;
END

  3. Now I want to use this FUNCTION called 'WordGenerator' in the INSERT INTO Clause however it simply does not work because I am not able to call it.

How can I call my Function that every single time it is supposed to generate a new, random word?

Using SELECT TOP 1 RandomWord FROM dbo.WordGenerator() does not work.

Using SELECT dbo.WordGenerator() does not work.

Using SELECT * FROM dbo.WordGenerator() does not work.

Any ideas?

Upvotes: 0

Views: 659

Answers (1)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

The issue is that your function expects a parameter that is never used and not passed in. So change it to:

CREATE FUNCTION [dbo].[WordGenerator] ()
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Alphabet VARCHAR(33) = 'abcdefghijklmnoprstuówxyzęąśłżźćń',
    DECLARE @StrLength INT;
    DECLARE @LoopCount INT;
    DECLARE @RandomString VARCHAR(MAX);
    DECLARE @AlphabetLength INT;

    SELECT @StrLength = RandomResult * 4 + 7, @LoopCount = 0, @RandomString = '', @AlphabetLength = LEN(@Alphabet)
    FROM dbo.Random;

    WHILE @LoopCount < @StrLength
    BEGIN
        SELECT @RandomString = @RandomString + SUBSTRING(@Alphabet, CONVERT(INT, RandomResult * @AlphabetLength), 1)
        FROM dbo.Random;

        SET @LoopCount += 1;  
    END;

    RETURN @RandomString;
END;

And then just call it like that: SELECT dbo.WordGenerator(); That's the way you call a scalar function.

SELECT * FROM dbo.WordGenerator(); this way you're calling table valued functions.

Upvotes: 1

Related Questions