Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

how to make a search proc for this tables and their conditions ? Expand / Collapse
Author
Message
Posted Saturday, September 1, 2012 1:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:26 AM
Points: 212, Visits: 1,382
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''
Post #1353168
Posted Saturday, September 1, 2012 2:09 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:26 AM
Points: 212, Visits: 1,382
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,Website desc
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

Post #1353169
Posted Sunday, September 2, 2012 10:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 35,264, Visits: 31,753
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1353246
Posted Sunday, September 2, 2012 11:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:26 AM
Points: 212, Visits: 1,382
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)

Post #1353256
Posted Monday, September 3, 2012 10:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 35,264, Visits: 31,753
sivajii (9/2/2012)
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)



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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1353590
Posted Monday, September 3, 2012 12:33 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:26 AM
Points: 212, Visits: 1,382
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

Post #1353616
Posted Monday, September 3, 2012 5:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 20,703, Visits: 32,344
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1353649
Posted Monday, September 3, 2012 5:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 20,703, Visits: 32,344
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1353651
Posted Monday, September 3, 2012 11:09 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:26 AM
Points: 212, Visits: 1,382
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

Post #1353706
Posted Tuesday, September 4, 2012 8:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 12,999, Visits: 12,415
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1353962
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse