problema tsql

[alfinete]

alter PROCEDURE DNVTTransactionsSELBy

@TransactionID        int            ,
@Mobile                varcHar(20)    ,
@Identification        varcHar(20)    ,
@DriverLicense        varcHar(20)    ,
@Plate                varcHar(20)    ,
@Name                varcHar(100)

AS

declare @QUERY NVARCHAR(max) -- comtem o nosso select
declare @FILTRO NVARCHAR(max)-- guarda o filtro 
declare @arg smallint -- variavel de controlo se ja foi criado o where ou não  

BEGIN

SET NOCOUNT ON;

set @arg = 0

set @QUERY = 'select 
                         Name,
                         Plate,
                         DriverLicense,
                         Identification,
                         Mobile,
                         Residence,
                         Municipal,
                         Model,
                         Entity,
                         Brand,
                         Chassis,
                         DU,
                         BirthDate
              from Transactions '

SET @FILTRO = ''

--***********************************************************
-- transationID
--***********************************************************

IF @TransactionID <> 0 or @TransactionID <> null
begin
    if @arg = 0
        begin
            SET @FILTRO = @FILTRO + 'where TransactionID = @TransactionID '
            set @arg = @arg +1
        end
    else
        begin
            SET @FILTRO = @FILTRO + 'and TransactionID = @TransactionID '
        end 
end

----***********************************************************
----Mobile
----***********************************************************

IF @Mobile !='' or @Mobile !=null
begin
    if @arg = 0
        begin
            SET @FILTRO = @FILTRO + 'where Mobile = @Mobile '
            set @arg = @arg +1
        end
    else
        begin
            SET @FILTRO = @FILTRO + 'and Mobile = @Mobile '
        end 
end

--***********************************************************
--Identification
--***********************************************************

IF @Identification !='' or @Identification !=null
begin
    if @arg = 0
        begin
            SET @FILTRO = @FILTRO + 'where Identification = @Identification '
            set @arg = @arg +1
        end
    else
        begin
            SET @FILTRO = @FILTRO + 'and Identification = @Identification '
        end 
end


--***********************************************************
--DriverLicense
--***********************************************************

IF @DriverLicense !='' or @DriverLicense !=null
begin
    if @arg = 0
        begin
            SET @FILTRO = @FILTRO + 'where DriverLicense = @DriverLicense '
            set @arg = @arg +1
        end
    else
        begin
            SET @FILTRO = @FILTRO + 'and DriverLicense = @DriverLicense '
        end 
end

--***********************************************************
--Plate
--***********************************************************

IF @Plate !='' or @Plate !=null
begin
    if @arg = 0
        begin
            SET @FILTRO = @FILTRO + 'where Plate = @Plate '
            set @arg = @arg +1
        end
    else
        begin
            SET @FILTRO = @FILTRO + 'and Plate = @Plate '
        end 
end

--***********************************************************
--Name
--***********************************************************

IF @Name !='' or @Name !=null
begin
    if @arg = 0
        begin
            SET @FILTRO = @FILTRO + 'where [Name] like @Name% '
            set @arg = @arg +1
        end
    else
        begin
            SET @FILTRO = @FILTRO + 'and [Name] like @Name% '
        end 
end


SET @QUERY =  @QUERY + @FILTRO



EXECUTE sp_executesql  @QUERY , N' @TransactionID int,@Mobile varcHar(20),
                                   @Identification    varcHar(20),
                                   @DriverLicense varcHar(20),
                                   @Plate    varcHar(20),
                                   @Name varcHar(100) ',
                                   @TransactionID,@Mobile,@Identification,
                                   @DriverLicense,@Plate,@Name
end

tenho esta store para criar dinamicamente o filtro conforme os parametros vem null ou não

estou com um problemita , funcinam tdos os parametros excepto quando passo o neme com valor

dá o seguinte erro na percentagem '%'

Msg 102, Level 15, State 1, Line 19
Incorrect syntax near '%'.

(1 row(s) affected)

gostave de uma ajuda

a mim parece estar tudo bem , não sei o que se passa

 

[alfinete]

problema resolvido

IF @Name !='' or @Name !=null
begin
    if @arg = 0
        begin
            SET @FILTRO = @FILTRO + 'where [Name] like '''+@name+' %'''
            set @arg = @arg +1
        end
    else
        begin
            SET @FILTRO = @FILTRO + 'and [Name] like '''+@name+' %'''
        end 
end

obrigada na mesma