Compare values of two tables in a Procedure

0

I have an application that lists in a grid files that are registered in the table

  

SCF_Process Documentation

However, now I need to list only if this file also exists in the table

  

SCF_ARQUIVO_UPLOAD

What the application does is fetch the file in the first table by the file type ID and list it. What it needs to do now is to get the ID, pick up the document path that is in the Path_Document column of the first table, then compare to the PathPartial, if it does, list them all.

I'm doing the query like this, but it's not listing anything:

ALTER PROC [dbo].[sp_RecuperaDocumentosProcesso] --2

@id_ProcessoCompra INT

AS

SELECT
        id_ProcessoCompra
        ,Path_Documento
FROM SCF_DocumentoProcessoCompra DPC
LEFT JOIN SCF_ARQUIVO_UPLOAD SAP
    ON DPC.id_DocumentoProcessoCompra = SAP.Id_Arquivo

    WHERE DPC.Path_Documento LIKE '%' + SAP.CaminhoParcial + '%'

Create the 2 tables:

USE [SCF2_HOMOLOG_325]
GO

/****** Object:  Table [dbo].[SCF_DocumentoProcessoCompra]    Script Date: 24/01/2018 16:42:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SCF_DocumentoProcessoCompra](
    [id_DocumentoProcessoCompra] [int] IDENTITY(1,1) NOT NULL,
    [id_ModeloDocumento] [int] NULL,
    [Desc_DocumentoProcessoCompra] [varchar](80) NULL,
    [Path_Documento] [varchar](300) NULL,
    [Dt_PublicacaoDocumento] [datetime] NULL,
    [Dt_CadastroDocumento] [datetime] NULL,
    [USR_CadastroDocumento] [varchar](20) NULL,
    [id_ProcessoCompra] [int] NULL,
    [id_TipoDocumento] [int] NULL,
    [FormatoDocumento] [smallint] NULL,
    [Texto_FinalDocumento] [text] NULL,
    [id_TipoModeloDocumento] [int] NULL,
    [ID_TIPOARQUIVO] [int] NULL,
 CONSTRAINT [PK__SCF_DocumentoPro__29572725] PRIMARY KEY CLUSTERED 
(
    [id_DocumentoProcessoCompra] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra]  WITH CHECK ADD  CONSTRAINT [FK__SCF_Docum__id_Pr__55009F39] FOREIGN KEY([id_ProcessoCompra])
REFERENCES [dbo].[SCF_ProcessoCompra] ([id_ProcessoCompra])
GO

ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra] CHECK CONSTRAINT [FK__SCF_Docum__id_Pr__55009F39]
GO

ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra]  WITH CHECK ADD  CONSTRAINT [FK__SCF_Docum__id_Ti__55F4C372] FOREIGN KEY([id_TipoDocumento])
REFERENCES [dbo].[SCF_TipoDocumento] ([id_TipoDocumento])
GO

ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra] CHECK CONSTRAINT [FK__SCF_Docum__id_Ti__55F4C372]
GO

ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra]  WITH CHECK ADD FOREIGN KEY([ID_TIPOARQUIVO])
REFERENCES [dbo].[SCF_TiposDeArquivos] ([ID_TIPOARQUIVO])
GO

ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra]  WITH CHECK ADD  CONSTRAINT [FK_SCF_DocProcesso_ModeloDocumento] FOREIGN KEY([id_ModeloDocumento])
REFERENCES [dbo].[SCF_ModeloDocumentos] ([id_ModeloDocumento])
GO

ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra] CHECK CONSTRAINT [FK_SCF_DocProcesso_ModeloDocumento]
GO

ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra]  WITH CHECK ADD  CONSTRAINT [FK_SCF_id_TipoModeloDocumento] FOREIGN KEY([id_TipoModeloDocumento])
REFERENCES [dbo].[SCF_TipoModeloDocumento] ([id_TipoModeloDocumento])
GO

ALTER TABLE [dbo].[SCF_DocumentoProcessoCompra] CHECK CONSTRAINT [FK_SCF_id_TipoModeloDocumento]
GO



USE [SCF2_HOMOLOG_325]
GO

/****** Object:  Table [dbo].[SCF_ARQUIVO_UPLOAD]    Script Date: 24/01/2018 16:42:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SCF_ARQUIVO_UPLOAD](
    [Id_Arquivo] [bigint] IDENTITY(1,1) NOT NULL,
    [CaminhoParcial] [varchar](max) NOT NULL,
    [ArquivoBinario] [varbinary](max) NOT NULL,
    [Dt_HoraUpload] [datetime] NOT NULL,
    [Id_Usuario] [int] NULL,
 CONSTRAINT [PK_SCF_Arquivo_Upload] PRIMARY KEY CLUSTERED 
(
    [Id_Arquivo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[SCF_ARQUIVO_UPLOAD]  WITH CHECK ADD  CONSTRAINT [FK_SCF_Arquivo_Upload_ACS_Usuario1] FOREIGN KEY([Id_Usuario])
REFERENCES [dbo].[ACS_Usuario] ([id_Usuario])
GO

ALTER TABLE [dbo].[SCF_ARQUIVO_UPLOAD] CHECK CONSTRAINT [FK_SCF_Arquivo_Upload_ACS_Usuario1]
GO
    
asked by anonymous 24.01.2018 / 19:29

0 answers