st1\:*{behavior:url(#ieooui) }

Stored Procedure

Parâmetros de Saída em SQL Server 2000

por Manoel Pimentel

Dando continuidade ao assunto abordado em meu artigo anterior intitulado “Stored Procedure - Passagem de parâmetros em SQL Server 2000”, trago agora neste texto, uma forma prática de como usar parâmetros de saídas, que é um recurso complementar ao trabalho com stored procedure.

Lembrando que continuamos usando como exemplo o Microsoft SQL Server 2000, mas os conceitos básicos podem ser aplicados em praticamente todos os bancos de dados relacionais do mercado.

Parâmetros de saída

O uso de parâmetros de saída em stored procedure possibilita que o engine do servidor de banco de dados utilize menos recursos de hardware para retornar e exibir os resultados de uma instrução SQL, possibilitando um aumento de performance em seu envio, processamento e saída.

Vale lembrar também, que o uso de parâmetros de saída, permite uma boa manipulação dos valores retornados.

O comando CREATE PROCEDURE (com o uso de OUTPUT)

Para especificar que um stored procedure terá uma variável do tipo saída, use a forma normal de sintaxe para criação de stored procedure adicionando o flag OUTPUT no final da declaração de cada variável, veja a sintaxe na Listagem 1:

create procedure NOME_DA_PROCEDURE (@VARIÁVEL TIPO_DE_DADO OUTPUT)

as

begin

Instrução SQL desejada atribuindo um valor ao parâmetro(variável)

end

Listagem 1. Sintaxe do comando CREATE PROCEDURE.

Executando uma procedure e recuperando seu valor de retorno.

Para invocar a execução de um stored procedure, é usado a mesma sintaxe simples EXEC NOMEPROCEDURE, porém, a diferença está na necessidade em declararmos uma variável com o mesmo tipo de dado do parâmetro, e especificarmos que ela receberá o valor retornado pelo parâmetro de saída através da função “output”. Veja na Listagem 2, a sintaxe básica para esse tipo de execução:

/*Declaração de variáveis que receberam o resultado da procedure */

declare @VARIÁVEL tipo ...

 

/*execução e atribuição do valor de retorno. */

exec NomeStoredProcedure @VARIÁVEL output …

 

/* Manipulação do resultado. */

PRINT 'O valor é: '+@VARIAVEL

Listagem 2. Sistaxe de execução de um stored procedure e recuperação do valor retornado através do uso da função output.

Lembrando que você pode aplicar todos os recursos de criação e execução de stored procedure, como por exemplo, as funções: alter procedure, drop procedure, with encryption, etc.

Exemplos práticos:

Veja aqui, alguns exemplos práticos que mostram como criar stored procedure com parâmetros de saída.

Nota 01

Você pode testar esses scripts em qualquer database, porém antes, é necessário criar e povoar uma tabela de exemplo conforme a Listagem 3.

/*Criação da tabela que será o objeto de nossos exemplos */

create table FUNCIONARIOS

(CODIGO varchar(5),

NOME varchar(80),

FUNCAO int,

DEPARTAMENTO int,

SALARIO money

CONSTRAINT PK_CODIGO PRIMARY KEY (CODIGO))

 

go

/*Inserções para povoamento da tabela de FUNCIONARIOS */

insert into FUNCIONARIOS (CODIGO, NOME,FUNCAO,DEPARTAMENTO, SALARIO )

values('00001','OSCAR ALHO DA SILVA', '5','30', 2500)

go

insert into FUNCIONARIOS (CODIGO, NOME,FUNCAO,DEPARTAMENTO, SALARIO )

values('00002','JOÃO DA SILVA SAURO', '5','30', 3500)

go

insert into FUNCIONARIOS (CODIGO, NOME,FUNCAO,DEPARTAMENTO, SALARIO )

values('00003','MARIA DA BOA MORTE', '7','20',1500)

go

insert into FUNCIONARIOS (CODIGO, NOME,FUNCAO,DEPARTAMENTO, SALARIO )

values('00004','BENEVENUTO LOPES ARAUJO', '7','10',1500)

go

insert into FUNCIONARIOS (CODIGO, NOME,FUNCAO,DEPARTAMENTO, SALARIO )

values('00005','MATUZALEM ALVES', '5','30',1500)

Listagem 3. Script SQL para Criação e povoamento da tabela de testes(FUNCIONARIOS).

Exemplo 02 - Implementação de um procedure com um parâmetro de saída

/*Procedure simples para recuperar a soma dos salários da tabela FUNCIONARIOS */

create procedure sp_BuscaSalario (@SOMA money OUTPUT)

as

begin

select @SOMA=sum(SALARIO) from FUNCIONARIOS

end

 

 

/*Execução do stored procedure */

 

/*Declaração de variáveis que receberam o resultado do procedure */

declare @SALARIO_TOTAL money

 

/*execução*/

exec sp_BuscaSalario @SALARIO_TOTAL output

 

/* Manipulação do resultado*/

PRINT 'Salário total R$'+(CAST(@SALARIO_TOTAL AS varchar(20)))

Listagem 4. Recuperando o valor de uma soma de consulta.

Exemplo 03 - Recuperando um valor e aplicando uma estrutura de decisão

/*Procedure simples para recuperar a soma dos salários e aplicar uma estrutura de decisão*/

create procedure sp_BuscaSalario2 (@SOMA money OUTPUT)

as

begin

select @SOMA=sum(SALARIO) from FUNCIONARIOS

end

 

 

/*Execução do procedure */

declare @SALARIO_TOTAL money

exec sp_BuscaSalario2 @SALARIO_TOTAL output

 

if @SALARIO_TOTAL < 20000

begin

print '***SALÁRIO BAIXO***** '

print 'Salário total R$'+(CAST(@SALARIO_TOTAL AS varchar(20)))

end

else

begin

print '***SALÁRIO ALTO***** '

print 'Salário total R$'+(CAST(@SALARIO_TOTAL AS varchar(20)))

end

Listagem 5. Parâmetros e estrutura IF ..ELSE..

Exemplo 04 - Passando e Recuperando parâmetros

/*Procedure com passagem de parâmetro como critério de seleção para

recuperar a soma dos salários da tabela FUNCIONARIOS através de parâmetro de saída*/

 

create procedure sp_BuscaSalario_porDepto (@DEPTO int, @SOMA money OUTPUT)

as

begin

select @SOMA=sum(SALARIO) from FUNCIONARIOS

where

DEPARTAMENTO=@DEPTO

end

 

 

/*Execução do procedure */

 

/*Variável de saída */

declare @SALARIO_TOTAL money

 

/*Variável para passagem de parâmetro*/

declare @DEPARTAMENTO int

set @DEPARTAMENTO=30

 

/*execução*/

exec sp_BuscaSalario_porDepto @DEPARTAMENTO, @SALARIO_TOTAL output

PRINT 'Salário total do departamento '+ cast(@DEPARTAMENTO as varchar)+ ' é R$'+(CAST(@SALARIO_TOTAL AS varchar(20)))

Listagem 6. Procedure com um parâmetro de entrada, e outro de saída.

Nota 02

Caso você esteja usando o SQL Query Analyzer, o resultado da execução de seus stored procedure será semelhante ao mostrado na Figura 01.

 

 ManoelPimentel_ProcedureParamentros_Saída_SQLServer_fig01.JPG

Figura 01. Resultado da execução de um stored procedure através do SQL Query Analyzer

Conclusões

Neste artigo vimos como implementar parâmetros de saída em stored procedures, sendo então, mais um assunto complementar ao uso do mesmo e ao meu artigo anterior publicado anteriormente no portal da revista (ver referências). Portanto, mais uma vez, espero ter contribuído para ajudar a clarear suas idéias acerca desse tema, e gostaria que você sinta-se a vontade para compartilhar comigo suas dúvidas sobre o mesmo, através do e-mail manoel_consultor@yahoo.com.br , até a próxima.

Referências

Books Online do SQL Server 2000 (www.microsoft.com.br)

Artigo: Stored Procedure - Passagem de parâmetros em SQL Server 2000” de Manoel Pimentel em www.devmedia.com.br/sqlmagazine

 

 Noticia_Manoel_Pimentel.gif

Manoel Pimentel Medeiros  é Analista de sistemas, presta consultoria em ERP e Business Intelligence. Certificado RM, ICP-BI e NGC-Intel/IDG. Fundador do XPnorte - Grupo de Usuários  Extreme Programming. Coordena projetos usando XP. Trabalha com tecnologias Java, PHP, Delphi, Oracle, MySQL, FireBird e SQL Server