|
Execute the following Microsoft SQL Server T-SQL script in Management Studio Query Editor to create and test a stored procedure with Monte Carlo simulation for roulette wheel.
-- SQL Server Generate Random Number - QUICK SYNTAX
SELECT LargeRandom=CONVERT(BIGINT, RAND() * 10000000000)
-- 3922837106
SELECT RandomRANDNewID = CEILING(RAND(CAST(NEWID() AS VARBINARY)) * 1000)
-- 668
SELECT RandomNewID= ABS(CONVERT(INT,CONVERT(varbinary, NEWID())))
-- 2138848854
------------
-- SQL RAND and NEWID functions for random number generation
-- T-SQL casino roulette wheel simulator stored procedure
USE AdventureWorks2008;
GO
CREATE PROC RouletteSimulator
@Bet INT,
@Amount SMALLMONEY
AS
BEGIN
DECLARE @Result TABLE(
MESSAGE CHAR(30),
Wheel INT,
Win SMALLMONEY
)
DECLARE @Wheel INT
IF (@Bet < 1
OR @Bet > 36)
BEGIN
INSERT @Result
SELECT 'Bet 1 through 36 only',
-1,
0
SELECT *
FROM @Result
RETURN
END
SET @Wheel = ROUND(RAND(CAST(NEWID() AS VARBINARY)) * 38,0,-1)
IF (@Wheel = @Bet)
BEGIN
INSERT @Result
SELECT 'Lucky! You won: ',
@Bet,
36 * @Amount
SELECT *
FROM @Result
RETURN
END
INSERT @Result
SELECT 'Sorry, you lost',
@Wheel,
-1.0 * @Amount
SELECT *
FROM @Result
END
GO
-- Execute stored procedure - Run 38 simulations -- WHILE loop
-- Betting $100 on number >10<
DECLARE @Plays smallint = 38, @i smallint = 0
WHILE ( @i < @Plays)
BEGIN
EXEC RouletteSimulator 10, 100.0
SET @i += 1
END
GO
/* Partial results
MESSAGE Wheel Win
Sorry, you lost 31 -100.00
MESSAGE Wheel Win
Lucky! You won: 10 3600.00
MESSAGE Wheel Win
Sorry, you lost 36 -100.00
*/
------------
|
