Categorias

Gerando números e registros aleatórios em SQL SERVER

Saudações a todos,

Hoje vou apresentar duas rotinas simples porém de extrema utilidade em SQL Server, seja 2005 ou 2008. A primeira delas consiste em gerar registros com números aleatórios e a segunda consiste em retornar um único registro aleatório.

Vamos ao nosso cenário, imagine que você esta trabalhando em um banco e este deseja implementar aqueles cartões de numeração para validar o usuario, o famoso TOKEN. Então iremos receber o ID da um registro de cliente, para poder ligar o token ao cliente.

Vamos aos código:
Precisaremos de 2 Tabelas 2 dois scripts T-SQL, sendo um deles uma procedure

Vamos a nossa estrutura de tabelas :

CREATE TABLE dbo.TOKEN
(
	IDRegistro  int IDENTITY(1,1) NOT NULL,
	IDOperador  int               NOT NULL,
	Validade    varchar(5)        NOT NULL,
	flagInativo bit               NOT NULL,
)

onde : 
IDRegistro  : campo com valor sequencial
IDOperador  : campo que faz relacionamento com a minha tabela de clientes do banco
Validade    : campo que indica a validade do token, tipo 05/11 (Maio de 2011)
flagInativo : campo que indica se este token esta ativo, por algum motivo pode ser torcado o cartao de token


CREATE TABLE dbo.TOKEN_FILHO
(
	IDRegistroFilho int IDENTITY(1,1) NOT NULL,
	IDTokenPai int NOT NULL,
	PosicaoToken int NOT NULL,
	NumeroToken varchar(4) NOT NULL,
)

onde:
IDRegistroFilho : campo com valor sequencial
IDTokenPai      : campo que faz relacionamento com a tabela dbo.TOKEN
PosicaoToken    : indica a posição do numero no cartão, para o cliente poder saber qual numero a aplicação espera que ele digite
NumeroToken     : este contem o valor que o usuário/cliente devera digitar quando for solicitado de acordo com a posição. Apesar de ser um numero
                  o tipo do campo é string, pois pode ocorrer se o valor iniciar com ZERO.

-->> Criando token, consiste em gerar registros com números aleatórios:


go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Alessandro Silvestre (www.hereclick.com.br)
-- Create date: 22/09/1975
-- Description:	Popular tabela Token e Token_Filho
-- Use Sample : exec PROC_INCLUIR_TOKEN @IDOperador = 1, @Validade = '05/11', @NumToken=20
-- =============================================
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE name ='PROC_INCLUIR_TOKEN' AND TYPE = 'P')
	DROP PROCEDURE dbo.PROC_INCLUIR_TOKEN
GO
CREATE PROCEDURE dbo.PROC_INCLUIR_TOKEN
                     (
                        @IDOperador	int                    -- Operador que solicita geração token
                      , @Validade	varchar(5)             -- Mes/Ano validade do(s) token
                      , @NumToken	int                    -- Quantidade de tokens a ser gerado
                      , @RESULTADO	BIT OUTPUT             -- Retorna status da operacao 1-OK, 0-Erro
                      , @MENSAGEM	VARCHAR(MAX) OUTPUT    -- Retorna mensagem, referente @resultado
                     )
