InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 15:20:41 GMT20http://www.sqlservercentral.com/Forums/Topic1353168-391-1.aspx[quote][b]sivajii (9/3/2012)[/b][hr]...sorry Lynn Pettis if anything hurts u i am sorry [/quote]Not sure where you get the idea that anything hurts. Just trying to make sure you understand that we aren't here to do your work for you but to help you understand what you need to do.Sean is correct, you are going to get table scans with your query. You may want to look at full text indexing, this may give you what you need.Tue, 04 Sep 2012 09:02:47 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1353168-391-1.aspx[quote]the original store proc it which i mentioned there was working but it take more than 10 minutes to execute [/quote]That is because your where clause is not sargable. That means you have table/index scan after scan for every column in your long list.As suggested by Jeff Moden you need to read the article by Gail about catch all queries.The others have been nicer than I am. You have been asked repeatedly in multiple threads on this topic to provide ddl, sample data and desired output. You continue to ignore these requests from the very people who are trying to help you. You will not find much help around here if you don't provide enough details to answer the question. You should try reading this article too. It will help you in understanding the details of what needs to be posted. [url=http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx]http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx[/url]Tue, 04 Sep 2012 08:58:15 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1353168-391-1.aspxhi Lynn Pettis i used the function to split the words which was given in the parameter @values=' the city words'thecity wordsand it has to check the data s related column which i joined [code="sql"] s.BusinessName LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.ContactPerson1 LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.ContactPerson2 LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.AddressLine1 LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.AddressLine2 LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.City LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.CountryCode LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.Landmark LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.State LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.Pincode LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.Locality LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.Mobile LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.Telephone LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.Email LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Items)), ' ', '%' ) + '%' or b.Website LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or ac .CategoryName LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or sb.SubCategoryName LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%'[/code]the original store proc it which i mentioned there was working but it take more than 10 minutes to execute the proceduresorry Lynn Pettis if anything hurts u i am sorry Mon, 03 Sep 2012 23:09:05 GMTsivajiihttp://www.sqlservercentral.com/Forums/Topic1353168-391-1.aspx[quote][b]sivajii (9/3/2012)[/b][hr][code="other"]here i used CROSS APPLY dbo.DelimitedSplit8k(@VALUES,'')ds funtion to split the words 'the cri word' declare @value varchar(800)='the cri word'function name is split it will split values like thisthecriword this words should be checked one by one in the select query [/code][/quote]Your problem above is you passing in the empty string as a delimiter. That just isn't going to work. If the words are delimited by spaces, then pass in a space like this:[code="sql"]CROSS APPLY dbo.DelimitedSplit8k(@VALUES,' ')ds[/code]Mon, 03 Sep 2012 17:48:35 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1353168-391-1.aspxsivag,For your informantion, we are volunteers on this site providing help to others as part of giving back to the SQL Server community.We are willing to help you, but you really have to help us as well by showing us what you are doing and where you are having problems. We do not get paid to help you get paid for the job you are expected to do for your employer.Mon, 03 Sep 2012 17:33:58 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1353168-391-1.aspx[code="other"]here i used CROSS APPLY dbo.DelimitedSplit8k(@VALUES,'')ds funtion to split the words 'the cri word' declare @value varchar(800)='the cri word'function name is split it will split values like thisthecriword this words should be checked one by one in the select query [/code]Mon, 03 Sep 2012 12:33:50 GMTsivajiihttp://www.sqlservercentral.com/Forums/Topic1353168-391-1.aspx[quote][b]sivajii (9/2/2012)[/b][hr]but here is used the cross split function in my original proc which i mentioned i want to check all column it was itwas not present data means that table value will be also stored [code="sql"]CROSS APPLY dbo.DelimitedSplit8k(@VALUES,'')ds WHERE ( s.BusinessName LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' Or ds.Item IS NULL) and ( b.ContactPerson1 LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' Or ds.Item IS NULL) and (b.ContactPerson2 LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' Or ds.Item IS NULL) and ( b.AddressLine1 LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' Or ds.Item IS NULL) [/code][/quote]Since the stored procedure you wrote doesn't actually work against the test data tables you provided and we have no idea what the @Values variable contains, I suggest that you need to provide a bit more information because there's not really enough info to help you with your problem, yet. It also appears that you're trying to use nothing as a delimiter for the DelimitedSplit8K function and that's just not going to work. You MUST use some form of delimiter for the second operand of the function and the elements in @Values must be separated by that delimiter.To wit, ever example you've given for @Values, so far, has been NULL.Also, please stop PMing me about this problem. I'm sorry but I just don't have the time to work as your personal programmer, especially on a holiday weekend. Heh... where do you think everyone else in the U.S. is today? ;-)Mon, 03 Sep 2012 10:09:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353168-391-1.aspxbut here is used the cross split function in my original proc which i mentioned i want to check all column it was itwas not present data means that table value will be also stored [code="sql"]CROSS APPLY dbo.DelimitedSplit8k(@VALUES,'')ds WHERE ( s.BusinessName LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' Or ds.Item IS NULL) and ( b.ContactPerson1 LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' Or ds.Item IS NULL) and (b.ContactPerson2 LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' Or ds.Item IS NULL) and ( b.AddressLine1 LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' Or ds.Item IS NULL) [/code]Sun, 02 Sep 2012 11:31:15 GMTsivajiihttp://www.sqlservercentral.com/Forums/Topic1353168-391-1.aspxThe quick answer to this problem is to NOT use all of the ORs you're using. Gail Shaw wrote a wonderful article about such "Catch All Queries" and the benefits of using Dynamic SQL to do such a thing. Her article can be found at the following URL. Please read it and give that method a try.[url]http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]Sun, 02 Sep 2012 10:50:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1353168-391-1.aspxfor this proconly i have given sample example iam trying this for this proc only[code="sql"]ALTER PROCEDURE [dbo].[SP_Search]( @CategoryId int=NULL, @Location [varchar](200)=NULL, @VALUES varchar(8000)=NULL, @SubcategoryId [int]=NULL)ASBEGINIF LTRIM(RTRIM(@VALUES))=''BEGIN SET @VALUES=NULL;ENDIF LTRIM(RTRIM(@SubcategoryId))=''BEGIN SET @SubcategoryId=NULL;ENDIF @CategoryId=0BEGIN SET @CategoryId=NULL;ENDIF RTRIM(LTRIM(@Location))=''BEGIN SET @Location=NULL;ENDIF EXISTS( SELECT * FROM sys.tables WHERE name LIKE '#solution1%')DROP TABLE #solution1 create table #solution1 ( AdSpaceId int , BusinessName varchar(200)Null , Logo varchar(150)Null , ContactPerson1 varchar(400) Null , AddressLine1 varchar(150)Null , AddressLine2 varchar(150)Null , Landmark varchar(150)Null , City varchar(150)Null , State varchar(150)Null , CountryCode varchar(3)Null , Pincode varchar(10)Null , Locality varchar(150)Null , Telephone varchar(100)Null , Mobile varchar(300) Null , Email varchar(400)Null , Website varchar(150)Null )IF ISNULL(@VALUES,'')!=''BEGIN insert into #solution1( AdSpaceId,BusinessName, Logo,ContactPerson1,AddressLine1,AddressLine2,Landmark,City,State ,CountryCode,Pincode,Locality,Telephone,Mobile,Email,Website) select distinct(s.AdSpaceId) ,s.BusinessName ,s.Logo ,b.ContactPerson1 ,b.AddressLine1 ,b.AddressLine2 ,b.Landmark ,b.City ,b.State ,b.CountryCode ,b.Pincode ,b.Locality ,b.Telephone ,b.Mobile ,b.Email ,b.Website from tblAdSpace s --left outer join -- tblMembers m on m.MemberId=s.MemberId left outer join tblAdContactInfo b ON s.AdSpaceId=b.AdspaceId left outer join tblApplicableCategories ACS ON ACS.AdSpaceId= s.AdSpaceId inner join tblAdCategory ac ON ac.CategoryId =ACS.CategoryId inner join tblAdSubCategory sb ON sb.SubCategoryId = ACS.SubCategoryId CROSS APPLY dbo.DelimitedSplit8k(@VALUES,'')ds WHERE s.BusinessName LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.ContactPerson1 LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.ContactPerson2 LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.AddressLine1 LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.AddressLine2 LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.City LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.CountryCode LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.Landmark LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.State LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.Pincode LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.Locality LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.Mobile LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.Telephone LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or b.Email LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Items)), ' ', '%' ) + '%' or b.Website LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or ac .CategoryName LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' or sb.SubCategoryName LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Item)), ' ', '%' ) + '%' ENDELSEBEGIN insert into #solution1( AdSpaceId,BusinessName, Logo,ContactPerson1,AddressLine1,AddressLine2,Landmark,City,[State] ,CountryCode,Pincode,Locality,Telephone,Mobile,Email,Website) select distinct(s.AdSpaceId) ,s.BusinessName ,s.Logo ,b.ContactPerson1 ,b.AddressLine1 ,b.AddressLine2 ,b.Landmark ,b.City ,b.State ,b.CountryCode ,b.Pincode ,b.Locality ,b.Telephone ,b.Mobile ,b.Email ,b.Website from tblAdSpace s left outer join tblMembers m on m.MemberId=s.MemberId left outer join tblAdContactInfo b ON s.AdSpaceId=b.AdspaceId left outer join tblApplicableCategories ACS ON ACS.AdSpaceId= s.AdSpaceId left outer join tblAdCategory ac ON ac.CategoryId =ACS.CategoryId left outer join tblAdSubCategory sb ON sb.SubCategoryId = ACS.SubCategoryId ORDER BY S.BusinessName ,ContactPerson1 ,AddressLine1 ,AddressLine2 ,Landmark,City,State,CountryCode ,Pincode,Locality,Telephone,Mobile,Email,Website descENDselect distinct(s.AdSpaceId) ,s.BusinessName ,s.Logo ,s.ContactPerson1 --,s.AddressLine1 --,s.AddressLine2 ,STUFF( (SELECT ',' + im1.AddressLine1 +','+ im1.AddressLine2 FROM tblAdContactInfo im1 WHERE im1.AdSpaceId = s.AdSpaceId FOR XML PATH(''),TYPE ).value('.','VARCHAR(MAX)') , 1,1,SPACE(0) ) as 'Address' ,s.Landmark ,s.City ,s.State ,s.CountryCode ,s.Pincode ,s.Locality ,s.Telephone ,s.Mobile ,s.Email ,s.Website ,(select top 1 CategoryId from tblApplicableCategories where AdSpaceId=s.AdSpaceId) CategoryId ,(select top 1 CategoryName from tblAdCategory a left outer join tblApplicableCategories b on a.CategoryId=b.CategoryId where b.AdSpaceId=s.AdSpaceId) as CategoryName from #solution1 s INNER JOIN tblApplicableCategories ACS ON s.AdSpaceId =ACS.AdSpaceId LEFT OUTER JOIN tblAdCategory ac ON ac.CategoryId =ACS.CategoryId LEFT OUTER JOIN tblAdSubCategory sb ON sb.SubCategoryId = ACS.SubCategoryId INNER JOIN tblAdSpace a on a.AdSpaceId =s.AdSpaceId LEFT OUTER JOIN tblAdContactInfo INF ON INF.AdSpaceId=s.AdSpaceId where a.IsApproved=1 AND a.ActiveStatus =1 AND (@Location IS NULL OR s.City=@Location OR s.State=@Location OR s.Locality=@Location) AND (@SubcategoryId IS NULL OR ACS.SubcategoryId=@SubcategoryId) AND (@CategoryId IS NULL OR ACS.CategoryId= @CategoryId) drop table #solution1 END [/code]the execution of this proc will be like thi s types[code="sql"]EXEC [dbo].[SP_Search] @CategoryId =0, @Location = NULL, @VALUES = N'Air King Tours & Travels smabes', @SubcategoryId = NULL EXEC [dbo].[SP_Search] @CategoryId =1, @Location = NULL, @VALUES = NULL,, @SubcategoryId = NULL EXEC [dbo].[SP_Search] @CategoryId =1, @Location = NULL, @VALUES = NULL,, @SubcategoryId =2 EXEC [dbo].[SP_Search] @CategoryId =1, @Location = 'dharkha', @VALUES = NULL, @SubcategoryId = NULL[/code]Sat, 01 Sep 2012 14:09:21 GMTsivajiihttp://www.sqlservercentral.com/Forums/Topic1353168-391-1.aspxhere iam having 5 tables for example i have declare 5 tablesthe @adforum table is used for add a bussiness and adforumid will be foreginkey for @contact table and @adforumapplicablethe @category table and @subcatgory table are static table this id were called in the table @adforumapplicable[code="other"]declare @adforum table( adforumid int,memberid int,adforumname varchar(100),activesstatus bit)insert into @adforum select 1,1,'inform',1 union allselect 2,6,'serg',1 union allselect 3,9,'serinform',1 --union all--select 4,2,4,'inform',1 union all--select 5,3,1,'serddd',1 union all--select 6,3,2,'serinform',1 [/code][code="sql"]declare @contact table([ContactId] [int] IDENTITY(1,1) NOT NULL, [adforumid] [int] NOT NULL, [AddressLine1] [varchar](150) NULL, [AddressLine2] [varchar](150) NULL, [City] [varchar](150) NULL, [Locality] [varchar](150) NULL, [Email] [varchar](400) NULL, [ContactPerson1] [varchar](150) NULL ) insert into @contact select 1,'2NDSTREET NELWAL','SERNT NELWAL','dharka','BRASWAN','ser@gmail.com', 'bala' union all select 2,'4NDSTREET NELWAL','apwarappart','sharja','tringwer','john@yahoomail.com','john' union all select 3,'2NDSTREET NELWAL','serant','afganist','mohali','seswaginr@gmail.com','brasn' [/code][code="sql"]declare @category table( catid int,categoryname varchar(100))insert into @categoryselect 1,'cricket' union allselect 2,'football'declare @subcatgory table( sid int,catid int,subcatgoryname varchar(100))insert into @subcatgoryselect 1,1,'bowling' union allselect 2,1,'batting' union allselect 3,1,'fielding' union allselect 4,2,'foul' union allselect 5,2,'goal'[/code][code="sql"] declare @adforumapplicable table( applicableid int,adforumid int,catid int,sid int)insert into @adforumapplicableselect 1,1,1,2 union allselect 2,2,2,4 union allselect 3,3,2,4 [/code]from this i want to write a search proc by joining this the user may give any kind of keyword these are the parameter and some time the will select 1 parameter or 2 parameter or 3 or 4 depend on that the output must be provided@VALUES paramete is to search text presnt in the tablesEXEC [dbo].[SP_Search] @CategoryId =N'', @VALUES = N'', @LOCATION=N'', @SubcategoryId=N''Sat, 01 Sep 2012 13:53:30 GMTsivajii