none
Problemas com User Function RRS feed

  • Pergunta

  • Olá galera,

    Estou tentando fazer uma codificação em SQL Server Express 2005 e estou tendo um erro.

    No meu código eu possuo apenas a criação e inserção de dados nas tabelas, gerando números randômicos, algumas tabelas não poderão possuir sequência de códigos iguais para não duplicar uma chave primária, então criei uma funcção para armazenar as sequencias de códigos primarios e caso o gerador retorne sequencias iguais ele gera novamente um novo código.

    O problema real agora é esse:

    Msg 111, Level 15, State 1, Line 2
    'CREATE FUNCTION' deve ser a primeira instrução em um lote de consultas.


    Não sou muito entendido de SQL, alguém sabe o que devo fazer para que funcione minha função?

    Segue o código completo

    USE vendas;
    
    CREATE TABLE #tvendas
    (
    	tempCodUm INT,
    	tempCodDois INT,
    )
    
    CREATE FUNCTION dbo.isEqual(@num1 AS INT, @num2 AS INT) 
    RETURNS INT
    	BEGIN
    		DECLARE @iFlag INT;
    		DECLARE @columnTempUm INT;
    		DECLARE @columnTempDois INT;
    		SET @iFlag = 1;
    		DECLARE CurItens CURSOR FOR
    		SELECT tempCodUm,tempCodDois FROM #tvendas
    			BEGIN TRANSACTION
    				OPEN CurItens
    					FETCH FIRST FROM CurItens INTO @columnTempUm,@columnTempDois
    						BEGIN								
    							WHILE (@@FETCH_STATUS = 0)
    								BEGIN
    									IF ((@columnTempUm = @num1) AND (@num2 = @randomProduto))
    										BEGIN		
    											SET @iFlag = 0;
    											PRINT 'É IGUAL';	
    										END
    									FETCH NEXT FROM CurItens INTO @columnTempUm,@columnTempDois;
    								END
    						END
    					
    				CLOSE CurItens;
    				DEALLOCATE CurItens;
    				RETURN @iFlag;
    	END
    GO
    
    USE master
    
    
    IF EXISTS (SELECT NAME FROM sys.databases WHERE name ='vendas')
    	BEGIN
    		DROP DATABASE vendas;
    	END
    GO
    
    
    CREATE DATABASE vendas;
    GO
    
    USE vendas;
    GO
    
    IF EXISTS (SELECT NAME FROM sys.tables WHERE name = 'ItensDaVenda')
    	BEGIN
    		DROP TABLE ItensDaVenda;
    	END
    GO
    
    IF EXISTS (SELECT NAME  FROM sys.tables WHERE name = 'ProdutosDoFornecedor')
    	BEGIN
    		DROP TABLE ProdutosDoFornecedor;
    	END
    GO
    
    IF EXISTS (SELECT NAME FROM sys.tables WHERE name = 'Vendas')
    	BEGIN
    		DROP TABLE Vendas;
    	END
    GO
    
    IF EXISTS (SELECT NAME FROM sys.tables WHERE name = 'Compras')
    	BEGIN
    		DROP TABLE Compras;
    	END
    GO
    
    IF EXISTS (SELECT  NAME FROM sys.tables WHERE name = 'Fornecedor')
    	BEGIN
    		DROP TABLE Fornecedor;
    	END
    GO
    
    IF EXISTS (SELECT  NAME FROM sys.tables WHERE name = 'Produto')
    	BEGIN
    		DROP TABLE Produto;
    	END
    GO
    
    IF EXISTS (SELECT NAME  FROM sys.tables WHERE name = 'Cliente')
    	BEGIN
    		DROP TABLE Cliente;
    	END
    GO
    
    CREATE TABLE Fornecedor
    (
    	codFornecedor INT IDENTITY(1,1),
    	nome VARCHAR(100) NOT NULL,
    	tel INT,
    	cnpj INT,
    	PRIMARY KEY(codFornecedor)
    );
    GO
    
    CREATE TABLE Produto
    (
    	codProduto INT IDENTITY(1,1),
    	descricao VARCHAR(200),
    	qtdade INT NOT NULL,
    	preco INT,
    
    	PRIMARY KEY(codProduto)
    );
    GO
    
    CREATE TABLE Cliente 
    (
    	idCliente INT IDENTITY(1,1),
    	nome VARCHAR(100),
    
    	PRIMARY KEY (idCliente)
    );
    GO
    
    CREATE TABLE Compras
    (
    	codCompra INT IDENTITY(1,1),
    	numNF INT,
    	codFornecedor INT NOT NULL,
    	codProduto INT NOT NULL,
    	data DATETIME,
    
    	PRIMARY KEY(numNF,codCompra),
    	FOREIGN KEY(codFornecedor) REFERENCES Fornecedor,
    	FOREIGN KEY(codProduto) REFERENCES Produto
    );
    GO
    
    CREATE TABLE Vendas
    (
    	codVenda INT IDENTITY(1,1),
    	numNF INT,
    	idCliente INT NOT NULL,
    	valorTotal INT NOT NULL,
    
    	PRIMARY KEY (codVenda,numNF),
    	FOREIGN KEY (idCliente) REFERENCES Cliente
    );
    GO
    
    CREATE TABLE ItensDaVenda
    (
    	codProduto INT,
    	codVenda INT,
    	numNF INT,
    	PRIMARY KEY(codProduto,numNF),
    	FOREIGN KEY(codProduto) REFERENCES Produto,
    	FOREIGN KEY(codVenda,numNF) REFERENCES Vendas
    );
    GO
    
    CREATE TABLE ProdutosDoFornecedor
    (
    	codFornecedor INT IDENTITY(1,1),
    	codProduto INT NOT NULL,
    	quantidade INT NOT NULL,
    
    	PRIMARY KEY(codFornecedor),
    	FOREIGN KEY(codProduto) REFERENCES Produto
    );
    GO
    
    
    
    
    DECLARE @count INT;
    DECLARE @lower INT;
    DECLARE @higher INT;
    DECLARE @randomQty INT;
    DECLARE @randomPhone INT;
    DECLARE @lowerPhone INT;
    DECLARE @higherPhone INT;
    DECLARE @randomDate DATETIME;
    DECLARE @date_from DATETIME;
    DECLARE @date_to DATETIME;
    DECLARE @cnpjHigher INT;
    DECLARE @cnpjLower INT;
    DECLARE @cnpj INT;
    DECLARE @flag INT;
    DECLARE @randomFornecedor INT;
    DECLARE @randomProduto INT;
    SET @date_from = '2009-01-01';
    SET @date_to = '2009-30-12';
    SET @cnpjHigher = 99999999;
    SET @cnpjLower = 111111111;
    SET @count = 1;
    SET @lower = 5;
    SET @higher = 999;
    SET @lowerPhone = 1000000;
    SET @higherPhone = 9000000;
    
    WHILE (@count <= 1000)
    	BEGIN
    		SET @cnpj = ROUND(((@cnpjHigher - @cnpjLower - 1) * RAND() + @cnpjLower),0);
    		SET @randomQty = ROUND(((@higher - @lower - 1) * RAND() + @lower),0);
    		SET @randomPhone = ROUND(((@higherPhone - @lowerPhone - 1) *  RAND() + @lowerPhone),0);
    		SET @randomDate = (@date_from +(ABS(CAST(CAST( NewID() AS BINARY(8) )AS INT))%CAST((@date_to - @date_from) AS INT)));
    		SET @randomFornecedor = ROUND(((@higher - @count - 1) * RAND() + @count),0);
    		SET @randomProduto = ROUND(((@higher - @count - 1) * RAND() + @count),0);
    	
    		WHILE ((SELECT isEqual(@randomProduto,@randomFornecedor)) = 0)
    			BEGIN
    				SET @randomProduto = ROUND(((@higher - @count - 1) * RAND() + @count),0);
    				SET @randomFornecedor = ROUND(((@higher - @count - 1) * RAND() + @count),0);
    			END	
    		
    		INSERT INTO produto (descricao,qtdade,preco) VALUES ('produto' + CONVERT(varchar(40),@count), @randomQty, @randomQty + @count);
    		INSERT INTO fornecedor (nome,tel,cnpj) VALUES ('fornecedor' + CONVERT(varchar(40),@count),@randomPhone,@cnpj);
    		INSERT INTO cliente (nome) VALUES ('cliente ' + CONVERT(varchar(40),@count));
    		
    		INSERT INTO compras (numNF,codFornecedor,codProduto,data) VALUES (@count,@randomFornecedor,@randomProduto,@randomDate);
    		INSERT INTO #tvendas (tempCodUm,tempCodDois) VALUES (@randomFornecedor,@randomProduto);
    		SET @count = @count + 1;
    
    	END
    
    
    GO
    
    
      DECLARE @date_from DATETIME;
      DECLARE @date_to DATETIME;
      DECLARE @date DATETIME; 
      
      
      -- Select random dates.
      
    
    
    segunda-feira, 28 de setembro de 2009 22:37

