CODEWITHSUNDEEP
sql-servert-sql
Technohydra
Technohydra

Reputation: 11

Getting an error while creating SQL function

I am creating a SQL function (MS-SQL) to be used in a sproc to create random strings of a random length. When I write the entire function out, the Create Function line gets the red line of doom, claiming that "Incorrect syntax: Create Function must be the only statement in the batch."

Code is as follows:

Create Function dbo.randomtesttext (@length int)
returns varchar(999)
as
Begin
    declare @pool varchar
    set @pool = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'
    declare @return varchar(999)
    declare @position int = 0
    while (@position < @length)
    begin
        set @return = @return + (select substring(@pool, convert(int, rand()*36), 1))
        set @position = @position + 1
    end
    return @return
end

Any thoughts? I'm sure the answer is a simple one that I'm just not experienced enough to see.

Upvotes: 1

Views: 99

Answers (2)

Technohydra
Technohydra

Reputation: 11

Create View rndView
as 
SELECT RAND() rndValue
GO

Declare @length int

Create Function dbo.randomint (@length int)
returns Varchar(50)
AS 
BEGIN
declare @positin int = 0
declare @numPool varchar = '1234567890'
declare @returnNum varchar = ''
while @position <= @Length
Begin
set @returnNum = @returnNum + (select Substring(@numPool, convert(int, rndValue*10),1))
set @position = @position + 1
end
return @returnNum
end

Upvotes: 0

gotqn
gotqn

Reputation: 43666

You cannot use RAND build-in function in your user defined function. Anyway, you can use the following workaround putting RAND in a view:

CREATE VIEW rndView
AS
SELECT RAND() rndResult
GO

and then call it in your function like this:

Create Function dbo.randomtesttext (@length int)
returns varchar(999)
as
Begin
    declare @pool varchar
    set @pool = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'
    declare @return varchar(999)
    declare @position int = 0
    while (@position < @length)
    begin
        set @return = @return + (select substring(@pool, convert(int, rndResult*36), 1) FROM rndView)
    end
    return @return
end

Upvotes: 1

Related Questions