carl Brooks carl Brooks - 6 months ago 8
SQL Question

How to set random numbers

I just want to know how can I set the random number so that it’s between 1-10 and does not display decimals like 0.744545454?
Also how can I iterate through each record through execution so that each row will contain its own random number for ‘PlayerWeighting?

Below Is my stored proc:

CREATE PROCEDURE [dbo].[Player_CreateWholeTeam]
@TeamID INT

AS
SET NOCOUNT ON
BEGIN
DECLARE @FirstName VARCHAR(25)
DECLARE @Surname VARCHAR(25)
DECLARE @PlayerWeighting TINYINT

SELECT @FirstName = TeamName
FROM dbo.Team
WHERE TeamID = @TeamID

SELECT @Surname = 'Player' + cast(NumberID AS VARCHAR (10))
FROM dbo.Team CROSS JOIN dbo.Number
WHERE TeamID = @TeamID
ORDER BY TeamID, NumberId

SELECT @PlayerWeighting = RAND(1-10)
FROM dbo.Player

INSERT INTO dbo.Player (FirstName, Surname, PlayerWeighting, TeamID)
VALUES (@FirstName, @Surname, @PlayerWeighting, @TeamID)


END

Answer

You could use something like:

SELECT CONVERT(INT, (RAND() * 10))
Comments