AS
BEGIN
    SET NOCOUNT ON
	SET @RESULTADO = 1
	IF NOT EXISTS(SELECT * FROM DBO.OPERADOR WHERE OPERADOR_ID = ISNULL(@IDOperador,0)) 
	BEGIN
	        RAISERROR 99999 'Operador não existe ou foi excluido por outro usuário'
	        SET @RESULTADO = 0
	        RETURN
	END
    BEGIN TRAN
		-->> SEMPRE que for gerado um novo token (cartao) a um operador/cliente, deve ser inativado
		-->> os cartão antigo		
	        UPDATE dbo.TOKEN SET flagInativo = 1 WHERE IDOperador = ISNULL(@IDOperador,0)
	        
	    -->> Por segurança a cada operação efetuada nos registros, verifico se esta foi concluída com sucesso.
	    -->> Se por ventura alguma operação falhar, nao importando em qual parte do processo. TODA a operação 
	    -->> sera cancelado, graças aos comandos BEGIN TRAN, ROLLBACK e COMMIT
	        IF @@ERROR <> 0
	         BEGIN
	          RAISERROR 99999 'ERROR: Ao incluir registro na tabela Token'
	          SET @RESULTADO = 0
	          ROLLBACK
	          RETURN 
	        END
	        
	    -->> Pronto agora posso gerar um novo cartão, isto consiste em inserir um registro na tabela TOKEN
	    -->>
	        INSERT INTO dbo.TOKEN ( IDOperador,  Validade, flagInativo) VALUES(@IDOperador, @Validade, 0)
	        IF @@ERROR <> 0
	         BEGIN
	          RAISERROR 99999 'ERROR: Ao incluir registro na tabela Token'
	          SET @RESULTADO = 0
	          ROLLBACK
	          RETURN 
	        END
		-->> Ate aqui o cartão de token foi gerado com sucesso, agora vamos gerar o conteudo deste cartão, ou
		-->> seja a numeração bem com suas posições Ex. posição 10 valor: 9764; posição 11 valor 4176, etc..
		
		-->> Crio algumas variaveis de controle, que no decorrer do código explico suas funções.
		DECLARE @IDRegistro		INT
		DECLARE @NumeroToken	VARCHAR(4)
		DECLARE @nCont			INT 
		DECLARE @valor			INT 
		DECLARE @svalor			VARCHAR(4)
		DECLARE @PosicaoToken	INT
		SET     @nCont			= 1
		SET		@IDRegistro		= @@IDENTITY
		SET     @PosicaoToken	= 1
		
		-->> @NumToken, é um parametro que indica a quantidade de posições (tokens) que iremos gerar
        -->> @nCont, indica a quantidade de token que ja foi gerada, tendo como limite o valor contido em @NumToken
            WHILE @nCont <= @NumToken
            BEGIN
              -->> Aqui utilizo o função TSQL NewID() que me gera uma chave UNICA de 36 posições contendo numeros
              -->> ou letras.
              SET @valor = CAST(SubString(CONVERT(binary(16), newid()), 14, 3) AS int)
              
              -->> Do valor de 36 posições gerado eu pego apenas 4, pois desejo que as minhas chaves tenham este tamanho
              SET @svalor = substring(convert(varchar, @valor),LEN(@valor)-3,100)              
              IF LEN(CONVERT(INT,@svalor)) =4
              BEGIN
				-->> Verifico se o valor de 4 posições selecionado ainda NAO FOI UTILIZADO por nenhum cliente, caso afirmativo
				-->> insiro este valor na tabela e avanco para o proxima numeração do token adicionando +1 a variavel @nCont
                IF NOT EXISTS(SELECT * FROM dbo.TOKEN_FILHO WHERE NumeroToken = @svalor)			
                BEGIN
                  INSERT INTO dbo.TOKEN_FILHO (IDTokenPai, PosicaoToken, NumeroToken) VALUES (@IDRegistro, @PosicaoToken, @svalor)
                  -->> como sempre verifico se esta indo tudo correto
                  IF @@ERROR <> 0
                  BEGIN
                    RAISERROR 99999 'ERROR: Ao incluir registro na tabela TOKEN_FILHO'
                    SET @RESULTADO = 0
                    ROLLBACK
                    RETURN 
                  END
                  -->> contabilizo um token gerado pois tudo ocorreu bem e incremento +1 na numeração da posição.
                  -->> Poderia ter utilizado uma unica variavel, mas pensando em futuras implementações decidi utilizar duas
                  
                  SET @PosicaoToken	= @PosicaoToken +1		-- Contabilizo a posição do token que foi gerado.
                  SET @nCont		= @nCont		+1		-- Contabilizo numero de tokens gerado
                END
              END
            END			
        -->> Como toda a operação ocorreu sem nenhum ERRINHO, eu finalizo processo com o comando COMMIT. Como assim?
        -->> quando abri o comando Begin Tran, toda esta operação foi feita em memória o commando commit torna estas
        -->> operações fisica no banco de dados
        COMMIT
END
GO

   -->> segunda consiste em retornar um unico registro aleatório


        Declare @OPERADOR int           -- Armazena o código do operador/Cliente
        Declare @Posicao  int           -- Armazena a posição a ser solicitada ao cliente
        Declare @Token    varchar(04)   -- Armazena o valor que o cliente devera digitar, claro que vc nao vai mostrar
                                        -- isto a ele e sim confrontar com o que ele digitou
        
        -->> Esta é a rotina completa porem vou quebra-la em partes para poder comentar melhor ok.
        SELECT TOP 1 
           @POSICAO = POSICAOTOKEN, 
           @TOKEN   = NUMEROTOKEN 
        FROM TOKEN_FILHO 
        WHERE IDTOKENPAI IN (SELECT IDREGISTRO 
                               FROM TOKEN 
                              WHERE IDOPERADOR = @OPERADOR AND FLAGINATIVO =0
                            ) 
        ORDER BY NEWID()
        
        
        -->> Esta sub-query vai me retornar o ID de um CARTÃO valido, lembra que na rotina anterior sempre que é gerado
        -->> um novo cartão eu cancelo o cartão anterior. Então estou identificando de qual cartão (token) devo solicitar
        -->> dados ao usuário
        (SELECT IDREGISTRO 
           FROM TOKEN 
          WHERE IDOPERADOR = @OPERADOR AND FLAGINATIVO =0
        ) 
        
        
        -->> Uma vez identificado o cartão valido, armazeno nas variaveis quais os valores que deverei confrontar com o
        -->> que o cliente/operador digitar, a clausuta TOP 1, indica que sera retornado apenas um unico registro.
        SELECT TOP 1 
           @POSICAO = POSICAOTOKEN, 
           @TOKEN   = NUMEROTOKEN 
        FROM TOKEN_FILHO 
        WHERE IDTOKENPAI ()
        
        -->> O que faz os registros serem ALEATÓRIOS é exatamente a linha ORDER BY 
        ORDER BY NEWID()

Arquivos para Download:

RotinaCompleta.txt