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

• • Reply
  

  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

  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)

  • Edited by sivaganesh1234 on May 25, 2012 06:10 AM
  • Edited by sivaganesh1234 on May 25, 2012 06:20 AM
• • Reply
  

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

  • 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

  
  
  

  Yogesh Bhadauriya
  .Net All About
  
• • Reply
  

  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

  • Edited by sivaganesh1234 on May 27, 2012 11:21 AM
• • Reply
  

  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

  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

  Yogesh Bhadauriya
  .Net All About
  
• • Reply
  

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

  • Edited by sivaganesh1234 on May 28, 2012 06:31 AM
• • Reply
  

  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

  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?

  Yogesh Bhadauriya
  .Net All About
  
• • Reply
  

  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

  • Edited by sivaganesh1234 on May 28, 2012 08:47 AM
  • Edited by sivaganesh1234 on May 28, 2012 08:49 AM
• • Reply
  

  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

  sivaganesh1234

  yes

     yogi

  Can you point out what is going wrong in output?

  Yogesh Bhadauriya
  .Net All About
  
• • Reply
  

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

• • Reply
  

  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

  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

  
  
  

  Yogesh Bhadauriya
  .Net All About