Complex PROC question

Complex to me, anyways. I posted this quite a bit ago, although the question was different, and you guys pointed out what was wrong, which saved me lots of headaches and coffee, so hopefully you guys can point out what is wrong this time.

This PROC is used in a search engine type script I wrote that searches through a database of magazine articles. You can search with just a name or description, or a range of dates. You can also search for all articles posted after a date, or before a date. On the perl side, I pass it 4 parameters, $querry_string, the description or name, $datefrom and $dateto, which specifies a range of dates, and $slice, which is which slice of results to return, like 1-10.

The script *mostly* works, it works correctly with two dates or no dates are specified, however it will not work with only one date filled in. ( See querry2 in the proc ) Any ideas what is wrong? When I run the line in Querry Analyzer it works, but in the script, it finds 0 results no matter what.

CREATE PROC [dbo].[search_querry_results](
@.datefrom datetime = NULL,
@.dateto datetime = NULL,
@.querry_string varchar(60),
@.slice int
)
AS

DECLARE @.querry nvarchar(2000)
DECLARE @.querry2 nvarchar(2000)
DECLARE @.querry3 nvarchar(2000)

-- Care of blind dude

SET @.querry = 'SELECT TOP ' + CAST(@.slice AS varchar(100))
+ ' * FROM FREETEXTTABLE( Exponent, *, ''' + @.querry_string
+ ''' ) as ct JOIN Exponent AS e ON ct.[KEY] = e.[Key] WHERE date > '''
+ CAST( @.datefrom AS varchar(30)) + ''' AND date < '''
+ CAST(@.dateto AS varchar(30)) + ''' ORDER BY Rank DESC'

SET @.querry2 = 'SELECT TOP ' + CAST(@.slice AS varchar(100))
+ ' * FROM FREETEXTTABLE( Exponent, *, ''' + @.querry_string
+ ''' ) as ct JOIN Exponent AS e ON ct.[KEY] = e.[Key] WHERE date < '''
+ CAST(@.dateto AS varchar(30)) + ''' ORDER BY Rank DESC'

SET @.querry3 = 'SELECT TOP ' + CAST(@.slice AS varchar(100))
+ ' * FROM FREETEXTTABLE( Exponent, *, ''' + @.querry_string
+ ''' ) as ct JOIN Exponent AS e ON ct.[KEY] = e.[Key] ORDER BY Rank DESC'

BEGIN
IF ( @.datefrom IS NOT NULL ) AND ( @.dateto IS NOT NULL )
EXEC sp_executesql @.querry

ELSE


IF ( @.dateto IS NOT NULL ) AND ( @.datefrom IS NULL)
EXEC sp_executesql @.querry2


ELSE


IF ( @.datefrom IS NULL ) AND (@.dateto IS NULL )
EXEC sp_executesql @.querry3
END
GO

There is the code. Let me know if you can come up with something, im all out of ideas. Thanks

-ruhkWhat is the error you're getting?

If you CAST a NULL to varchar, you get NULL. So I'm not sure if that's where you're problem is occurring or not...|||Hi!, the "date" field in the table is (VarChar or NVarchar) or DateTime?
if it's Datetime, i think the better way is comparing it as datetime. You don't have to convert anything because @.DateTo and @.DateFrom are already DateTime, so compare it directly.

Sorry for my bad english, i'm a programmer, not a biligue, jejeje. I hope this will be usefull, i spend much time figting with date comparisons and that's the way i found it works.
Suerte!!!!!!|||Try the following.

I changed:
- Compare actual dates rather than string version of dates
- Cleaned up the conditions of datefrom/dateto being NULL. The code below is much easier to understand/maintain.
- Avoided escaping issues by leaving @.querry_string as a variable reference in the dynamic SQL rather than dynamically adding the actual value to the dynamic SQL.

<code>
CREATE PROC [dbo].[search_querry_results](
@.datefrom datetime = NULL,
@.dateto datetime = NULL,
@.querry_string varchar(60),
@.slice int
)
AS

DECLARE @.query varchar(2000)

SET @.query = 'SELECT TOP ' + CAST(@.slice AS varchar(100))
+ ' * FROM FREETEXTTABLE( Exponent, *, @.querry_string ) AS ct'
+ ' JOIN Exponent AS e ON ct.[KEY] = e.[Key]'

IF ( @.datefrom IS NOT NULL ) AND ( @.dateto IS NOT NULL )
SET @.query = @.query + ' WHERE Date BETWEEN @.datefrom AND @.dateto'
ELSE IF ( @.datefrom IS NOT NULL )
SET @.query = @.query + ' WHERE Date > @.datefrom'
ELSE IF ( @.dateto IS NOT NULL )
SET @.query = @.query + ' WHERE Date < @.dateto'

SET @.query = @.query + ' ORDER BY Rank DESC'

EXEC sp_executesql @.query
</code>|||Roger - Tried using your code and it gives me a Procedure expects parameter '@.statement' of type 'ntext/nchar/nvarchar'. (SQL-37000)(DBD: st_execute/SQLExecute err=-1) error. Going to try to figure out what that means.|||Ah, I changed the declare line to DECLARE @.query nvarchar(2000), and that got rid of one problem. However now it says I must declare @.querry_string, and if I add in ''' + @.querry_string + ''' it gives me errors that I must declare my other variables. Anyone know how to fix this?|||Comment out your EXEC statements temporarily. Then add code to display your SQL strings:

select @.querry1
select @.querry2
select @.querry3

Then try running each string through Query Analyzer to help debug your dynamic code.|||They all works perfectly in querry analzyer but when I run my origonal code I posted in the script, it returns 0 hits.

I think its a problem with the datetime. For some reason WHERE Date < @.Dateto does not work.|||I fixed it! Thank you RogerWilco and others, your code worked great. The problem was actually in another stored procedure that counted just the hits.