Oi! Seja bem-vindo!
Hoje veremos 4 formas de inserir o resultado de uma procedure em uma tabelas do SQL server. Aprenderemos também como fazer select em XML, como usar o openrowset e como retornar parâmetros e dados ao usuário com a cláusula output.
Aqui vai o script usado no vídeo:
------------------------------------------------------------------ -- 4 formas de salvar resultados de procedures em tabelas do SQL ------------------------------------------------------------------ -------------------------------------------------- -- Requisitos do teste -------------------------------------------------- -- Banco de testes use master if db_id('curso') is not null drop database curso go create database curso go use curso go -- Tabela para guardar os resultados da SP: create table resultados_procedure (id smallint identity(1,1) primary key, empresa varchar(50), data_execucao datetime2) go -- Stored Procedure qualquer create procedure usp_teste as begin select 'dba-pro' as empresa, getdate() as data_execucao end go -- Teste da nossa SP execute usp_teste go -------------------------------------------------- -- Método 1: Insert/Execute -------------------------------------------------- -- Salvando resultados insert into resultados_procedure (empresa, data_execucao) execute usp_teste -- Verificando dados inseridos select * from resultados_procedure go -------------------------------------------------- -- Método 2: Openrowset -------------------------------------------------- -- OBS: Permite acesso a outros SQL da rede... -- Requisito: Habilitar Ad Hoc Distributed Queries execute sp_configure 'show advanced options', 1 reconfigure go execute sp_configure 'ad hoc distributed queries', 1 reconfigure go -- Executando proc com openrowset insert into resultados_procedure (empresa, data_execucao) select * from openrowset('msdasql', 'driver={sql server};server=.;trusted_connection=yes', 'execute curso.dbo.usp_teste') as rowset -- Verificando dados inseridos select * from resultados_procedure go /* -- Exemplo de openrowset com usuário e senha ao invés de trusted connection insert into resultados_procedure (empresa, data_execucao) select * from openrowset('msdasql', 'driver={sql server};server=.;uid=nome_usuario;pwd=senha_usuario', 'execute curso.dbo.usp_teste') as rowset go */ -------------------------------------------------- -- Método 3: Insert + cláusula output -------------------------------------------------- -- Inserir resultado na tabela e retornar dados via output alter procedure usp_teste as begin insert into resultados_procedure (empresa, data_execucao) output inserted.* select 'dba-pro' as empresa, getdate() as data_execucao end go execute usp_teste go select * from resultados_procedure go -------------------------------------------------- -- Método 4: Retorno em XML -------------------------------------------------- -- Retornar XML via variável e dados via select alter procedure usp_teste @xml xml = null output as begin set @xml = ( select 'dba-pro' as empresa, getdate() as data_execucao for xml path) select 'dba-pro' as empresa, getdate() as data_execucao end go -- Execução normal execute usp_teste go -- Execução recebendo o XML declare @xml xml execute dbo.usp_teste @xml output select @xml go -- Execução desmontando o XML recebido para inserir na tabela declare @xml xml execute dbo.usp_teste @xml output print 'procedure retornou resultados' insert into resultados_procedure select resultado.colunas.value('empresa[1]', 'varchar(100)'), resultado.colunas.value('data_execucao[1]', 'datetime2') from @xml.nodes('row') as resultado(colunas) print 'xml foi desmontado e dados inseridos' go
O que é uma stored procedure ou procedimento armazenado
Para você que está começando agora, vamos do começo… 🙂 O que é uma stored procedure? Para começar, existem vários nomes que identificam esse objeto de banco de dados:
– Stored procedures (nome completo)
– Procedures (no inglês abreviado)
– Procedimento armazenado (no português)
– SP (abreviação)
– P (outra abreviação)
Todos esses nomes significam a mesma coisa: Um conjunto de comandos SQL guardados dentro do banco de dados.
Porque usar Stored Procedures
Esse tipo de pergunta sempre dá muito pano para manga… porém como procuro sempre a abordagem mais prática e “de mercado” possível, posso afirmar que as razões principais são:
1) Reutilização de código: Imagine que você tem um conjunto de comandos SQL que calcula quantos feriados existem no ano. Ao invés de você repetir esse mesmo código em vários programas diferentes, páginas web, pontos diferentes do código fonte do seu executável, você pode criar uma SP e sempre que você precisar desse cálculo você usa a SP. Mais fácil, né?
2) Performance: Como o código SQL fica guardado no próprio banco de dados, o banco sabe com antecedência o que você vai quer (antes de você pedir), então para te retornar o que precisa é mais rápido.
3) Manutenção facilitada: Em geral é muito mais fácil alterar e testar o código de uma SP do que um código compilado em linguagens tradicionais de programação (VB, C#, aspnet, etc…)
4) Segurança: SPs ajudam com a segurança por duas razões: A) Através de uma SP você pode permitir que usuários façam atualizações no banco que necessitam de “acesso privilegiado” sem dar o acesso privilegiado para os usuários – Você dá acesso para a SP. Dessa forma o usuário só tem acesso privilegiado executando o código da sua SP ao invés de ter acesso a fazer qualquer coisa. B) O código pode ser criptografado.
Em meu ponto de vista essas são as principais vantagens. Existem desvantagens? Creio que sim, principalmente no que se refere a escolher usar “procedures”, “funções”, “views” e/ou outros tipos de código armazenado. Nesse exato momento, estou pensando que esse assunto merece um artigo específico… rsrsrsrs 🙂 Se cadastre na minha lista que já anotei esse tema para conversarmos aqui nos próximos artigos.
Se você quiser se adiantar e dar mais uma pesquisada, separei esse artigo da Microsoft para você continuar seus estudos.
4 formas de INSERIR o RESULTADO de uma PROCEDURE em uma TABELA do SQL Server
Bem, voltando ao tema central, aqui vão os 4 métodos para salvar o resultado de uma SP em uma tabela do SQL.
Insert-Execute
O primeiro método é o Insert/Execute:
-------------------------------------------------- -- Método 1: Insert/Execute -------------------------------------------------- -- Salvando resultados insert into resultados_procedure (empresa, data_execucao) execute usp_teste -- Verificando dados inseridos select * from resultados_procedure go
Nesse método, ao invés do SQL mostrar as informações para o usuário em tela, ele as salva na tabela que você escolher. O requisito para usar esse método é que as colunas da tabela escolhida devem ser iguais as colunas retornadas pela sua stored procedure.
Insert from Openrowset
O segundo método utiliza o Openrowset para criar um resultset “selecionável”.
-------------------------------------------------- -- Método 2: Openrowset -------------------------------------------------- -- Requisito: Habilitar Ad Hoc Distributed Queries execute sp_configure 'show advanced options', 1 reconfigure go execute sp_configure 'ad hoc distributed queries', 1 reconfigure go -- Executando proc com openrowset insert into resultados_procedure (empresa, data_execucao) select * from openrowset('msdasql', 'driver={sql server};server=.;trusted_connection=yes', 'execute curso.dbo.usp_teste') as rowset
O diferencial dessa técnica é que no primeiro modelo você só pode pegar dados do servidor local, enquanto que com o openrowset você pode acessar quaisquer servidores SQL da sua rede.
Importante lembrar que antes de usar o openrowset, você precisará ativar as queries distribuídas como eu fiz no script abaixo. O bom é que você só precisa ativar as querys distribuídas uma única vez no seu SQL Server.
Cláusula Output
O terceiro método usa a cláusula output para inserir e mostrar ao usuário simultaneamente.
-------------------------------------------------- -- Método 3: Insert + cláusula output -------------------------------------------------- -- Inserir resultado na tabela e retornar dados via output alter procedure usp_teste as begin insert into resultados_procedure (empresa, data_execucao) output inserted.* select 'dba-pro' as empresa, getdate() as data_execucao end go execute usp_teste go
Esse terceiro método é interessante porque usando o código da própria stored procedure, podemos inserimos os dados na tabela e ao mesmo tempo mostra-los ao usuário. Isso significa que o usuário não sabe que houve mudança no código original, pois ele continua visualizando o resultado normalmente.
Output de XML
No quarto e último método, usamos o retorno em grid para mostrar os dados ao usuário, e retornamos também um XML via variável. Esse método é muito interessante pela flexibilidade e também por ser transparente para o usuário (assim como o método 3).
Quando retornamos XML, há três formas de trabalhar com nossa stored procedure:
Execução normal
Nesse primeiro método, o XML é retornado, porém o usuário não percebe pois não está carregando o XML em alguma variável.
-- Execução normal execute usp_teste go
Execução recebendo e demonstrando o XML
Nesse segundo método o XML retornado é demonstrado como XML.
-- Execução recebendo o XML declare @xml xml execute dbo.usp_teste @xml output select @xml go
Execução montando o XML recebido para salvar em uma tabela
Nesse terceiro método o XML retornado é demonstrado como tabela, e ai é só fazer um insert/select para inserir os dados do XML em uma tabela se esse for o caso.
declare @xml xml execute dbo.usp_teste @xml output print 'procedure retornou resultados' insert into resultados_procedure select resultado.colunas.value('empresa[1]', 'varchar(100)'), resultado.colunas.value('data_execucao[1]', 'datetime2') from @xml.nodes('row') as resultado(colunas) print 'xml foi desmontado e dados inseridos' go
CONCLUSÃO
Bastante conteúdo né… entretanto, esse é o tipo assunto mais difícil de explicar do que de fazer… Então veja o vídeo, use e abuse dos exemplos de código pronto que fiz para você. Depois me diga qual método você gostou mais…
Abraço do seu amigo Josué