Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
using wildcard

Answered using wildcard

  • Tuesday, August 22, 2006 9:48 AM
     
     
    Sign In to Vote
    0

     

    alter procedure sp_ADsearch @tbname varchar(1000),@searchproduct nvarchar(500)

    as

    declare @sqlstr nvarchar(1000)

    set @sqlstr='select * from '

    set @sqlstr= @sqlstr + @tbname

    set @sqlstr= @sqlstr + ' where name like '

    set @sqlstr= @sqlstr + '%' + @searchproduct + '%'

    exec sp_executesql @sqlstr

    --

    exec sp_ADsearch product,ee

    when executing the procedure i am getting an error "Incorrect syntax near 'ee'.".

    whats wrong with the syntax? how to use % along with variables

     

All Replies

  • Tuesday, August 22, 2006 11:41 AM
     
     
    Sign In to Vote
    0
    Replace the following SQL statement SET @sqlstr=@sqlstr + '%' + @searchproduct +'%' with SET @sqlstr=@sqlstr + '''%''' + @searchproduct + '''%'''. And execute your stored procedure as EXEC sp_APsearch 'product', 'ee'
     
  • Tuesday, August 22, 2006 11:49 AM
     
     
    Sign In to Vote
    0

    That results in

    Where name like %ee%

    And it should be

    Where name like '%ee%'

     

     

     

     
  • Tuesday, August 22, 2006 11:54 AM
     
     Answered
    Sign In to Vote
    0

     

     hi a_shyam41,

                                    i tried myself and got the correct code for it. once again u just check u r code. its not working.

    here is the right one.

    alter procedure sp_ADsearch (@tbname varchar(1000),@searchproduct varchar(500), @debug bit = 1 )

    as

    declare @sqlstr nvarchar(1000)

    set @sqlstr='select * from '

    set @sqlstr= @sqlstr + @tbname

    set @sqlstr= @sqlstr + ' where name like '+ '''%' + @searchproduct + '%'''

    exec sp_executesql @sqlstr

    IF @debug = 1 PRINT @sqlstr

    ---------------------------

    exec sp_ADsearch product,ee

    --*************************

    no need to give product & ee in quotes. My code is working fine for me.

     

     
  • Tuesday, August 22, 2006 11:57 AM
     
     
    Sign In to Vote
    0

     

    Hi a_shyam41

      thank u for ur reply.....