DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to simulate a casino roulette?

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

*/

------------

-- RANDOM date from specific date range - map date range to integer sequence

-- SELECT RANDOM data from a set - SELECT random dates from a set of dates

USE tempdb;

SELECT ROWID = ROW_NUMBER() OVER( ORDER BY SalesOrderID),

       OrderDate

INTO SOHDate

FROM AdventureWorks2008.Sales.SalesOrderHeader

GO

-- (31465 row(s) affected)

 

-- SELECT Random Dates

DECLARE @Max int = (SELECT MAX(ROWID) FROM SOHDate)

DECLARE @Rand INT = (SELECT CEILING(RAND(CAST(NEWID() AS VARBINARY)) * @Max))

SELECT *

FROM SOHDate WHERE ROWID = @Rand

GO 10

 

/*    ROWID       OrderDate

      12183       2003-10-09 00:00:00.000  */

------------

------------

-- Generating Random Numbers with NEWID() & CHECKSUM()

------------

SELECT Random = CONVERT(VARCHAR,ABS(CHECKSUM(NEWID())))

FROM master.dbo.spt_values

ORDER BY Random;

/*

Random

1000945892

1003905302

1008347353 ..... */

------------

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE