April 26, 2013 at 7:23 am
Hi,
I want to search student information depending upon the passing either values of firstname,lastname,email. My requirement is even if I entered first 3 characters then the information of student should display. I have written SP below.its working fine but if I am not passing any search condition then it shows all records.But i dont want to display any record if i pass blank record.
CREATE PROCEDURE [dbo].[ProSearchStudent]
@Search varchar(50)
AS
Set NoCount On
Declare @SQL varchar(max)
Select @SQL = 'select StudentId
,LastName
,FirstName
,Email from StuDentInfo
WHERE LTRIM(RTRIM(LastName)) like '''+@Search+'%''
or LTRIM(RTRIM(FirstName)) like '''+@Search+'%''
or LTRIM(RTRIM(Email)) like '''+@Search+'%''
'
exec(@SQL)
print @sql
--exec Usp_SearchStudents ''
thanks
Abhas.
April 26, 2013 at 7:49 am
abhas (4/26/2013)
Hi,I want to search student information depending upon the passing either values of firstname,lastname,email. My requirement is even if I entered first 3 characters then the information of student should display. I have written SP below.its working fine but if I am not passing any search condition then it shows all records.But i dont want to display any record if i pass blank record.
CREATE PROCEDURE [dbo].[ProSearchStudent]
@Search varchar(50)
AS
Set NoCount On
Declare @SQL varchar(max)
Select @SQL = 'select StudentId
,LastName
,FirstName
,Email from StuDentInfo
WHERE LTRIM(RTRIM(LastName)) like '''+@Search+'%''
or LTRIM(RTRIM(FirstName)) like '''+@Search+'%''
or LTRIM(RTRIM(Email)) like '''+@Search+'%''
'
exec(@SQL)
print @sql
--exec Usp_SearchStudents ''
thanks
Abhas.
You need to read up on sql injection. Your code is a textbook case of vulnerability. NEVER NEVER NEVER execute parameters directly. It is a recipe for disaster. Consider what would happen if the user entered the following in the search text box.
'';create database hacked;--
You might also consider storing your data with LTRIM(RTRIM()) instead of doing that when you retrieve it. Those functions have killed any index usage on those columns.
For the best way to handle this type of catch all query you should read this article. It explains an excellent method for doing this type of thing.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 26, 2013 at 8:09 am
NOTE Sean Lange's comments should / must be given every consideration...
although the following will do the task you require You might want to modify your T-SQL statement to:
CREATE PROCEDURE [dbo].[ProSearchStudent]
@Search varchar(50) = ''
AS
IF (DATALENGTH(RTRIM(LTRIM((@Search))))) = 0
PRINT 'Invalid entry'
Return
Set NoCount On
Declare @SQL varchar(max)
Select @SQL = 'select StudentId
,LastName
,FirstName
,Email from StuDentInfo
WHERE LTRIM(RTRIM(LastName)) like '''+@Search+'%''
or LTRIM(RTRIM(FirstName)) like '''+@Search+'%''
or LTRIM(RTRIM(Email)) like '''+@Search+'%'
April 27, 2013 at 4:18 pm
What dynamic SQL is for?
CREATE PROCEDURE [dbo].[ProSearchStudent]
@Search varchar(50) = ''
AS
IF (DATALENGTH(RTRIM(LTRIM((@Search))))) = 0
PRINT 'Invalid entry'
Return
Set NoCount On
select StudentId, LastName, FirstName, Email
from StuDentInfo
WHERE LTRIM(LastName) like @Search+'%'
or LTRIM(FirstName) like @Search+'%'
or LTRIM(Email) like @Search+'%'
I removed RTRIM as it's absolutely pointless here.
As for LTRIM - see the comment above.
But with this kind of query - it's gonna be a table scan anyway in most cases, so it does not really matter if it's used or not.
It's gonna perform fine on smaller data sets, and it's gonna cause timeouts on larger data sets with or without LTRIM.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply