Generate random 7 digit numbers 1

[williey]

I'm trying to generate random 7 digit numbers and then convert the result to char or varchar. Everything the below function executes, it only return 1 digit number. If I define the @result as int, I have no problem generating 7 digit long numbers.. The results are the same when using either "cast" or "convert". Coming from Oracle, I'm trying to learn SQL Server syntax..

ALTER FUNCTION [dbo].[fn_generatenumber] 
(
	-- Add the parameters for the function here
	@ltype char
)
RETURNS varchar
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result varchar
	DECLARE @Random int
	DECLARE @Lower int
	DECLARE @Upper int

	-- Add the T-SQL statements to compute the return value here
	SET @Lower = 1
	SET @Upper = 9999999

	SELECT @Random = ((@Upper - @Lower -1) * RandNumber + @Lower) from vw_randomnumber

  SET @Result = cast(@Random as varchar)
	RETURN @Result

END

------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.

 

[SQLDenis]

your varchar doesn't have a size
take a look at this


declare @Result varchar,@Result2 varchar(7)

select @Result ='1234567',@Result2 ='1234567'

select @Result,@Result2

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

I suggest you take a look at vongrunt's faq regarding random numbers. faq183-6047

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[williey]

Thanks! I was tempted the use the below code. I have spend way too much time on a simple function than I want to..

select cast(dbo.fn_generatenumber(1) as char)

------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.

 

[SQLSister]

Anytime you do not specify a length on a char or varchar or nchar or nvarchar field in a variable, SQL server will default it to one character.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[williey]

I did read through the rand() portion of FAQ183-6047. As a result, I create a view to generate the random seed.


------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.

 

[SQLDenis]

>>Anytime you do not specify a length on a char or varchar or nchar or nvarchar field in a variable, SQL server will default it to one character.


unfortunately not always!

select convert(varchar,getdate())

sometimes it defaults to 30

that's the problem it is not consistent

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLSister]

Huh, learn something new everyday.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief