Getting an error while creating SQL function

Question

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.

That error is caused by the other code you have around this function. Surround it with gos. However, you can't use rand in a function. — podiluska
– podiluska, Commented Jul 28, 2014 at 14:54
aside, you are not incrementing @position so this will get you into an infinite loop, unless @length is 0. — Jenn
– Jenn, Commented Jul 28, 2014 at 14:56
I saw that as soon as I posted the code, and knew I'd get assassinated on it. I do have an increment in place, I just forgot to type it out. I am instead using While loops to assemble random strings instead of a function. Less processant, but these are one-offs for testing data, so the efficiency isn't a big concern. — Technohydra
– Technohydra, Commented Jul 28, 2014 at 17:18

gotqn · Accepted Answer · 2014-07-28 15:00:51Z

1

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

answered Jul 28, 2014 at 15:00

gotqn

44k47 gold badges167 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Technohydra Over a year ago

I did this as described. The view generates, and spits out RAND's, but when I try to call rndResult in the function, the SSMS spits back a 'bad column' error.

Technohydra Over a year ago

Or if I try to use it as database.schema.table.column, I get a multi-part identifier cannot be bound error.

gotqn Over a year ago

@Technohydra How you are calling the function: SELECT dbo.randomtesttext (5) ?

Technohydra Over a year ago

currently I have it stuck in a variable assign, as in select @orgid = dbo.ranomint(9)

gotqn Over a year ago

Could you should the final code of your dbo.ranomint function?

Technohydra · Accepted Answer · 2014-07-29 15:39:43Z

0

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

answered Jul 29, 2014 at 15:39

Technohydra

112 bronze badges

2 Comments

Technohydra Over a year ago

@gotqn This is the code I have ATM. The hitch is that it keeps telling me that rndValue is an invalid column, or if I try to use the full name, that the multipart ID cannot be bound.

gotqn Over a year ago

First of all, you should delete this answer and edit your question in order to add the code above in it. Then, there are a lot of errors in the code itself - firs there is typo in declare @positin int = 0 and you have missed to include from rndView -it should be set @returnNum = @returnNum + (select Substring(@numPool, convert(int, rndValue*10),1) from rndView)

Collectives™ on Stack Overflow

Getting an error while creating SQL function

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related