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
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
/*
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
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
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
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
sivaganesh12...
Member
227 Points
308 Posts
how to make dynamic sql select query for the select query with condition 3 table with ?
May 25, 2012 06:08 AM|LINK
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
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)
yrb.yogi
Star
14460 Points
2402 Posts
Re: how to make dynamic sql select query for the select query with condition 3 table with ?
May 25, 2012 06:49 AM|LINK
HI, Defined Default values to parameter say NULL & follow below process..
This will give you exact results without using if else condtion..
check below
.Net All About
sivaganesh12...
Member
227 Points
308 Posts
Re: how to make dynamic sql select query for the select query with condition 3 table with ?
May 27, 2012 08:32 AM|LINK
i am not getting the proper output from ur query plz tell some other ways
yrb.yogi
Star
14460 Points
2402 Posts
Re: how to make dynamic sql select query for the select query with condition 3 table with ?
May 28, 2012 05:14 AM|LINK
Can you show us some data & Expected output?
Without knowing this, noone can judge regarding selectquery
.Net All About
sivaganesh12...
Member
227 Points
308 Posts
Re: how to make dynamic sql select query for the select query with condition 3 table with ?
May 28, 2012 06:30 AM|LINK
/* 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 ENDoutput
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
yrb.yogi
Star
14460 Points
2402 Posts
Re: how to make dynamic sql select query for the select query with condition 3 table with ?
May 28, 2012 06:37 AM|LINK
So what is wrong in query, which will not come into output of query?
.Net All About
sivaganesh12...
Member
227 Points
308 Posts
Re: how to make dynamic sql select query for the select query with condition 3 table with ?
May 28, 2012 08:38 AM|LINK
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
yrb.yogi
Star
14460 Points
2402 Posts
Re: how to make dynamic sql select query for the select query with condition 3 table with ?
May 28, 2012 08:42 AM|LINK
Can you point out what is going wrong in output?
.Net All About
sivaganesh12...
Member
227 Points
308 Posts
Re: how to make dynamic sql select query for the select query with condition 3 table with ?
May 28, 2012 08:55 AM|LINK
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''
yrb.yogi
Star
14460 Points
2402 Posts
Re: how to make dynamic sql select query for the select query with condition 3 table with ?
May 28, 2012 09:01 AM|LINK
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.Net All About