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

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

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

Hey,

i didn't get you but from what i understood

don't u think these 4 statements are not similar


1st one:
This will return searched words any where in the a row

2nd one :
This will return any rows starting with searched words

3rd one :
This will return any rows if the searched string is in the first 4 char

4th one:
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