locked
Select TOP 1 in SP not working RRS feed

  • Question

  • I have the following select statement within a stored procedure:

     

    DECLARE @first_date   DATETIME

     

    select @first_date = top 1 (g_collectdate)
    from ESS_Coefficients es, ProgramXRef
    where es.Program_Id  = ProgramXRef.Program_Id
    AND @In_Program  = ProgramXRef.Program_Name
    AND es.Scope  LIKE @In_Scope
    order by g_collectdate asc

     

    The syntax check tells me "Error 156: Incorrect syntax near keyword 'top'".

     

    I took out the @first_date = portion, substituted in values and ran the query from SQL query analyzer and it worked fine.

     

    select top 1 (g_collectdate)
    from ESS_Coefficients es, ProgramXRef
    where es.Program_Id  = ProgramXRef.Program_Id
    AND 'ABC'  = ProgramXRef.Program_Name
    AND es.Scope  LIKE 'abc/%'
    order by g_collectdate asc

     

    What syntax problem is causing the error?

    Thursday, January 31, 2008 3:32 PM

Answers

  • DECLARE @first_date   DATETIME

     

    select top 1 @first_date =  g_collectdate
    from ESS_Coefficients es, ProgramXRef
    where es.Program_Id  = ProgramXRef.Program_Id
    AND @In_Program  = ProgramXRef.Program_Name
    AND es.Scope  LIKE @In_Scope
    order by g_collectdate asc
    Thursday, January 31, 2008 3:44 PM

All replies

  • DECLARE @first_date   DATETIME

     

    select top 1 @first_date =  g_collectdate
    from ESS_Coefficients es, ProgramXRef
    where es.Program_Id  = ProgramXRef.Program_Id
    AND @In_Program  = ProgramXRef.Program_Name
    AND es.Scope  LIKE @In_Scope
    order by g_collectdate asc
    Thursday, January 31, 2008 3:44 PM
  •  

    Thanks!
    Thursday, January 31, 2008 4:04 PM
  • You are welcome sir
    Thursday, January 31, 2008 4:05 PM