Saudações a todos, Hoje vou apresentar duas rotinas simples porem de extrema funcionalidades 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 unico 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 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. Vamos aos código: -->> 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 -- 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() -->> Bom Galera, é isto ai! Espero de verdade ter ajudado, conforme eu tenha uma folga da correria do dia-a-dia voltarei a postar mais código.