July 4, 2008 at 12:36 am
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.
July 4, 2008 at 7:05 am
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 + '%'
July 4, 2008 at 8:15 am
Thanks
it worked
July 4, 2008 at 9:18 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply