How to execute a script before a view in sql server?

1

I created a view and it fetches information from an X table. However, this X table must be created in a select, before running the view, so that the data is updated.

How can I create something in the sql server so that as soon as the user clicks on the view, sql executes this script before running the view?

I need to execute this drop, select and update first, before the last select is a view:

DROP TABLE dbo.tblTemporaria;

SELECT
CONVERT(VARCHAR(10), dbo.compra.data_entrada, 103) AS [Data Entrada CTE],
dbo.compra.codigo AS [Código Sistema],
SUBSTRING((CONVERT(VARCHAR(MAX), xml_conhecimento)), (PATINDEX('%<infNFe><chave>%', CONVERT(VARCHAR(MAX), xml_conhecimento)) + 43), 6) AS [Nº Nota Fiscal],
dbo.compra.numero_compra AS [Nº Conhecimento],
Transp.nome AS Transportadora,
dbo.cliente_fornecedor.nome AS Cliente,
SUBSTRING(ClassCliente.nome, 3, 50) AS Classificacao,
dbo.cliente_fornecedor.uf_sigla AS UF,
dbo.compra.cfop_codigo AS CFOP,
dbo.compra.valor_total AS [Vlr. Total Frete],
dbo.compra.observacao AS Observações INTO tblTemporaria
FROM dbo.compra 
INNER JOIN dbo.cliente_fornecedor
    ON dbo.cliente_fornecedor.codigo = dbo.compra.clifor_codigo
LEFT OUTER JOIN dbo.cliente_fornecedor AS Transp
    ON Transp.codigo = dbo.compra.tran_codigo
INNER JOIN dbo.classificacao_cliente AS ClassCliente
    ON dbo.cliente_fornecedor.clascli_codigo_1 = ClassCliente.codigo
WHERE (dbo.compra.data_entrada >= '01/01/2015')
AND (dbo.compra.cfop_codigo IN ('2353', '1353'));

    UPDATE tblTemporaria
SET [Nº Nota Fiscal] = NULL
WHERE [Código Sistema] IN (SELECT
    [Código Sistema]
FROM tblTemporaria
WHERE ISNUMERIC([Nº Nota Fiscal]) = 0);

View

SELECT
    r.Cliente,
    r.Transportadora,
    r.[Data Entrada CTE],
    r.[Nº Conhecimento],
    r.[Vlr. Total Frete],
    r.CFOP,
    r.Observações,
    r.[Nº Nota Fiscal],
    CONVERT(VARCHAR(10), NotaFiscal.data, 103) AS [Data Emissão NF],
    NotaFiscal.peso_liquido_volume AS [Peso Líquido],
    NotaFiscal.peso_bruto_volume AS [Peso Bruto],
    NotaFiscal.valor_total_produtos AS [Valor Total Produtos],
    NotaFiscal.valor_total AS [Valor Total Nota]
FROM dbo.tblTemporaria AS r
LEFT OUTER JOIN dbo.nota_fiscal_venda AS NotaFiscal
    ON NotaFiscal.numero_nota = r.[Nº Nota Fiscal]
    
asked by anonymous 09.04.2015 / 16:02

1 answer

0

Code Below creates the procedure below.

create procedure ProcedureTeste
as

DROP TABLE dbo.tblTemporaria;

SELECT
  CONVERT(VARCHAR(10), dbo.compra.data_entrada, 103) AS [Data Entrada CTE],
  dbo.compra.codigo AS [Código Sistema],
  SUBSTRING((CONVERT(VARCHAR(MAX), xml_conhecimento)), (PATINDEX('%<infNFe><chave>%', CONVERT(VARCHAR(MAX), xml_conhecimento)) + 43), 6) AS [Nº Nota Fiscal],
  dbo.compra.numero_compra AS [Nº Conhecimento],
  Transp.nome AS Transportadora,
  dbo.cliente_fornecedor.nome AS Cliente,
  SUBSTRING(ClassCliente.nome, 3, 50) AS Classificacao,
  dbo.cliente_fornecedor.uf_sigla AS UF,
  dbo.compra.cfop_codigo AS CFOP,
  dbo.compra.valor_total AS [Vlr. Total Frete],
  dbo.compra.observacao AS Observações INTO tblTemporaria
FROM 
  dbo.compra 
    INNER JOIN 
  dbo.cliente_fornecedor
      ON dbo.cliente_fornecedor.codigo = dbo.compra.clifor_codigo
    LEFT OUTER JOIN 
  dbo.cliente_fornecedor AS Transp
      ON Transp.codigo = dbo.compra.tran_codigo
    INNER JOIN 
  dbo.classificacao_cliente AS ClassCliente
      ON dbo.cliente_fornecedor.clascli_codigo_1 = ClassCliente.codigo
WHERE 
  (dbo.compra.data_entrada >= '01/01/2015')
  AND (dbo.compra.cfop_codigo IN ('2353', '1353'));

--------------------------------------------------------------------------

UPDATE 
  tblTemporaria
SET [Nº Nota Fiscal] = NULL
WHERE 
  [Código Sistema] IN (SELECT
  [Código Sistema]
FROM 
  tblTemporaria
WHERE 
  ISNUMERIC([Nº Nota Fiscal]) = 0);


--------------------------------------------------------------------------

select 
  nome,
  codigo
from 
  @CadastroNome as a
    inner join 
  cadastro as b
      on a.int_Codigo = b.codigo

SELECT
    r.Cliente,
    r.Transportadora,
    r.[Data Entrada CTE],
    r.[Nº Conhecimento],
    r.[Vlr. Total Frete],
    r.CFOP,
    r.Observações,
    r.[Nº Nota Fiscal],
    CONVERT(VARCHAR(10), NotaFiscal.data, 103) AS [Data Emissão NF],
    NotaFiscal.peso_liquido_volume AS [Peso Líquido],
    NotaFiscal.peso_bruto_volume AS [Peso Bruto],
    NotaFiscal.valor_total_produtos AS [Valor Total Produtos],
    NotaFiscal.valor_total AS [Valor Total Nota]
FROM dbo.tblTemporaria AS r
LEFT OUTER JOIN dbo.nota_fiscal_venda AS NotaFiscal
    ON NotaFiscal.numero_nota = r.[Nº Nota Fiscal]

to execute the procedure you use

exec ProcedureTeste

Below is an example code for executing a procedure through Excel VBA

Public Sub UpdateWithStoredProcedure()
        Dim cmd As New ADODB.Command
        Dim conn As ADODB.Connection
        Dim prm As ADODB.Parameter
        Dim strConn As String
        Dim strSQL As String

        strConn = "Provider=SQLOLEDB.1;" & _
            "Data Source=(local); Initial Catalog=NorthWind;" & _
            "Integrated Security=SSPI"

        Set conn = New ADODB.Connection
        conn.Open strConn

        Set cmd = New ADODB.Command
        cmd.CommandText = "ProcedureTeste"
        cmd.CommandType = adCmdStoredProc
        cmd.ActiveConnection = conn

        Set prm = cmd.CreateParameter("OrderID", adInteger, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("OrderID").Value = 1

        Set prm = cmd.CreateParameter("OrderDate", adDate, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("OrderDate").Value = "1/1/2007"

        Set prm = cmd.CreateParameter("ShipVia", adInteger, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("ShipVia").Value = 2
        Set prm = cmd.CreateParameter("Freight", adCurrency, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("Freight").Value = "10.5"

        'Execute the Stored Procedure
        cmd.Execute

        'Close the connection
        conn.Close
    End Sub
    
09.04.2015 / 17:39