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.
