carl Brooks carl Brooks - 5 months ago 11
SQL Question

How to list random numbers for each row

I have created an insert into with a select table where what I want it to do is give a random 'PlayerWeighting for each player between 0-99. However the problem I have is that instead of randomizing a player weighting for each row, it seems to do it for each team a player is in. For example:

PlayerID FirstName Surname PlayerWeighting TeamID
1 Chelsea Player1 87 1
2 Chelsea Player2 87 1
3 Chelsea Player3 87 1
4 Liverpool Player1 73 2
5 Liverpool Player2 73 2


I instead want it to do something like this:

PlayerID FirstName Surname PlayerWeighting TeamID
1 Chelsea Player1 87 1
2 Chelsea Player2 73 1
3 Chelsea Player3 62 1
4 Liverpool Player1 90 2
5 Liverpool Player2 51 2


What do I need to change in my query for this to work (NumberID is the number at the end of each string for 'Player'?

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

AS
SET NOCOUNT ON
BEGIN


INSERT INTO dbo.Player (FirstName, Surname, PlayerWeighting, TeamID)
SELECT TeamName AS FirstName, 'Player' + cast(NumberID AS VARCHAR (10)) AS Surname, CONVERT(INT, (RAND() * 99)) AS PlayerWeighting, @TeamID
FROM dbo.Team t
CROSS JOIN dbo.Number n
WHERE TeamID = @TeamID
AND n.NumberID <= 20

END

Answer

this will gives you random integer between 0 and 99

abs(convert (bigint, convert(varbinary(20), newid()))) % 100

or

abs(checksum(newid())) % 100

they key is to use NEWID(), it will generate a unique value for each row. RAND() does not

Comments