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

    Unanswered: Weird Proc Syntax Error

    'Ello there. This proc has been giving me headaches for a week now, hopefully someone here knows whats wrong with it.

    Code:
    CREATE PROC [dbo].[search_querry_results](
    @datefrom 	datetime = NULL,
    @dateto	datetime = NULL,
    @querry_string   varchar(60),
    @slice      	int
    )
    AS
    
    DECLARE @querry nvarchar(2000)
    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'
    
    BEGIN
    		IF ( @datefrom IS NOT NULL ) AND ( @dateto IS NOT NULL ) 
    			EXEC sp_executesql @querry
    END
    GO
    I am running it from Query Analyzer using the following line
    Code:
    EXEC search_querry_results @datefrom = '01/01/1970' , @dateto = '01/01/1980', @querry_string = 'Student' , @slice = '10'
    The error it is giving me is: Line 1: Incorrect syntax near '1'.

    Anyone have any ideas what is going wrong?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to enclose your dates in single-quotes:

    declare @dateto datetime
    declare @querry_string varchar(60)
    declare @slice int

    set @datefrom = '01/01/1970'
    set @dateto = '01/01/1980'
    set @querry_string = 'Student'
    set @slice = '10'

    DECLARE @querry nvarchar(2000)
    --won't work
    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'
    select @querry
    --works fine
    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'
    select @querry
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Weird Proc Syntax Error

    Originally posted by ruhk
    hopefully someone here knows whats wrong with it.

    I'm sorry...but I have to...

    Ahem...

    The developer

    Sorry...be back in a flash with the fix...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Try this

    Code:
    CREATE PROC [dbo].[search_query_results](
    	  @datefrom 		datetime = NULL
    	, @dateto		datetime = NULL
    	, @querry_string	varchar(60)
    	, @slice      		int
    )
    AS
    
    DECLARE @querry 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'
    
    
    BEGIN
    		IF ( @datefrom IS NOT NULL ) AND ( @dateto IS NOT NULL ) 
    			EXEC sp_executesql @querry
    END
    GO
    
    EXEC search_querry_results '01/01/1970', '01/01/1980', 'Student', '10'
    GO
    Last edited by Brett Kaiser; 03-02-04 at 17:54.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2004
    Posts
    8
    Aha, I see what you changed. Works fine now, thanks a bunch.

Posting Permissions

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