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

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)

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

HI, Defined Default values to parameter say NULL & follow below process..

Define Parameters Default Values in SP
Check Condition for NULL OR EMPTY Space on front end application, if condition match, don't pass parameter to call store procedure
Check NULL Condition (Default condtion first to execute select query.

This will give you exact results without using if else condtion..

check below

ALTER PROCEDURE [dbo].[SP_data]
(
	@CategoryId int=NULL,
	@Location [varchar](120)=NULL,
	@VALUES varchar(8000)=NULL,
	@AdType [char](1)=NULL
)
AS

IF ISNULL(@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]
	)
	
	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 (@Location IS NULL OR s.Locality=@Location)
	and (@AdType IS NULL OR s.AdType=@AdType)
	and (@CategoryId IS NULL OR sb.CategoryId= @CategoryId)

	drop table #solution1
END

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

i am not getting the proper output from ur query plz tell some other ways

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

sivaganesh1234

i am not getting the proper output from ur query plz tell some other ways

Can you show us some data & Expected output?

Without knowing this, noone can judge regarding selectquery

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

/*

 EXEC [dbo].[SP_data]
        @CategoryId=N'247',
		@VALUES = N'Agra chennai numgambkm',
		@Location=N'chennai',
		@AdType =N''
		

*/

ALTER PROCEDURE [dbo].[SP_data]
(
        @CategoryId int=NULL,
        @Location [varchar](120)=NULL,
        @VALUES varchar(8000)=NULL,
        @AdType [char](1)=NULL
)
AS

