## 7 replies

Last post May 05, 2012 10:47 PM by Rituranjan

• Rituranjan

Member

305 Points

1258 Posts

### Stored Procedure Problem

in a stored procedure a string variable conain

varchar S="1,3,7,9,5":

how to generate Random number in this Variable (S)

it means random number may be  only (S) character  it means

Rand_No=1,

Rand_No=7,

Rand_No=5,

Rand_No=3,

Rand_No=9,

• ramanselva

Participant

1414 Points

315 Posts

### Re: Stored Procedure Problem

```DECLARE @Random int;
DECLARE @Upper int;
DECLARE @Lower int

SET @Lower = 1 -- The lowest random number
SET @Upper = 9 -- The highest random number
SELECT @Random = Round(((@Upper - @Lower -1) * Rand() + @Lower), 0)
SELECT CASE WHEN (@RANDOM%2<>0) THEN @RANDOM ELSE @RANDOM+1 END```

Please remember to click “Mark as Answer” on the post that helps you also "Unmark" if not

Regards,
RamaSelvam M.
• Rituranjan

Member

305 Points

1258 Posts

• saibrunda77

None

0 Points

1 Post

### Re: Stored Procedure Problem

In SQL server Declare @strTest nvarchar(50) set @strTest = '1,3,7,9,5' --select * from Demo.dbo.Split(@strTest,',') SELECT [Data] FROM Demo.dbo.Split(@strTest,',') ORDER BY NEWID() CREATE FUNCTION dbo.Split ( @RowData nvarchar(2000), @SplitOn nvarchar(5) ) RETURNS @RtnValue table ( Id int identity(1,1), Data nvarchar(100) ) AS BEGIN Declare @Cnt int Set @Cnt = 1 While (Charindex(@SplitOn,@RowData)>0) Begin Insert Into @RtnValue (data) Select Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))) Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData)) Set @Cnt = @Cnt + 1 End Insert Into @RtnValue (data) Select Data = ltrim(rtrim(@RowData)) Return END
• ramanselva

Participant

1414 Points

315 Posts

### Re: Stored Procedure Problem

Rituranjan,

have you tried SQL which always returns from the range {1,3,5,7,9} in random

Please remember to click “Mark as Answer” on the post that helps you also "Unmark" if not

Regards,
RamaSelvam M.
• Rituranjan

Member

305 Points

1258 Posts

### Re: Stored Procedure Problem

please give the full sql stored procedure

• ramanselva

Participant

1414 Points

315 Posts

### Re: Stored Procedure Problem

Hi,

I intended my previous post

DECLARE @Random int; DECLARE @Upper int; DECLARE @Lower int SET @Lower = 1 -- The lowest random number SET @Upper = 9 -- The highest random number SELECT @Random = Round(((@Upper - @Lower -1) * Rand() + @Lower), 0) SELECT CASE WHEN (@RANDOM%2<>0) THEN @RANDOM ELSE @RANDOM+1 END

Please remember to click “Mark as Answer” on the post that helps you also "Unmark" if not

Regards,
RamaSelvam M.
• Rituranjan

Member

305 Points

1258 Posts

### Re: Stored Procedure Problem

thanks but number is also even or odd

like  number may be

no="1,2,4,7,10,14,3"

the easiest way how to this no is arrange in random order like

no="1,4,10,14,3,2,7"

or

no="1,4,3,,2,7,10,14"