Depois de ter lido este artigo, irá aprender o básico de um SQL dinâmico, como compilar as declarações com base em valores variáveis e como executar as instruções construídas usando o sp_executesql e executar() de dentro de um procedimento armazenado.
Todos os exemplos encontrados nesta lição são baseados no Microsoft SQL Server Management Studio e nas bases de dados de amostras da AdventureWorks e WideWorldImporters. Você pode começar a usar essas ferramentas gratuitas usando o meu guia, começando a usar o servidor SQL.,
construir SQL dinâmico num procedimento armazenado.
muitos SQL que escrevemos estão explicitamente escritos no procedimento armazenado. Isto é o que chamamos de SQL estática. Esse nome é dado porque não muda. Uma vez escrito, significa que é martelado em pedra.
abaixo está um exemplo de um SQL estático:
SELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1970GROUP BY JobTitleSELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1971GROUP BY JobTitle
notou que existem duas afirmações aqui? Cada declaração retorna um resumo dos postos de trabalho para um ano de nascimento específico do empregado. Se queremos adicionar mais anos de nascimento, então precisamos adicionar mais declarações., E se só tivéssemos de escrever a Declaração uma vez e ser capazes de mudar o ano de avião?
é aqui que a dinâmica SQL entra em jogo.
SQL dinâmico é SQL que é criado e executado em tempo de execução. Parece complicado, mas realmente não é. em vez de ter as declarações digitadas diretamente no procedimento armazenado, as declarações SQL são primeiro construídas e definidas em variáveis.
o código nestas variáveis é então executado. Agora, vamos continuar com o nosso exemplo, aqui está o mesmo código usando a dinâmica SQL:
a dinâmica SQL é realçada em verde., Este é o SQL que é construído para cada @birthYear. À medida que o SQL é construído, ele é armazenado na declaração@. É então executado usando sp_executesql, que explicaremos abaixo.
Introdução ao sp_executesql
pode usar o sp_executeslq para executar o transact SQL armazenado dentro de uma variável. O formulário de declaração é
EXECUTE sp_executesql @statement.
Em caso de dúvida, o sp_executesql é um procedimento armazenado no sistema. Os procedimentos armazenados do sistema estendem o idioma e fornecem mais recursos para você usar.,
Aqui está um exemplo simples para tentar:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE sp_executesql @statement
Se você executar esta na janela de consulta, você vai obter um resultado parecido como este:
2018-01-24 18:49:30.143
Agora que você já viu como sp_executeslq funciona, vamos colocá-lo em prática. Vamos assumir que você foi solicitado a escrever um procedimento de loja que retorna ou a média LineTotal ou soma de LineTotal por ProductID para produtos enviados em 2011.
Seu chefe preferiria ter isso escrito como um procedimento armazenado. O procedimento armazenado deve aceitar um parâmetro @ReturnAverage., Se for verdade, devolverá a média, caso contrário a soma.
é claro, você poderia escrever isso como duas consultas separadas, como mostrado no seguinte proc armazenado, mas que não seria muito divertido, como seria muito digitação e propenso a erros!
CREATE PROCEDURE uspCalcuateSalesSummaryStatic@returnAverage bitASIF (@returnAverage = 1)BEGIN SELECT SOD.ProductID, AVG(SOD.LineTotal) as ResultAvg FROM Sales.SalesOrderDetail SOD INNER JOIN Sales.SalesOrderHEader SOH ON SOH.SalesOrderID = SOD.SalesOrderID WHERE YEAR(SOH.ShipDate) = 2011 GROUP BY SOD.ProductIDENDELSEBEGIN SELECT SOD.ProductID, SUM(SOD.LineTotal) as ResultSum FROM Sales.SalesOrderDetail SOD INNER JOIN Sales.SalesOrderHEader SOH ON SOH.SalesOrderID = SOD.SalesOrderID WHERE YEAR(SOH.ShipDate) = 2011 GROUP BY SOD.ProductIDEND
a parte má aqui é que há um monte de código duplicado, que eu colori verde. Não há muito código único, mas que há, é vermelho colorido.com toda esta redundância, temos uma grande oportunidade de mostrar algum SQL dinâmico. Vamos a isso!,
aqui, em vez de ter duas versões completas do SQL, uma para o AVG, a outra para o SUM, nós construímos a versão solicitada on-the-fly.
o SQL é construído e gravado na variável @instrução. Esta variável é construída com base no valor do parâmetro @returnAverage. Se definido para 1, então @função representa a média; caso contrário, soma.
Você pode ver onde o SQL é então construído para criar a instrução. Repara no código de cores. Ele deve corresponder porções semelhantes dentro da versão estática; isso deve ajudá-lo a fazer uma comparação.,
Debugging Dynamic SQL
pode estar a perguntar-se como é a SQL no tempo de execução. Você pode facilmente inspecionar o código usando o depurador:
execute o procedimento armazenado usando o comando de execução do depurador, e depois entre no código
Continue a entrar no código até que você leia a instrução executar seleccionada abaixo.
uma Vez que você chegar a esta instrução, passe o mouse sobre a @instrução, e quando a dica de ferramenta é exibida, selecione o texto visualizer.,o depurador é poderoso e vale a pena ser entendido. Se o encorajasse a aprender mais sobre isso aqui.
usando sp_executesql com parâmetros
pode usar sp_executesql para parâmetros de referência dentro da sua declaração. Isso, em última análise, torna o seu código mais fácil de ler e fornece alguns benefícios de otimização como a declaração pode ser compilada uma vez e reutilizada muitas vezes.
A declaração toma a forma:
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
então vamos explicar as peças.
- @a instrução SQL que desejamos executar.,
- @parameterDefinition é uma cadeia que contém uma definição de todos os parâmetros referenciados em @statement. Cada parâmetro e tipo encontrado @declaração está listado. O nome e o tipo são separados por um espaço. Vários parâmetros são separados por uma vírgula.
em seguida, definimos os valores dos parâmetros, especificando os parâmetros e o valor desejado. Os parâmetros são listados na ordem definida dentro da cadeia de parameterdefinição@.
- @parm1 é o primeiro parâmetro definido dentro da cadeia de parameterdefinição@. Valor é o valor, você deseja configurá-lo para.,
- @parm2, é o segundo parâmetro, se define, como declarado em @parameterdefinição.
- e assim por diante…
Aqui está um exemplo simples que adicionar dois números, para o tentar:
As várias partes do mapa são codificadas por cor:
- @instrução (verde) – aviso prévio inclui 2 parâmetros: @e @b. Também observe que estas não são declarados em TSQL. Em vez disso, eles são definidos na definição do parâmetro.
- @parameterDefinition – azul) – cada parâmetro listado é definido como tipo int.
- valores dos parâmetros (vermelho) – definimos aqui o valor dos parâmetros.,
para terminar, neste exemplo temos uma instrução SQL executada dinamicamente que adiciona dois parâmetros.estes parâmetros são definidos como inteiros. O valor de cada parâmetro é definido no comando sp_ ExecuteSQL.
exemplo usando sp_ ExecuteSQL com parâmetros
vamos pegar o nosso exemplo anterior e ampliá-lo. Em vez de codificar a data da nave na consulta como fizemos, vamos trazer isso como um parâmetro. Isso torna a consulta mais flexível e para trabalhar com outros anos além de 2011.,
para fazer esta alteração, adicionaremos um parâmetro ao nosso procedimento armazenado, bem como à consulta dinâmica. Usaremos o comando sp_executesql para chamar a consulta dinâmica usando estes parâmetros.
o procedimento atualizado armazenado com alterações é mostrado abaixo. O parâmetro de procedimento armazenado é verde, e o parâmetro de consulta dinâmica vermelho.
para executar isto, basta chamar o proc uspcalatesalessalessummarydynamic2 a partir de uma janela de consulta usando o seguinte comando:
EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011
Se o fizer, verá estes resultados.,
Deixe-me mostrar-lhe uma multa de simplificação, vamos combinar @shipDateYear e @shipDate em um parâmetro. Eliminaremos @shipDateYear do nosso código. Isto torna mais fácil de seguir e ler:
Notice that the EXECUTE statement is much simpler, there is no need to assign the SQL statement parameter @shipDateYear to the store procedure parameter @shipdate’s value.
Isto torna a declaração mais compacta e mais fácil de ler., O fluxo parece ler melhor, uma vez que você não tem que fazer ligações mentais entre os parâmetros de procedimento armazenados e os parâmetros SQL
Executar SQL dinâmico com EXECUTE()
Você também pode usar o comando EXEC ou executar para executar SQL dinâmico. O formato para este comando é
EXECUTE (@statement)
Aqui está um exemplo simples para tentar:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE (@statement)
é importante para delimitar @instrução entre parênteses. Se você não fizer a instrução executar pega a instrução@, e em vez de executar o SQL dinâmico, ele pensa que o valor variável é o nome de um procedimento armazenado., Você terá o seguinte erro:
Msg 2812, Level 16, State 62, Line 3Could not find stored procedure "SELECT getdate()".
é claro, isto oferece uma grande dica! Se você desejar, você pode usar variáveis para especificar quais procedimentos armazenados para chamar.
sp_ ExecuteSQL versus EXECUTE
pode estar a perguntar-se porque é que usa o sp_ ExecuteSQL versus EXECUTE. Qual é a diferença entre os dois?
Aqui estão várias razões pelas quais a Microsoft recomenda o uso de sp_executesql para executar SQL dinâmico:
- Com executar todos os parâmetros muito ser convertido de seu tipo nativo para Unicode., Isso dificulta a capacidade do otimizador para combinar o SQL construído dinamicamente com um plano pré-existente.
- usando sp_ ExecuteSQL, o Optimizador reconhece os parâmetros dentro do SQL dinâmico, tornando mais fácil para o Optimizador corresponder aos planos.
- é mais fácil ler consultas parametrizadas do que ler um monte de texto concatenado que as incorpora.as consultas parametrizadas são menos propensas a ataques de injeção de SQL.