# Stored Procedure Problem RSS

## 7 replies

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

Member

305 Points

1267 Posts

### Stored Procedure Problem

Apr 29, 2012 02:26 AM|Rituranjan|LINK

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,

Participant

1414 Points

315 Posts

### Re: Stored Procedure Problem

Apr 29, 2012 02:59 AM|ramanselva|LINK

```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
This can be beneficial to other community members reading the thread.

Regards,
RamaSelvam M.

Member

305 Points

1267 Posts

### Re: Stored Procedure Problem

Apr 29, 2012 03:12 AM|Rituranjan|LINK

but my question is number must be  onely 1,3,5,7,9 only please help me

None

0 Points

1 Post

### Re: Stored Procedure Problem

Apr 29, 2012 04:44 AM|saibrunda77|LINK

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

Participant

1414 Points

315 Posts

### Re: Stored Procedure Problem

Apr 29, 2012 08:02 AM|ramanselva|LINK

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
This can be beneficial to other community members reading the thread.

Regards,
RamaSelvam M.

Member

305 Points

1267 Posts

### Re: Stored Procedure Problem

May 04, 2012 02:23 AM|Rituranjan|LINK

please give the full sql stored procedure

Participant

1414 Points

315 Posts

### Re: Stored Procedure Problem

May 04, 2012 02:47 AM|ramanselva|LINK

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
This can be beneficial to other community members reading the thread.

Regards,
RamaSelvam M.

Member

305 Points

1267 Posts

### Re: Stored Procedure Problem

May 05, 2012 10:47 PM|Rituranjan|LINK

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"