How to run a script before a view on sql server?

Question

I created a view and it looks for information from an X table. Only this table X, must be created in a select, before executing the view, for the data to be updated.

It has to create something in sql server, so as soon as the user clicks on the view, sql runs this script before running the view?

I need to run this drop, select and update first, before the last select which 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]

Answer 1

Code Below creates the code 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 run the trial you use

exec ProcedureTeste

Below example code to run a precedent 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