Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Red face Unanswered: Error message in SP

    guys can you help me pls, there is something I'm missing and maybe I cant see it because I'm so tired and stressed. Can you tell me why I keep getting this error message please

    Error
    Msg 102, Level 15, State 1, Procedure AdvSrch, Line 20
    Incorrect syntax near ')'.



    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER FUNCTION [dbo].[AdvSrch]
    (@Start_EventDate datetime = NULL,
    @End_EventDate datetime = NULL,
    @Beginning_ContactDate datetime = null,
    @End_ContactDate datetime = null,
    @Enter_ParentID Nvarchar(50) = null)
    
    RETURNS TABLE
    AS
    RETURN (SELECT     People_tbl.[Student First Name], People_tbl.[Student Last Name], Contact_tbls.[Type of Contact], TanfActivity_tbls.[Services Covered], 
                          TanfActivity_tbls.[State Catagory], TanfActivity_tbls.[State Services Covered], TanfActivity_tbls.[Catagory for hours], TanfActivity_tbls.[Earned hours], 
                          People_tbl.[Parent ID], Contact_tbls.[Purpose of Contact], Contact_tbls.[Contact Date], TanfActivity_tbls.EventDate
    FROM         People_tbl INNER JOIN
                          TanfActivity_tbls ON People_tbl.[Parent ID] = TanfActivity_tbls.[Parent ID] INNER JOIN
                          Contact_tbls ON People_tbl.[Parent ID] = Contact_tbls.[Parent ID]
    WHERE        (dbo.Contact_tbls.[Contact Date] >= @Beginning_ContactDate) and
             (dbo.Contact_tbls.[Contact Date] <= @End_ContactDate.[Contact Date])and
                (dbo.TanfActivity_tbls.[EventDate] >= @Start_EventDate)   and                      
                     ( dbo.TanfActivity_tbls.[EventDate] <= @End_EventDate) and
    						(@Enter_ParentID is null or People_tbl.[Parent ID] = @Enter_ParentID)

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    desireemm, This looks suspicious:

    (dbo.Contact_tbls.[Contact Date] <= @End_ContactDate.[Contact Date])

    It should probably be:

    (dbo.Contact_tbls.[Contact Date] <= @End_ContactDate)

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    still getting an error message

    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER FUNCTION [dbo].[AdvSrch]
    (@Start_EventDate datetime = NULL,
    @End_EventDate datetime = NULL,
    @Beginning_ContactDate datetime = null,
    @End_ContactDate datetime = null,
    @Enter_ParentID Nvarchar(50) = null)
    
    RETURNS TABLE
    AS
    RETURN (SELECT     People_tbl.[Student First Name], People_tbl.[Student Last Name], Contact_tbls.[Type of Contact], TanfActivity_tbls.[Services Covered], 
                          TanfActivity_tbls.[State Catagory], TanfActivity_tbls.[State Services Covered], TanfActivity_tbls.[Catagory for hours], TanfActivity_tbls.[Earned hours], 
                          People_tbl.[Parent ID], Contact_tbls.[Purpose of Contact], Contact_tbls.[Contact Date], TanfActivity_tbls.EventDate
    FROM         People_tbl INNER JOIN
                          TanfActivity_tbls ON People_tbl.[Parent ID] = TanfActivity_tbls.[Parent ID] INNER JOIN
                          Contact_tbls ON People_tbl.[Parent ID] = Contact_tbls.[Parent ID]
    WHERE        (dbo.Contact_tbls.[Contact Date] >= @Beginning_ContactDate) and
             (dbo.Contact_tbls.[Contact Date] <= @End_ContactDate) and
    
                (dbo.TanfActivity_tbls.[EventDate] >= @Start_EventDate)   and                      
                     ( dbo.TanfActivity_tbls.[EventDate] <= @End_EventDate) and
    						(@Enter_ParentID is null or People_tbl.[Parent ID] = @Enter_ParentID)

    Msg 102, Level 15, State 1, Procedure AdvSrch, Line 17
    Incorrect syntax near '('.

  4. #4
    Join Date
    Dec 2007
    Posts
    11
    Forgot to close the bracket .... Add one more ')' in the last..

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE FUNCTION [dbo].[AdvSrch]
    (
    @Start_EventDate datetime = NULL,
    @End_EventDate datetime = NULL,
    @Beginning_ContactDate datetime = null,
    @End_ContactDate datetime = null,
    @Enter_ParentID Nvarchar(50) = null
    )
    RETURNS TABLE
    AS
    RETURN
    (
    SELECT
    People_tbl.[Student First Name],
    People_tbl.[Student Last Name],
    Contact_tbls.[Type of Contact],
    TanfActivity_tbls.[Services Covered],
    TanfActivity_tbls.[State Catagory],
    TanfActivity_tbls.[State Services Covered],
    TanfActivity_tbls.[Catagory for hours],
    TanfActivity_tbls.[Earned hours],
    People_tbl.[Parent ID],
    Contact_tbls.[Purpose of Contact],
    Contact_tbls.[Contact Date],
    TanfActivity_tbls.EventDate
    FROM
    People_tbl
    INNER JOIN
    TanfActivity_tbls
    ON People_tbl.[Parent ID] = TanfActivity_tbls.[Parent ID]
    INNER JOIN
    Contact_tbls
    ON People_tbl.[Parent ID] = Contact_tbls.[Parent ID]
    WHERE
    (dbo.Contact_tbls.[Contact Date] >= @Beginning_ContactDate) and
    (dbo.Contact_tbls.[Contact Date] <= @End_ContactDate) and
    (dbo.TanfActivity_tbls.[EventDate] >= @Start_EventDate) and
    ( dbo.TanfActivity_tbls.[EventDate] <= @End_EventDate) and
    (@Enter_ParentID is null or People_tbl.[Parent ID] = @Enter_ParentID)
    )

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by sanoj_av
    Forgot to close the bracket .... Add one more ')' in the last..

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE FUNCTION [dbo].[AdvSrch]
    (
    @Start_EventDate datetime = NULL,
    @End_EventDate datetime = NULL,
    @Beginning_ContactDate datetime = null,
    @End_ContactDate datetime = null,
    @Enter_ParentID Nvarchar(50) = null
    )
    RETURNS TABLE
    AS
    RETURN
    (
    SELECT
    People_tbl.[Student First Name],
    People_tbl.[Student Last Name],
    Contact_tbls.[Type of Contact],
    TanfActivity_tbls.[Services Covered],
    TanfActivity_tbls.[State Catagory],
    TanfActivity_tbls.[State Services Covered],
    TanfActivity_tbls.[Catagory for hours],
    TanfActivity_tbls.[Earned hours],
    People_tbl.[Parent ID],
    Contact_tbls.[Purpose of Contact],
    Contact_tbls.[Contact Date],
    TanfActivity_tbls.EventDate
    FROM
    People_tbl
    INNER JOIN
    TanfActivity_tbls
    ON People_tbl.[Parent ID] = TanfActivity_tbls.[Parent ID]
    INNER JOIN
    Contact_tbls
    ON People_tbl.[Parent ID] = Contact_tbls.[Parent ID]
    WHERE
    (dbo.Contact_tbls.[Contact Date] >= @Beginning_ContactDate) and
    (dbo.Contact_tbls.[Contact Date] <= @End_ContactDate) and
    (dbo.TanfActivity_tbls.[EventDate] >= @Start_EventDate) and
    ( dbo.TanfActivity_tbls.[EventDate] <= @End_EventDate) and
    (@Enter_ParentID is null or People_tbl.[Parent ID] = @Enter_ParentID)
    )
    Nice to see you can actually use inner joins, not here.

Posting Permissions

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