Using Contains in SQL server

Where can not be applied directly to a insert , if you want to do so it is by select and that select strong> what inserts , I leave you the documentation . What you require for your case is a conditional, since what you are sending is a parameter .

Example INSERT with SELECT :

--INSERT...SELECT example
INSERT dbo.EmployeeSales
    SELECT 'SELECT', e.EmployeeID, c.LastName, sp.SalesYTD 
    FROM HumanResources.Employee AS e
        INNER JOIN Sales.SalesPerson AS sp
        ON e.EmployeeID = sp.SalesPersonID 
        INNER JOIN Person.Contact AS c
        ON e.ContactID = c.ContactID
    WHERE e.EmployeeID LIKE '2%'
    ORDER BY e.EmployeeID, c.LastName;
GO

Response to your case:

IF @Extension LIKE '%pdf%'
BEGIN
    INSERT INTO ArchivosContratos (IdContrato, Nombre, Extension, Ruta, RutaWeb, IdUsuarioOperacion, fecha, Activo, esContrato)
    VALUES (@IdContrato, @Nombre, @Extension, @Ruta, @RutaWeb, @IdUsuarioOperacion, GETDATE(), 1, 1)
END

If you want to use the WHERE in your SP , I think what you are looking for is to do something similar to:

DECLARE @Extension AS NVARCHAR(255) = 'ejemplo de prueba.pdf';

IF @Extension LIKE '%pdf'
BEGIN
    -- Coloca aquí tu sentencia insert:
    INSERT INTO ArchivosContratos (IdContrato, Nombre, Extension, Ruta, RutaWeb, IdUsuarioOperacion, fecha, Activo, esContrato)
    VALUES (@IdContrato, @Nombre, @Extension, @Ruta, @RutaWeb, @IdUsuarioOperacion, GETDATE(), 1, 1)
END

In my case, what I have done is declare an action variable and use IF with BEGIN & END , look at the attached example.

USE NAMEBD
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_NombreSP]
    @cAccion            varchar(1)      = '',
    @nIdConcepto        int             = -1,
    @cNombre            varchar(100)    = ''

AS
SET NOCOUNT ON

DECLARE @nError     INT
DECLARE @vchDescripcion VARCHAR(100)
DECLARE @cNom VARCHAR(100)

ErrorProc:
    IF @nError <> 0 
        BEGIN
            SELECT  @vchDescripcion = description 
            FROM    master..sysmessages 
            WHERE   error = @nError

            SELECT -1                   Codigo,
                    @nError * -1        CodigoSql, 
                    @vchDescripcion     Descripcion
            RETURN -1
        END

    IF @cAccion = 'C' Or @cAccion = 'M' -- Creación o Modificación
        BEGIN

            if @cAccion = 'C'                               -- Inserta nuevo
                BEGIN
                    --INSERT INTO

                END
            else        
                BEGIN                                   -- Actualiza 
                    --UPDATE
                    SET @nError = @@Error
                END

        END

    ELSE                                        -- Solicitud de Eliminacion
        BEGIN
            --DELETE
        END
    IF @nError <> 0    
        GOTO ErrorProc
    ELSE
        SELECT  @nIdConcepto            Codigo,
                0                       CodigoSql, 
                'Ok'                    Descripcion

RETURN 0