Respostas

  • Welysson,

    Para você conseguir criar a função

    Coloque um go entre o create table e o create function

    CREATE
     TABLE
     #tvendas
    (
    tempCodUm INT ,
    tempCodDois INT ,
    )

    GO -- aqui

    CREATE FUNCTION dbo.isEqual(@num1 AS INT , @num2 AS INT )
    RETURNS INT
    Agora pra ela fazer o que precisa (se nao esta fazendo) é outra coisa.

    Como tu disse que o problema real é o erro acima, resolve-se como o GO
    $____ Your Experience !!! www.laertejuniordba.spaces.live.com
    terça-feira, 29 de setembro de 2009 02:35

Todas as Respostas

  • Welysson,

         Ele ser randômico é uma exigência do seu negócio? Ou ele poderia ser sequencial?
    MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008
    segunda-feira, 28 de setembro de 2009 23:28
    Moderador
  • Welysson,

    Você esta se conectando no banco de dados Vendas, mas depois apaga o mesmo!!!!

    O que você esta querendo fazer?
    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    terça-feira, 29 de setembro de 2009 00:24
  • Welysson,

    Para você conseguir criar a função

    Coloque um go entre o create table e o create function

    CREATE
     TABLE
     #tvendas
    (
    tempCodUm INT ,
    tempCodDois INT ,
    )

    GO -- aqui

    CREATE FUNCTION dbo.isEqual(@num1 AS INT , @num2 AS INT )
    RETURNS INT
    Agora pra ela fazer o que precisa (se nao esta fazendo) é outra coisa.

    Como tu disse que o problema real é o erro acima, resolve-se como o GO
    $____ Your Experience !!! www.laertejuniordba.spaces.live.com
    terça-feira, 29 de setembro de 2009 02:35
  • Welysson,

         Ele ser randômico é uma exigência do seu negócio? Ou ele poderia ser sequencial?
    MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008

    Olá Roberto,

    Não era exigência ser randômico, mas acabei resolvendo por fazer sequencial mesmo, o que eu fiz foi:

    WHILE (@count <= 1000)
    	BEGIN
    		SET @cnpj = ROUND(((@cnpjHigher - @cnpjLower - 1) * RAND() + @cnpjLower),0);
    		SET @randomQty = ROUND(((@higher - @lower - 1) * RAND() + @lower),0);
    		SET @randomPhone = ROUND(((@higherPhone - @lowerPhone - 1) *  RAND() + @lowerPhone),0);
    		SET @randomDate = (@date_from +(ABS(CAST(CAST( NewID() AS BINARY(8) )AS INT))%CAST((@date_to - @date_from) AS INT)));
    		SET @randomNF = ROUND(((@higher - @lower - 1) * RAND() + @lower),0);
    		SET @myPass = EncryptByKey(Key_GUID('myKey'), 'blahblah' + CONVERT(varchar(40),@count));
    		
    		INSERT INTO produto (descricao,qtdade,preco) VALUES ('produto' + CONVERT(varchar(40),@count), @randomQty, @randomQty + @count);
    		-- @@identity é a última chave criada na sessão atual
    		SET @idProduto = @@identity;		
    		INSERT INTO fornecedor (nome,tel,cnpj) VALUES ('fornecedor' + CONVERT(varchar(40),@count),@randomPhone,@cnpj);
    		-- @@identity é a última chave criada na sessão atual
    		SET @idFornecedor = @@identity;
    		INSERT INTO cliente (nome,pass) VALUES ('cliente ' + CONVERT(varchar(40),@count),@myPass);	
    		-- @@identity é a última chave criada na sessão atual
    		SET @idCliente = @@identity;
    		INSERT INTO compras (numNF,codFornecedor,codProduto,data) VALUES (@randomNF,@idFornecedor,@idProduto,@randomDate);
    		INSERT INTO vendas (numNF,idCliente,valorTotal) VALUES (@randomNF,@idCliente,@count+@randomNF);
    		-- @@identity é a última chave criada na sessão atual
    		SET @idVenda = @@identity;
    		INSERT INTO itensdavenda VALUES (@idProduto,@idVenda);
    		INSERT INTO produtosdofornecedor (codProduto,quantidade) VALUES (@idProduto,@count+@randomQty);
    
    		SET @count = @count + 1;
    	END
    GO
    Agora ele pega sempre a ultima chave identity da sessão. Resolveu meu problema. Tirei aquela User Function que verificava se o mesmo código já estava cadastrado e ficou mais simples. Esse código é para um trabalho na faculdade que terei que apresentá-lo hoje.

    Muito obrigado pela ajuda.
    terça-feira, 29 de setembro de 2009 15:58
  • Welysson,

    Você esta se conectando no banco de dados Vendas, mas depois apaga o mesmo!!!!

    O que você esta querendo fazer?
    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA

    Olá Junior,


    Devido esse código ser um trabalho da faculdade o professor quer ficar apertando F5 sem parar e não quer que ocorra nenhum erro, então tive que criar todo código preventivo para que verifique a existência das tabelas e se existirem o código deleta automaticamente.
    terça-feira, 29 de setembro de 2009 16:07
  • Welysson,

    Para você conseguir criar a função

    Coloque um go entre o create table e o create function

    CREATE
    
     TABLE
    
     #tvendas
    (
    tempCodUm INT ,
    tempCodDois INT ,
    )

    GO -- aqui

    CREATE FUNCTION dbo.isEqual(@num1 AS INT , @num2 AS INT )
    RETURNS INT
    Agora pra ela fazer o que precisa (se nao esta fazendo) é outra coisa.

    Como tu disse que o problema real é o erro acima, resolve-se como o GO
    $____ Your Experience !!! www.laertejuniordba.spaces.live.com

    Entendi Laerte,

    Então o Go finaliza um bloco de código para que ele seja executado em uma espécie de "lote". O que eu fiz que deu certo também foi ter colocado a function em um arquivo SQL separado com o GO também.

    Muito obrigado pela sua ajuda.
    terça-feira, 29 de setembro de 2009 16:09
  • Olá Welysson,

           Eu te perguntei porque não acho uma boa idéia você controlar manualmente os códigos neste caso. Acho melhor um campo Identity...

           Fico contente de você ter conseguido resolver o problema. Mas pense na possibilidade do uso de identity no futuro...


    MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008
    terça-feira, 29 de setembro de 2009 17:38
    Moderador