GENERATE RANDOM NUMBERS AND ALPHANUMERIC RANDOM NUMBERS

DECLARE @I AS NUMERIC

SET @I=1

WHILE @I<100

BEGIN

INSERT INTO PIN_MAST(SL_NO,PIN_ID,PIN_PASS)(

(SELECT 1,ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber],CONVERT(varchar(10), right(newid(),10)))

)

SET @I=@I+1

END

PROCEDURE FOR BINERY CALCULATION IN MSSQLSERVER 2012

--SAMPLE PROCEDURE TO CALCULATE TEAM MANPOWER IN LEFT AND RIGHT

ALTER PROCEDURE [TEAM_POINT]

      @M_ID NVARCHAR(MAX),

      @BV AS NUMERIC

AS

BEGIN

     

      DECLARE @C_ID NVARCHAR(MAX)

      DECLARE @SP_ID NVARCHAR(MAX)

      DECLARE @POS NVARCHAR(MAX)

      DECLARE @XX NVARCHAR(MAX)

      DECLARE @C_ID1 NVARCHAR(MAX)

      DECLARE @SP_ID1 NVARCHAR(MAX)

      DECLARE @POS1 NVARCHAR(MAX)

      DECLARE @I INTEGER

      DECLARE @J INTEGER

     

      SET @XX=@M_ID

      SET @I=0

      WHILE @I=0

      BEGIN

     

            SELECT @C_ID=C_ID,@SP_ID=SP_ID,@POS=POSITION FROM CUST_MAST WHERE C_ID=@XX

            IF @POS='LEFT'

            BEGIN

                  UPDATE POINT_MAST SET NEW_LEFT=NEW_LEFT+@BV WHERE C_ID=@SP_ID

            END

           

            IF @POS='RIGHT'

            BEGIN

                  UPDATE POINT_MAST SET NEW_RIGHT=NEW_RIGHT+@BV WHERE C_ID=@SP_ID

            END

            SET @XX=@SP_ID

            IF LEN(@SP_ID)<3

            BEGIN

                  SET @XX=''

                  SET @I=1

            END

      END

END

DROP TABLE AND CREATE TABLE IN MSSQLSERVER 2012

DROP TABLE TEMP_POINT_MAST

CREATE TABLE TEMP_POINT_MAST(

C_ID NVARCHAR(MAX),

USER_NAME NVARCHAR(100) PRIMARY KEY,

SP_ID NVARCHAR(MAX),

SP_NAME NVARCHAR(MAX),

SPILL_ID NVARCHAR(MAX),

SPILL_NAME NVARCHAR(MAX),

POSITION NVARCHAR(MAX),

TAG NVARCHAR(10)

)

CREATE TABLE IN MSSSQL SERVER 2012

CREATE TABLE RPT_TEAM_LIST(

C_ID NVARCHAR(MAX),

NAME NVARCHAR(MAX),

USER_NAME NVARCHAR(MAX),

SP_ID NVARCHAR(MAX),

SP_NAME NVARCHAR(MAX),

J_DATE DATE,

STATUS NVARCHAR(MAX),

SL_NO NUMERIC(18,2),

LVL NUMERIC(18,2)

)