Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    8

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

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

    -ruhk

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    What 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...
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Feb 2004
    Posts
    25

    Red face try using Convert instead Cast!

    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!!!!!!
    Last edited by parmaia; 05-27-04 at 09:20. Reason: Mistake!

  4. #4
    Join Date
    Oct 2003
    Posts
    268
    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>

  5. #5
    Join Date
    Mar 2004
    Posts
    8
    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.

  6. #6
    Join Date
    Mar 2004
    Posts
    8
    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?

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Mar 2004
    Posts
    8
    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.

  9. #9
    Join Date
    Mar 2004
    Posts
    8
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •