SQL SERVER sp_executesql incorrect syntax near ')'

Question

I have this problem when I am trying to execute sp_ExecuteSql in sql server 2014 there is my stored procedure :

alter proc search
@Name nvarchar 
as
declare @SQL nvarchar
declare @Params nvarchar
begin
    set @SQL = N' Select * from Table_1 , Table_2 where (1=1) '

    if @Name is not null
        begin
        set @SQL = @SQL + ' and Table_2.thatoneID = ( Select Table_1.Id from Table_1 where Table_1.Name like ''%''+@Name+''%'' ) '
        end
    set @SQL = @SQL + ' and Table_2.thatoneID = Table_1.Id ; '

    set @Params = '@Name nvarchar'

    execute sp_executesql @SQL , @Params , @Name 
end

When I am trying to :

Execute search 'hh'

I got this Error

Msg 102, Level 15, State 1, Line 29 Incorrect syntax near ')'.

Answer 1

You should always specify a length of a variable, so nvarchar(100) otherwise it will just default to 1 character.

Just test what your variables have stored by printing them:

print @sql
print @params

With no length set, the only thing printed is

@

Why only "@" and not the "S" from the SELECT keyword, for the @sql variable? Because you have a whitespace at the start of the @sql variable.

As opposed to when setting a size for each variable (I'll just go with max, cause I don't know what length you actually need):

declare @SQL nvarchar(max)
    , @Name nvarchar(max)
declare @Params nvarchar(max)
begin
    set @SQL = N' Select * from Table_1 , Table_2 where (1=1) '

    if @Name is not null
        begin
        set @SQL = @SQL + ' and Table_2.thatoneID = ( Select Table_1.Id from Table_1 where Table_1.Name like ''%''+@Name+''%'' ) '
        end
    set @SQL = @SQL + ' and Table_2.thatoneID = Table_1.Id ; '

    set @Params = '@Name nvarchar'

    print @sql
    print @params

end

Which will give you:

 Select * from Table_1 , Table_2 where (1=1)  and Table_2.thatoneID = Table_1.Id ; 
@Name nvarchar

I suggest you try this first and see if you still get the error.