September 1, 2012 at 1:53 pm
here iam having 5 tables for example i have declare 5 tables
the @adforum table is used for add a bussiness and adforumid will be foreginkey for @contact table and @adforumapplicable
the @category table and @subcatgory table are static table this id were called in the table @adforumapplicable
declare @adforum table
(
adforumid int,memberid int,adforumname varchar(100),activesstatus bit
)
insert into @adforum
select 1,1,'inform',1 union all
select 2,6,'serg',1 union all
select 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
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'
declare @category table
(
catid int,categoryname varchar(100)
)
insert into @category
select 1,'cricket' union all
select 2,'football'
declare @subcatgory table
(
sid int,catid int,subcatgoryname varchar(100)
)
insert into @subcatgory
select 1,1,'bowling' union all
select 2,1,'batting' union all
select 3,1,'fielding' union all
select 4,2,'foul' union all
select 5,2,'goal'
declare @adforumapplicable table
(
applicableid int,adforumid int,catid int,sid int
)
insert into @adforumapplicable
select 1,1,1,2 union all
select 2,2,2,4 union all
select 3,3,2,4
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 tables
EXEC[dbo].[SP_Search]
@CategoryId =N'',
@VALUES = N'',
@LOCATION=N'',
@SubcategoryId=N''
September 1, 2012 at 2:09 pm
for this proconly i have given sample example iam trying this for this proc only
ALTER PROCEDURE [dbo].[SP_Search]
(
@CategoryId int=NULL,
@Location [varchar](200)=NULL,
@VALUES varchar(8000)=NULL,
@SubcategoryId [int]=NULL
)
AS
BEGIN
IF LTRIM(RTRIM(@VALUES))=''
BEGIN
SET @VALUES=NULL;
END
IF LTRIM(RTRIM(@SubcategoryId))=''
BEGIN
SET @SubcategoryId=NULL;
END
IF @CategoryId=0
BEGIN
SET @CategoryId=NULL;
END
IF RTRIM(LTRIM(@Location))=''
BEGIN
SET @Location=NULL;
END
IF 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)), ' ', '%' ) + '%'
END
ELSE
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
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,Websitedesc
END
select
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
the execution of this proc will be like thi s types
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
September 2, 2012 at 10:50 am
The 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.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
--Jeff Moden
Change is inevitable... Change for the better is not.
September 2, 2012 at 11:31 am
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
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)
September 3, 2012 at 10:09 am
sivajii (9/2/2012)
but here is used the cross split function in my original proc which i mentionedi want to check all column it was itwas not present data means that table value will be also stored
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)
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? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 3, 2012 at 12:33 pm
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 this
the
cri
word
this words should be checked one by one in the select query
September 3, 2012 at 5:33 pm
sivag,
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.
September 3, 2012 at 5:48 pm
sivajii (9/3/2012)
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 this
the
cri
word
this words should be checked one by one in the select query
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:
CROSS APPLY dbo.DelimitedSplit8k(@VALUES,' ')ds
September 3, 2012 at 11:09 pm
hi Lynn Pettis
i used the function to split the words which was given in the parameter
@values=' the city words'
the
city
words
and it has to check the data s related column which i joined
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)), ' ', '%' ) + '%'
the original store proc it which i mentioned there was working but it take more than 10 minutes to execute
the procedure
sorry Lynn Pettis if anything hurts u i am sorry
September 4, 2012 at 8:58 am
the original store proc it which i mentioned there was working but it take more than 10 minutes to execute
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. http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
_______________________________________________________________
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/
September 4, 2012 at 9:02 am
sivajii (9/3/2012)
...sorry Lynn Pettis if anything hurts u i am sorry
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.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply