June 22, 2009 at 12:00 am
declare @temp table
(
ID bigint identity(1,1),
Assesmentname Nvarchar(200),
NoOfQuestions INT,
ThresholdScoreToPass bigint,
LastModifiedDate datetime
)
Insert into @temp ( Assesmentname ,NoOfQuestions ,ThresholdScoreToPass,LastModifiedDate )
values('test',10,35,getutcdate())
Insert into @temp ( Assesmentname ,NoOfQuestions ,ThresholdScoreToPass,LastModifiedDate )
values('testprep',15,35,getutcdate())
Insert into @temp ( Assesmentname ,NoOfQuestions ,ThresholdScoreToPass,LastModifiedDate )
values('Unittest',5,35,getutcdate())
Insert into @temp ( Assesmentname ,NoOfQuestions ,ThresholdScoreToPass,LastModifiedDate )
values('progress',5,35,getutcdate())
Insert into @temp ( Assesmentname ,NoOfQuestions ,ThresholdScoreToPass,LastModifiedDate )
values('lessontest',5,35,getutcdate())
Insert into @temp ( Assesmentname ,NoOfQuestions ,ThresholdScoreToPass,LastModifiedDate )
values('programngtest',5,35,getutcdate())
Insert into @temp ( Assesmentname ,NoOfQuestions ,ThresholdScoreToPass,LastModifiedDate )
values('science',5,35,getutcdate())
Insert into @temp ( Assesmentname ,NoOfQuestions ,ThresholdScoreToPass,LastModifiedDate )
values('social',5,35,getutcdate())
--select * from @temp
declare @searchfield nvarchar(max),
@searchtext nvarchar(max)
SET @searchfield = '1,2'
SET @searchtext = '5' -- Here
declare @SearchTable table
(
FieldID int,
ColumnName NVARCHAR(255),
IsSearch bit
)
insert into @SearchTable select 1,'Assesmentname',0
insert into @SearchTable select 2,'NoOfQuestions',0
insert into @SearchTable select 3,'ThresholdScoreToPass',0
insert into @SearchTable select 4,'LastModifiedDate',0
UPDATE @SearchTable SET IsSearch = 1
WHERE FieldID like '%['+@searchfield+']%'
-- select * from @SearchTable
SELECT * FROM @temp T
WHERE
EXISTS(
SELECT 1 FROM @SearchTable S
WHERE
(ColumnName = 'Assesmentname'
AND IsSearch = 1 and FieldID = 1
AND T.Assesmentname like '%'+@searchtext+'%'
)
OR
(ColumnName = 'NoOfQuestions'
AND IsSearch = 1 and FieldID = 2
AND T.NoOfQuestions like @searchtext
)
OR
(ColumnName = 'ThresholdScoreToPass'
AND IsSearch = 1
AND T.ThresholdScoreToPass like @searchtext
)
OR
(ColumnName = 'LastModifiedDate'
AND IsSearch = 1
AND T.LastModifiedDate like '%'+@searchtext+'%'
)
)
Passing @searchfield = '1,2', @searchtext = '5'
will search on columns 1(=Assesmentname) and 2 (=NoOfQuestions)
for Assesmentname like '%5%' or NoOfQuestions=5
Could anyone Please get a query better than this keeping performance in view?
Thanks in advance:-)
June 22, 2009 at 2:46 am
Hi,
try this
declare @searchfield nvarchar(100),
@searchtext nvarchar(100)
SET @searchfield = '1,2'
SET @searchtext = '5'
SELECT * FROM @temp
where Assesmentname = (case when @searchfield like '%1%' then @searchtext else null end)
or
NoOfQuestions = (case when @searchfield like '%2%' then @searchtext else null end)
or
ThresholdScoreToPass = (case when @searchfield like '%3%' then @searchtext else null end)
or
LastModifiedDate = (case when @searchfield like '%4%' then @searchtext else null end)
ARUN SAS
June 22, 2009 at 3:54 am
Another approach would could take advantage of appropriate indexing would be:
SELECT*
FROM@temp
WHERE@searchfield LIKE '%1%'
ANDAssesmentname = @searchtext
UNIONALL
SELECT*
FROM@temp
WHERE@searchfield LIKE '%2%'
ANDNoOfQuestions = @searchtext
UNIONALL
SELECT*
FROM@temp
WHERE@searchfield LIKE '%3%'
ANDThresholdScoreToPass = @searchtext
UNIONALL
SELECT*
FROM@temp
WHERE@searchfield LIKE '%4%'
ANDLastModifiedDate = @searchtext
This may look like it would be less efficient, but each UNIONed query will only be executed if the test on @searchfield is true. This is achieved through the use of a start-up filter for each section in the query plan.
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply