Member Avatar for Mohfeza

please help for MsSQL stored procedure for ASP.net/C#. Inside stored procedure i used 4 different statements in where clause . first one is working but last 3's are not . Statements are given below-


FILENAME LIKE ''%'+ @FILENAMETEXT +'%''
FILENAME LIKE '+ @FILENAMETEXT +'%''
SUBSTRING(FILENAME,1,4)='+@FILENAMETEXT+'
LEFT(FILENAME,4)='+@FILENAMETEXT+'


bunch of code for where clause are given below-

DECLARE @FILENAMETEXT CHAR(4)
DECLARE @CONDITION VARCHAR(2000)
SET @FILENAMETEXT='http'

SET @CONDITION = ' WHERE ('
SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'')
AND FILENAME LIKE ''%'+ @FILENAMETEXT +'%'' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 '

SET @CONDITION = ' WHERE ('
SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'')
AND FILENAME LIKE '+ @FILENAMETEXT +'%'' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 '

SET @CONDITION = ' WHERE ('
SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'')
AND SUBSTRING(FILENAME,1,4)='+@FILENAMETEXT+' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 '


SET @CONDITION = ' WHERE ('
SET @CONDITION = @CONDITION + 'TITLE LIKE ''%' +@SearchText+ '%'' OR DESCRIPTION LIKE ''%'+@SearchText+ '%'' OR TAGS LIKE ''%'+@SearchText+ '%'')
AND LEFT(FILENAME,4)='+@FILENAMETEXT+' AND ISAPPROVED=1 AND ENCODESTATUS=1 AND ISARCHIVED=0 AND ISDELETED=0 '


please reply me as soon as possible

  • 4 Contributors
  • 3 Replies
  • 193 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by varmadba
Member Avatar for tesuji

Well, looking at your short code fragments it seems to be hard to me understanding your problem. I would suggest that you post the really complete (from create procedure... till go)

krs,
tesu

Member Avatar for omrsafetyo

I would also wrap your sql in some code=sql tags. It's hard to see everything - but for instance, it looks like you have some mixed and/or missing quotes

Member Avatar for varmadba

Hey,

i didn't get you but from what i understood

don't u think these 4 statements are not similar

FILENAME LIKE ''%'+ @FILENAMETEXT +'%''
          FILENAME LIKE '+ @FILENAMETEXT +'%''
         SUBSTRING(FILENAME,1,4)='+@FILENAMETEXT+'
         LEFT(FILENAME,4)='+@FILENAMETEXT+'

1st one:

FILENAME LIKE ''%'+ @FILENAMETEXT +'%''

This will return searched words any where in the a row

2nd one :

FILENAME LIKE '+ @FILENAMETEXT +'%''

This will return any rows starting with searched words

3rd one :

SUBSTRING(FILENAME,1,4)='+@FILENAMETEXT+'

This will return any rows if the searched string is in the first 4 char

4th one:

LEFT(FILENAME,4)='+@FILENAMETEXT+'

This will return any rows if the searched string is in the first 4 char

so you have o/p when using the first code

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.