how to make a search proc for this tables and their conditions ?

  • 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''

  • 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

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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)

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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.

  • 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

  • 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

  • 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/

  • 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