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,

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

Re: Stored Procedure Problem

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

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

Re: Stored Procedure Problem

Rituranjan,

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

Re: Stored Procedure Problem

please give the full sql stored procedure

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

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"

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