Compare values of two tables in a Procedure

Question

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

Scf_documentprocessocompra

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. What she needs to do now is find the ID, take the path of the document that is in the Path_document column of the first table after comparing with the Camioparcial, if it has resulted, list all.

I’m making 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 from 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