3

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 ')'.
mohammad.pro
  • 53
  • 5
  • 5
    You should always specify a length of a variable, so `nvarchar(100)` otherwise it will just default to 1 character – Radu Gheorghiu Aug 24 '16 at 08:06
  • Also, when working with unicode text, use the unicode literals (`N'%'`). Though in this case, you're not going to use any indices anyway :D – Luaan Aug 24 '16 at 08:38
  • Why dynamic SQL? Why old style joins? Keep your SP simple. – gofr1 Aug 24 '16 at 08:48

2 Answers2

2

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.

Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
  • thanks that fixed my problem but I get another one Msg 512, Level 16, State 1, Line 32 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. – mohammad.pro Aug 24 '16 at 08:19
  • @mohammad.pro Well, then that should imply you create another question. Keep things distinct. – Radu Gheorghiu Aug 24 '16 at 08:19
  • it's working man thanks ! ^_^ , the 2nd problem was with '=' "subquery" it should be 'in' instead of equal – mohammad.pro Aug 24 '16 at 08:37
1

At first - please use table aliases.

Second, rewrite your query, you are using old ANSI style.

Third - define nvarchar size.

Forth - why you even use dynamic SQL??

ALTER PROC search
    @Name nvarchar(max)
AS

SELECT * 
FROM Table_1 t1 
INNER JOIN Table_2 t2 
    ON t2.thatoneID = t1.Id 
WHERE (1=1) AND t1.Name LIKE '%'+@Name+'%'

If you insist in using dynamic SQL you can use this:

ALTER PROC search
    @Name nvarchar(max)
AS
DECLARE @SQL nvarchar(max),
        @Params nvarchar(max)

SELECT @SQL = N'
SELECT * 
FROM Table_1 t1 
INNER JOIN Table_2 t2 
    ON t2.thatoneID = t1.Id 
WHERE (1=1) AND t1.Name LIKE ''%''+@Name+''%'''

PRINT @sql
SET @Params = '@Name nvarchar(max)'

EXEC sp_executesql @SQL, @Params, @Name = @Name
gofr1
  • 15,741
  • 11
  • 42
  • 52