IF ISNULL(@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
		tblAdCategory ac ON ac.CategoryId =sb.CategoryId
		LEFT OUTER  JOIN
        tblClassifiedAdditionalData CAD ON CAD.ClassifiedId =csf.ClassifiedId
		LEFT OUTER  JOIN
         tblSubCategoryAdditionalData  SAD ON SAD.SubCategoryAdditionalDataId=CAD.SubCategoryAdditionalDataId

		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
       inner join tblAdSubCategory sb ON sb.SubCategoryId = s.SubCategoryId
       inner JOIN tblAdCategory ac ON  sb.CategoryId =ac.CategoryId
        ----inner join tblClassified csf on csf.ClassifiedId=t.ClassifiedId
        where (@Location IS NULL OR s.Locality=@Location)
        and (@AdType IS NULL OR s.AdType=@AdType)
        or (@CategoryId IS NULL OR sb.CategoryId= @CategoryId)

        drop table #solution1
END
END

output

ClassifiedId MemberId SubCategoryId Email City Locality AdType AdOwnerType OfferCondition Price Title Description

1 1 185 tsg@gmail.com Chennai Chennai b null null 0.00 tablet should need it

RelatedKeywords MobileNo Weightage CreateDate IsActive IsAuthorized ClassifiedAdditionalDataId1 ClassifiedAdditionalDataId2

Rahman 59069 3 2012-05-14 1 1 1|Music,2|Albums,3|stage NULL

ClassifiedImageId FileName
1 355img1030.png

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

sivaganesh1234

output

ClassifiedId MemberId SubCategoryId Email City Locality AdType AdOwnerType OfferCondition Price Title Description

1 1 185 tsg@gmail.com Chennai Chennai b null null 0.00 tablet should need it

RelatedKeywords MobileNo Weightage CreateDate IsActive IsAuthorized ClassifiedAdditionalDataId1 ClassifiedAdditionalDataId2

Rahman 59069 3 2012-05-14 1 1 1|Music,2|Albums,3|stage NULL

ClassifiedImageId FileName
1 355img1030.png

So what is wrong in query, which will not come into output of query?

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

yes

yogi similarly i just want to find

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

inorder to make shrort the store proc condition i asked for dynamic sql query i tried a sample sql query in my question it self i have mentioned

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

sivaganesh1234

yes

yogi

Can you point out what is going wrong in output?

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

yogi similarly i just want to find

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

inorder to make shrort the store proc condition i asked for dynamic sql query i tried a sample sql query in my question it self i have mentioned

when i execute all parameter means it is showing result

EXEC [dbo].[SP_data]
@CategoryId=N'247',
@VALUES = N'Agra chennai numgambkm',
@Location=N'chennai',
@AdType =N'b'

but when i execute yhis mean iam not getting output

EXEC [dbo].[SP_data]
@CategoryId=N'247',
@VALUES = N'Agra chennai numgambkm',
@Location=N'',
@AdType =N''

similarly for this also

EXEC [dbo].[SP_data]
@CategoryId=N'247',
@VALUES = N'',
@Location=N'',
@AdType =N''

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

sivaganesh1234

similarly for this also

EXEC [dbo].[SP_data]
@CategoryId=N'247',
@VALUES = N'',
@Location=N'',
@AdType =N''

In order to work query with respect to blank parameter value, you must have to change following query, i have made little change

& run, than post if you do not get exact output

/*

 EXEC [dbo].[SP_data]
        @CategoryId=N'247',
                @VALUES = N'Agra chennai numgambkm',
                @Location=N'chennai',
                @AdType =N''
                

*/

ALTER PROCEDURE [dbo].[SP_data]
(
        @CategoryId int=NULL,
        @Location [varchar](120)=NULL,
        @VALUES varchar(8000)=NULL,
        @AdType [char](1)=NULL
)
AS

IF LTRIM(RTRIM(@AdType))=''
BEGIN
	SET @AdType=NULL;
END

IF @CategoryId=0
BEGIN
	SET @CategoryId=NULL;
END

IF RTRIM(LTRIM(@Location))=''
BEGIN
	SET @Location=NULL;
END

IF ISNULL(@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
                tblAdCategory ac ON ac.CategoryId =sb.CategoryId
                LEFT OUTER  JOIN
        tblClassifiedAdditionalData CAD ON CAD.ClassifiedId =csf.ClassifiedId
                LEFT OUTER  JOIN
         tblSubCategoryAdditionalData  SAD ON SAD.SubCategoryAdditionalDataId=CAD.SubCategoryAdditionalDataId

                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
       inner join tblAdSubCategory sb ON sb.SubCategoryId = s.SubCategoryId
       inner JOIN tblAdCategory ac ON  sb.CategoryId =ac.CategoryId
        ----inner join tblClassified csf on csf.ClassifiedId=t.ClassifiedId
        where (@Location IS NULL OR s.Locality=@Location)
        and (@AdType IS NULL OR s.AdType=@AdType)
        or (@CategoryId IS NULL OR sb.CategoryId= @CategoryId)

        drop table #solution1
END
END

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

if iam giving only one parameter value means the output was not coming out

for example

if iam giving only

EXEC [dbo].[SP_data]
@CategoryId=N'247',
@VALUES = N'',
@Location=N'',
@AdType =N''

and if iam giving only

EXEC [dbo].[SP_data]
@CategoryId=N'',
@VALUES = N'',
@Location=N'',
@AdType =N'b'

if iam giving only

EXEC [dbo].[SP_data]
@CategoryId=N'',
@VALUES = N'',
@Location=N'agra',
@AdType =N''

i am not finding the output

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

sivaganesh1234

if iam giving only one parameter value means the output was not coming out

for example

if iam giving only

EXEC [dbo].[SP_data]
@CategoryId=N'247',
@VALUES = N'',
@Location=N'',
@AdType =N''

and if iam giving only

EXEC [dbo].[SP_data]
@CategoryId=N'',
@VALUES = N'',
@Location=N'',
@AdType =N'b'

if iam giving only

EXEC [dbo].[SP_data]
@CategoryId=N'',
@VALUES = N'',
@Location=N'agra',
@AdType =N''

i am not finding the output

Now you must have to use this query which will work in all environment..

ALTER PROCEDURE [dbo].[SP_data]
(
        @CategoryId int=NULL,
        @Location [varchar](120)=NULL,
        @VALUES varchar(8000)=NULL,
        @AdType [char](1)=NULL
)
AS

IF LTRIM(RTRIM(@VALUES))=''
BEGIN
     SET @VALUES=NULL;
END

IF LTRIM(RTRIM(@AdType))=''
BEGIN
     SET @AdType=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
(
        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

IF ISNULL(@VALUES,'')!=''
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 tblAdCategory ac ON ac.CategoryId =sb.CategoryId
	LEFT OUTER  JOIN tblClassifiedAdditionalData CAD ON CAD.ClassifiedId =csf.ClassifiedId
	LEFT OUTER  JOIN tblSubCategoryAdditionalData  SAD ON SAD.SubCategoryAdditionalDataId=CAD.SubCategoryAdditionalDataId
	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)), ' ', '%' ) + '%'
END
ELSE
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 tblAdCategory ac ON ac.CategoryId =sb.CategoryId
	LEFT OUTER  JOIN tblClassifiedAdditionalData CAD ON CAD.ClassifiedId =csf.ClassifiedId
	LEFT OUTER  JOIN tblSubCategoryAdditionalData  SAD ON SAD.SubCategoryAdditionalDataId=CAD.SubCategoryAdditionalDataId
	LEFT OUTER  JOIN tblClassifiedImage CFI ON csf.ClassifiedId = CFI.ClassifiedId
END

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
inner join tblAdSubCategory sb ON sb.SubCategoryId = s.SubCategoryId
inner JOIN tblAdCategory ac ON  sb.CategoryId =ac.CategoryId
----inner join tblClassified csf on csf.ClassifiedId=t.ClassifiedId
where (@Location IS NULL OR s.Locality=@Location)
and (@AdType IS NULL OR s.AdType=@AdType)
or (@CategoryId IS NULL OR sb.CategoryId= @CategoryId)

drop table #solution1