Seach query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Seach query

Author

Topic

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2009-07-17 : 02:26:01

Hi all,

What is the improvement need for the below stored procedure.

CREATE PROCEDURE [dbo].[Sp_usersearch] 

@FromDate varchar(50),

@ToDate varchar(50),

@UserName varchar(50)
     
AS  
  
SELECT * FROM TimeLogSheet TLS 

INNER JOIN ITWP_Users U ON U.UserId = TLS.UserId 

WHERE TLS.IsDeleted = 0 AND (TLS.Date BETWEEN Convert(Varchar,@FromDate,101) AND Convert(Varchar,@ToDate,101))

AND (U.FirstName LIKE  @UserName +'%' OR U.LastName LIKE  @UserName + '%')
  
RETURN

Here Fromdate, Todate and username as a input parameters. Username may be First name Or Last name. Lot of possibilities will come for our search.

1. Only username will be given.
2. Only From date will be given.
3. Only From Date and To date will be given.
4. User name contains First name and last name.
5. User name, From date and to date will be given.

Kidly give me your inputs,

Regards,
Kamal.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-07-17 : 02:56:20

Here instead of putting * can use indivisual column names it will be good from performance prospective..

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2009-07-17 : 03:03:24

Yes. Thanks. But in my original sp i have selected the selected columns. but i need seach option conditions.

kamal.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-17 : 05:56:06

hmm... i have read some article about when you use 'LIKE' you will experience table scan...will you likely to use full text search?might be faster compare to 'LIKE'

Hope can help...but advise to wait pros with confirmation...

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2009-07-17 : 06:49:27

Thanks. Finally i have created the below sp.

ALTER PROCEDURE [dbo].[SP_ITWP_UserTimeSheetList_SelectAllBySearch]

@FromDate varchar(50),

@ToDate varchar(50),

@UserName varchar(50)

AS

DECLARE @SQLQuery As Varchar(8000)

SET @SQLQuery = 'SELECT TLS.UserId, Convert(Varchar,TLS.Date,101) As ''TimeSheetDate'', Convert(Varchar,TLS.SubmittedDateTime,101) AS ''SubmittedDate'', '

SET @SQLQuery = @SQLQuery + 'U.FirstName + '' '' + U.LastName As ''UserName'' '

SET @SQLQuery = @SQLQuery + 'FROM ITWP_TimeLogSheet TLS INNER JOIN ITWP_Users U ON U.UserId = TLS.UserId WHERE TLS.IsDeleted = 0 '

IF @UserName Is Not Null

SET @SQLQuery = @SQLQuery + ' AND U.FirstName LIKE '''+ @UserName +'%'' '

IF (@FromDate Is Not Null) AND (@ToDate Is Not Null)

SET @SQLQuery = @SQLQuery + ' AND (TLS.Date BETWEEN Convert(Varchar,''' + @FromDate + ''',101) AND Convert(Varchar,''' + @ToDate + ''',101))'

SET @SQLQuery = @SQLQuery + ' GROUP By TLS.Date, TLS.Userid, TLS.SubmittedDateTime, U.FirstName , U.LastName '

EXEC @SQLQuery

RETURN

the query creation is good. The separate query is running fine. But I have EXEC @SQLQuery, i met the following error.

Msg 203, Level 16, State 2, Procedure SP_ITWP_UserTimeSheetList_SelectAllBySearch, Line 39
The name 'SELECT TLS.UserId, Convert(Varchar,TLS.Date,101) As 'TimeSheetDate', Convert(Varchar,TLS.SubmittedDateTime,101) AS 'SubmittedDate', U.FirstName + ' ' + U.LastName As 'UserName' FROM ITWP_TimeLogSheet TLS INNER JOIN ITWP_Users U ON U.UserId = TLS.UserId WHERE TLS.IsDeleted = 0 AND U.FirstName LIKE '7/14/2009%' AND (TLS.Date BETWEEN Convert(Varchar,'kamal',101) AND Convert(Varchar,'6/14/2009',101)) GROUP By TLS.Date, TLS.Userid, TLS.SubmittedDateTime, U.FirstName , U.LastName ' is not a valid identifier.

how can i exec this sp. pls help me.

kamal.

kamal.A
Yak Posting Veteran

70 Posts

Posted - 2009-07-17 : 07:30:49

Sorry. I put the EXEC (@SQLQuery) in brackets .. it is running fine.

Kamal.

asgast
Posting Yak Master

149 Posts

Posted - 2009-07-17 : 08:01:42

if you want to compare dates use datetime not varchar and convert

i have seen lots of examples where changing OR to UNION speeds things up

whenever using like '%%' will force table scan, don't expect it to run as lightning

when using dynamic sql try not to EXEC(@sql), use EXEC sp_executesql @sql

using dynamic sql instead of OR is bad, really bad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources