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.
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
DECLARE @Randomint; DECLARE
@Upperint; DECLARE
@Lowerint 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+1END
Please remember to click “Mark as Answer” on the post that helps you.
This can be beneficial to other community members reading the thread.
Rituranjan
Participant
1231 Points
1277 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,
ramanselva
Contributor
2064 Points
324 Posts
Re: Stored Procedure Problem
Apr 29, 2012 06:59 AM|LINK
This can be beneficial to other community members reading the thread.
Regards,
Rama Selvam M.
Rituranjan
Participant
1231 Points
1277 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
saibrunda77
Member
2 Points
1 Post
Re: Stored Procedure Problem
Apr 29, 2012 08:44 AM|LINK
ramanselva
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
This can be beneficial to other community members reading the thread.
Regards,
Rama Selvam M.
Rituranjan
Participant
1231 Points
1277 Posts
Re: Stored Procedure Problem
May 04, 2012 06:23 AM|LINK
please give the full sql stored procedure
ramanselva
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
This can be beneficial to other community members reading the thread.
Regards,
Rama Selvam M.
Rituranjan
Participant
1231 Points
1277 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"