how to make dynamic sql select query for the select query with condition 3 table with ?

Question

how to make dynamic sql select query for the select query with condition 3 table with ?

Sivaganesh Tamilvendhan

Hall of Fame

Points: 3498
More actions
May 25, 2012 at 12:19 am

#256590

here i used 3 tables in that 3 table i want to search a word whether it presented in the columns of the first table and similarly i just want to search conditions so i used cross split function also and another scalar function also included
ALTER PROCEDURE [dbo].[SP_data]
@CategoryId int,
@Location [varchar](120),
@VALUES varchar(8000),
@AdType [char](1)
AS
BEGIN
IF (@CategoryId<>'' and @AdType<>'' and @Location<>''and @VALUES<>'')
BEGIN
IF EXISTS (
SELECT *
FROM sys.tables
WHERE name LIKE '#solution1%')
DROP TABLE #solution1
create table #solution1
(
ClassifiedId [int],
MemberId [int],
SubCategoryId [int],
Email [varchar](120),
City [varchar](120),
Locality [varchar](120),
AdType [char](1),
AdOwnerType [char](1),
OfferCondition [char](1),
Price [decimal](18, 2),
Title [varchar](100) ,
[Description] [varchar](1000),
RelatedKeywords [varchar](150) ,
MobileNo [varchar](20),
Weightage [smallint],
CreateDate [datetime],
IsActive [int],
IsAuthorized [int],
ClassifiedAdditionalDataId1 [varchar](max),
ClassifiedAdditionalDataId2 [varchar](max),
ClassifiedImageId [int],
[FileName] [varchar](100),
DATALEVEL [int]
)
BEGIN
insert into #solution1(
ClassifiedId
,MemberId
,SubCategoryId
,Email
,City
,Locality
,AdType
,AdOwnerType
,OfferCondition
,Price
,Title
,[Description]
,RelatedKeywords
,MobileNo
,Weightage
,CreateDate
,IsActive
,IsAuthorized
,ClassifiedAdditionalDataId1
,ClassifiedAdditionalDataId2
,ClassifiedImageId
,[FILENAME]
)
SELECT
DISTINCT(csf.ClassifiedId),
--csf.ClassifiedId,
csf.MemberId,
csf.SubCategoryId,
csf.Email,
csf.City,
csf.Locality,
csf.AdType,
csf.AdOwnerType,
csf.OfferCondition,
csf.Price,
csf.Title,
csf.[Description],
csf.RelatedKeywords,
csf.MobileNo,
csf.Weightage,
csf.CreateDate,
csf.IsActive,
csf.IsAuthorized,
(select dbo.FN_GetDataLevel1Value(csf.ClassifiedId))as'ClassifiedAdditionalData1',
(select dbo.FN_GetDataLevel2Value(csf.ClassifiedId))as'ClassifiedAdditionalData2',
(select TOP 1 ClassifiedImageId from tblClassifiedImage where ClassifiedId=CFI.ClassifiedId)as'ClassifiedImageId',
(select TOP 1 [FILENAME] from tblClassifiedImage where ClassifiedId=CFI.ClassifiedId) as'[FILENAME]'
FROM
tblClassified csf
LEFT OUTER JOIN
tblAdSubCategory sb ON sb.SubCategoryId = csf.SubCategoryId
LEFT OUTER JOIN
tblClassifiedImage CFI ON csf.ClassifiedId = CFI.ClassifiedId
cross apply dbo.characterSplit(@values, '') ds --Replace ',' with your actual delimter
where
csf.Email LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Items)), ' ', '%' ) + '%'
or
csf.City LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Items)), ' ', '%' ) + '%'
or
csf.Locality LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Items)), ' ', '%' ) + '%'
--or
--csf.AdType LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Items)), ' ', '%' ) + '%'
or
csf.AdOwnerType LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Items)), ' ', '%' ) + '%'
or
csf.OfferCondition LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Items)), ' ', '%' ) + '%'
or
csf.Price LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Items)), ' ', '%' ) + '%'
or
csf.Title LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Items)), ' ', '%' ) + '%'
or
csf.Description LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Items)), ' ', '%' ) + '%'
or
csf.RelatedKeywords LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Items)), ' ', '%' ) + '%'
or
csf.MobileNo LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Items)), ' ', '%' ) + '%'
or
csf.Weightage LIKE '%'+ REPLACE(RTRIM(LTRIM(ds.Items)), ' ', '%' ) + '%'
select s.ClassifiedId
,s.MemberId
,s.SubCategoryId
,s.Email
,s.City
,s.Locality
,s.AdType
,s.AdOwnerType
,s.OfferCondition
,s.Price
,s.Title
,s.Description
,s.RelatedKeywords
,s.MobileNo
,s.Weightage
,s.CreateDate
,s.IsActive
,s.IsAuthorized
,s.ClassifiedAdditionalDataId1
,s.ClassifiedAdditionalDataId2
,s.ClassifiedImageId
,s.FileName
from
#solution1 s
LEFT OUTER JOIN
tblAdSubCategory sb ON sb.SubCategoryId = s.SubCategoryId
LEFT OUTER JOIN
tblAdCategory ac ON ac.CategoryId =sb.CategoryId
--inner join
-- tblClassified csf
-- on csf.ClassifiedId=t.ClassifiedId
where
s.Locality=@Location
and
s.AdType=@AdType
and
sb.CategoryId= @CategoryId
drop table #solution1
end
end
similarly i want to check 15 condition in else if state ment
Else if (@CategoryId<>'' and @AdType<>'' and @VALUES<>'')
Else if (@CategoryId<>'' and @VALUES<>'')
Else if (@CategoryId<>'' and @AdType<>'' )
Else if (@Location<>''and @VALUES<>'')
in the same proc
i just tried a sample
DECLARE @SQL as varchar(max)
SET @SQL =
DECLARE @SQL as varchar(max)
SET @SQL ='SELECT
DISTINCT(csf.ClassifiedId),
csf.ClassifiedId,
FROM
tblClassified csf
LEFT OUTER JOIN
tblClassifiedImage CFI ON csf.ClassifiedId = CFI.ClassifiedId'
IF @AdSpaceInfo is not null
BEGIN
SET @SQL = @SQL + ' where csf.ClassifiedId = ' + CAST(@ClassifiedId as varchar(5))
END
PRINT @SQL
EXECUTE (@SQL)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gullimeel Hall of Fame Points: 3871 More actions · Answer 1

LEFT OUTER JOIN
tblAdCategory ac ON ac.CategoryId =sb.CategoryId
--inner join
-- tblClassified csf
-- on csf.ClassifiedId=t.ClassifiedId
where
s.Locality=@Location
and
s.AdType=@AdType
and
sb.CategoryId= @CategoryId

Why are you not using this condition in the select statement which is inserting the data into the #solution1 table. If the data is selective it will save lots of work and the #solution table should have minimu data.

GulliMeel

Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 2

the temp table check is not needed since the table is scoped to the session.

in the where condition I would try the following

where 1 = Case when condition1 like 'sometext' then 1

when consition2 like 'someothertext' then 1

else 0

end

Jayanth Kurup[/url]

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 3

i used a cross apply function before where condition in the select query

after cross apply function

and i want to check whether the text was presented in which column of a table thats wyhy i used are condition

Dung Dinh SSCrazy Points: 2513 More actions · Answer 4

To improve performance, you should limit data while inserting into temp table. It means that we only insert

ClassifiedId

,MemberId

,SubCategoryId

which match our searching condition

Then get info which we want by joining temp table to other tables