Dicas - Codificando Stored Procedures

Criar Stored Procedures é muito útil em qualquer projeto, pois nos proporciona grande flexibilidade no desenvolvimwento. Durante toda nossa atuação em projetos sempre temos algo a aprender e melhorar. Neste intuito, segue abaixo alguns recursos interessantes que podem ser aplicados a qualquer tipo de projeto, pequeno ou grande.

1 - Uso de Cursor
Em projetos grandes o uso de cursor acaba degradando a performance de sua Stored Procedure. Como alternativa, segue recurso que substitui o uso de cursores.

-- Como exemplo, criaremos uma Tabela Temporária qualquer
CREATE TABLE #tmp
(
 cod int,
 valor nvarchar(max)
)

INSERT INTO #tmp VALUES (1, 'Valor1')
INSERT INTO #tmp VALUES (2, 'Valor2')
INSERT INTO #tmp VALUES (3, 'Valor3')

-- Criamos uma nova temporária que vai contér o [ID] de cada linha, para efetuarmos o LOOP.
SELECT ROW_NUMBER() OVER(ORDER BY cod) AS row, valor
INTO #tmpLoop
FROM #tmp

-- Criamos o processo de Loop
DECLARE @iTmp INT = 1
DECLARE @countTmp INT
SELECT @countTmp = COUNT(row) FROM #tmpLoop

DECLARE @valor NVARCHAR(MAX)

WHILE (@iTmp <= @countTmp)
BEGIN 
 SELECT @valor = valor FROM #tmpLoop WHERE row = @iTmp

 SELECT @valor

 SET @iTmp = @iTmp + 1
END

2 - Retorno de Valores
É útil executar, dentro de sua Stored Procedure, uma outra para efetuar algum processo e retornar um valor. Segue exemplos abaixo.

-- Prepara a variável que receberá o valor e a execução da Proc
DECLARE @Valor NVARCHAR(MAX)
EXEC BuscarValor
  @id = 'passa algum parâmetro para a proc',
  @pValor = @Valor OUTPUT

SELECT @Valor

CREATE PROCEDURE BuscarValor
 @id NVARCHAR(MAX),
 @pValor NVARCHAR(MAX) OUTPUT
AS
BEGIN

 -- Efetua alguma busca e alimenta a variável de retorno [@pValor]
 SET @pValor = 'Algum Valor...'

END

Uma forma interessante para alimentar uma variável (@descricao) a partir da execução de um SQL dinâmico

DECLARE @ParameterDefinition NVARCHAR(MAX) = '@descricao NVARCHAR(MAX) OUTPUT'
DECLARE @SQL NVARCHAR(MAX)
DECLARE @descricao NVARCHAR(MAX)

SET @SQL = ' SELECT TOP 1 @descricao = descricao'
SET @SQL = @SQL + ' FROM TB'
SET @SQL = @SQL + ' WHERE ...'

EXECUTE sp_executesql @SQL,
       @ParameterDefinition,
       @descricao OUTPUT

SELECT @descricao

Ter como retorno uma TABELA também é útil

CREATE TABLE #tmp
(
 nome nvarchar(max),
 valor nvarchar(max)
)

EXEC BuscarTabela
  @id = 1

SELECT * FROM #tmp

CREATE PROCEDURE BuscarTabela
(
 @id INT
)
AS
BEGIN

 INSERT INTO #tmp
  SELECT *
   FROM TB
   WHERE ...

END

3 - Manipulação de XML
Com a função [OPENXML] do Sql Server fica fácil converter um XML em uma tabela.

DECLARE @xml XML, @idoc int

-- Imagine o seguinte XML
SET @xml = '<root>
 <campos>
  <campo campo_id="1" campo_nome="campo1" />
  <campo campo_id="2" campo_nome="campo2" />
  <campo campo_id="3" campo_nome="campo3" />
 </campos>
</root>'

IF OBJECT_ID('tempdb..#xmlTable') IS NOT NULL DROP TABLE #xmlTable
CREATE TABLE #xmlTable
(
 campo_id nvarchar(max), 
 campo_nome nvarchar(max)
)

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

INSERT INTO #xmlTable
SELECT *
FROM OPENXML (@idoc, '/root/campos/campo', 2)
 WITH (
   campo_id nvarchar(max) '@campo_id', 
   campo_nome nvarchar(max) '@campo_nome'
   );

EXEC sp_xml_removedocument @idoc

SELECT * FROM #xmlTable

4 - PIVOT Table (CrossTab) 
Com o resurso [pivot] do Sql Server a tarefa fica bem mais simples. Analise o exemplo abaixo.

-- Imagine uma [TABELA_ORIGINAL] que contém a estrutura:
-- linha INT
-- campo NVARCHAR(MAX)
-- valor NVARCHAR(MAX)
-- 
-- EXEMPLO:
-- linha campo valor
-- ---------------------------
-- 1  c1 valor1
-- 1  c2 valor2
-- 2  c1 valor3
-- 2  c2 valor4
--
-- E deseja converter os valores do campo [campo] em COLUNAS
-- Execute a Proc abaixo para montar o SQL que realizará esta tarefa

-- e terá como resultado
--
-- linha c1 c2
-- ---------------------------
-- 1  valor1 valor2
-- 2  valor3 valor4

IF OBJECT_ID('tempdb..#tmpCampos') IS NOT NULL DROP TABLE #tmpCampos

CREATE TABLE #tmpCampos
(
 row int,
 campo nvarchar(max)
)

DECLARE @Insert NVARCHAR(MAX)
SET @Insert = 'INSERT INTO #tmpCampos'
SET @Insert = @Insert + ' SELECT ROW_NUMBER() OVER(ORDER BY campo) row, campo'
SET @Insert = @Insert + ' FROM TABELA_ORIGINAL'
SET @Insert = @Insert + ' GROUP BY campo'
SET @Insert = @Insert + ' ORDER BY campo'
EXEC(@Insert)
 
DECLARE @cols AS NVARCHAR(max), @campos AS NVARCHAR(max), @icampos INT
SET @icampos = (SELECT min(row) FROM #tmpCampos)
 
--Variável para conter a relação de campos
SET @cols = N''
--Montagem da relação de campos
WHILE (@icampos is not null)
BEGIN
 SELECT @campos = campo FROM #tmpCampos WHERE row = @icampos
 SET @cols = @cols+N',[' + @campos + N']'
 SET @icampos = (SELECT min(row) FROM #tmpCampos WHERE row > @icampos)
END

SET @cols = substring(@cols,2,len(@cols))

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'SELECT *'
SET @SQL = @SQL + N' FROM (SELECT linha,campo,valor FROM TABELA_ORIGINAL) AS p'
SET @SQL = @SQL + N' PIVOT (MAX(valor) FOR campo IN (' + @cols + N')) AS pv'

That's It.