# Stored Procedure Problem RSS

## 7 replies

Last post May 06, 2012 02:47 AM by Rituranjan

Participant

1231 Points

1279 Posts

### Stored Procedure Problem

Apr 29, 2012 06:26 AM|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,

Contributor

2064 Points

324 Posts

### Re: Stored Procedure Problem

Apr 29, 2012 06:59 AM|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.
This can be beneficial to other community members reading the thread.

Regards,
Rama Selvam M.

Participant

1231 Points

1279 Posts

### Re: Stored Procedure Problem

Apr 29, 2012 07:12 AM|LINK

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

Member

2 Points

1 Post

### Re: Stored Procedure Problem

Apr 29, 2012 08:44 AM|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

Contributor

2064 Points

324 Posts

### Re: Stored Procedure Problem

Apr 29, 2012 12:02 PM|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.
This can be beneficial to other community members reading the thread.

Regards,
Rama Selvam M.

Participant

1231 Points

1279 Posts

### Re: Stored Procedure Problem

May 04, 2012 06:23 AM|LINK

please give the full sql stored procedure

Contributor

2064 Points

324 Posts

### Re: Stored Procedure Problem

May 04, 2012 06:47 AM|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.
This can be beneficial to other community members reading the thread.

Regards,
Rama Selvam M.

Participant

1231 Points

1279 Posts

### Re: Stored Procedure Problem

May 06, 2012 02:47 AM|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"