how to pass parameter in like'%' format

• jmamphekgo

  SSC Enthusiast

  Points: 103

  More actions

  July 4, 2008 at 12:36 am

  #209498

  hi

  i want pass parameter in like '%'.for the example if i want names that begin with j from my table iwould jus"SELECT Emp_No,Emp_Name FROM Employees WHERE Emp_Name Like 'J%'so want to pass parameter as unknown values and enter it.

• Kent Waldrop

  Hall of Fame

  Points: 3064

  More actions

  Follow

  July 4, 2008 at 7:05 am

  #838347

  Is what you are looking for basically something along the line of:

  create procedure emp_proc @nameParm varchar(30)

  as

  SELECT Emp_No,Emp_Name FROM Employees

  WHERE Emp_Name Like @nameParm + '%'

  ??

  To search for names that start with 'J', this would be called with SQL statements similar to:

  exec emp_proc 'J'

  or

  exec emp_proc @nameParm = 'J'

  or

  emp_proc 'J'

  Give a look at articles related to procedures in books online.

  As an aside, it is good that you are looking for names that "start with" a certain string. The performance of a clause such as

  WHERE Emp_Name Like @nameParm + '%'

  optimizes better than

  WHERE Emp_Name Like '%' + @nameParm + '%'

• jmamphekgo

  SSC Enthusiast

  Points: 103

  More actions

  July 4, 2008 at 8:15 am

  #838366

  Thanks

  it worked

• Vijaya Kadiyala

  SSCrazy Eights

  Points: 9149

  More actions

  July 4, 2008 at 9:18 am

  #838383

  Hi,

  If you have '%' in your column values then you need to implement this in special way. I tought i woule like to give you some information on this.

  There are several characters that have special meaning within a SQL query, for example the percent sign (%) in a LIKE query is a wildcard that essentially means "any number of characters can go here." Likewise, the underscore (_) is a wildcard that says "any single character can go here." So what if you are actually looking for a value that contains a literal percent sign? You will end up with bizarre results if you try the following:

  SELECT columns FROM table WHERE

  column LIKE '%%%'

  Instead, you can try one of the following solutions:

  SELECT columns FROM table WHERE

  column LIKE '%[%]%'

  -- or

  SELECT columns FROM table WHERE

  column LIKE '%\%%' ESCAPE '\'

  The first query 'delimits' the special character with square brackets, telling the engine to treat it as a normal literal character instead of a character with special meaning. The second query uses a custom escape character -- you can use any character you like, just be careful that you aren't also expecting to use it as part of the literal string.

  Thanks -- VJ

  http://dotnetvj.blogspot.com

  Thanks -- Vijaya Kadiyala
  www.dotnetvj.com
  SQL Server Articles For Beginers