CODEWITHSUNDEEP
sql-serverstored-procedures
Nisar
Nisar

Reputation: 6038

Inserting random number in SQL Server

I have following table

create table MBR_IDS
(
    MBR_SLNO int identity(1,1),
    MBR_ID int
)
GO

ALTER TABLE MBR_IDS 
    ADD CONSTRAINT UNIQUE_MBR_ID 
    UNIQUE NONCLUSTERED (MBR_ID)

and the following stored procedure to create a random number

CREATE PROCEDURE USP_RANDOM_6
AS 
BEGIN
    DECLARE @chars NCHAR(36)
    SET @chars = N'0123456789'

    DECLARE @result NCHAR(6)
    SET @result = SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
            + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
            + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
            + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
            + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
            + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)

    SELECT @result
END
GO

When I am inserting a value through stored procedure:

declare @row int
set @row =1

while (@row <= 1000)
begin
    insert into MBR_IDS 
    values(USP_RANDOM_6)

    set @row = @row + 1
end
GO

I get this error:

enter image description here

Upvotes: 2

Views: 2544

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

One option is to create a function instead of a stored procedure.

You can do what you want with your stored procedure by capturing the return value in a table:

create table #retval (val nchar(6));
declare @row int = 1;
while (@row <= 1000)
begin
    truncate table #retval;

    insert into #retval
        exec USP_RANDOM_6;

    insert into MBR_IDS
        select val
        from #retval; 
    set @row=@row + 1
end;

Actually, if you store it in a table, you can put everything into a temporary table and do just one insert afterwards:

create table #retval (val nchar(6));
declare @row int = 1;
while (@row <= 1000)
begin
    insert into #retval
        exec USP_RANDOM_6;

    set @row = @row + 1;
end;

insert into MBR_IDS
    select val
    from #retval;

EDIT:

I do not know which is faster, a stored procedure or a function for this operation. However, I would use neither. I would simply do:

declare @row int = 1;
while (@row <= 1000)
begin
    insert into MBR_IDS
        select replace(str(checksum(newid())%1000000, 6, 0), ' ', '0')
    set @row=@row + 1
end;

This uses newid() to calculate a new long string. It then takes the checksum(), which should be a random 4-byte number modulo one million. The rest is just formatting it so it is a '0'-prepended 6-digit character string.

Also, I don't know what I was thinking when I introduced a temporary table in the above constructs. The simpler method is simply:

declare @row int = 1;
while (@row <= 1000)
begin
    insert into MBR_IDS
        exec USP_RANDOM_6;; 
    set @row=@row + 1
end;

Upvotes: 4

roman
roman

Reputation: 117380

if you want to use procedure, you could define output parameter

CREATE PROCEDURE USP_RANDOM_6
(
    @result NCHAR(6) = null output
)
AS
BEGIN
    DECLARE @chars NCHAR(36)
    SET @chars = N'0123456789'

    SET @result = SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
                + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
                + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
                + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
    + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
    + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1)
END
GO

and then use it

declare @row int, @rand nchar(6)
set @row =1
while (@row <= 1000)
begin
    exec USP_RANDOM_6 @result = @rand output

    insert into MBR_IDS 
    values(@rand)
set @row=@row + 1
end

Upvotes: 2

Related Questions