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

    Unanswered: More then one option incorporated into SP

    Hello all

    I would like to add a couple more fields that the users can query by, more options how would I add in the same stored proecedure, First Name, Date, and IR#?? so they can have the ability to run it by one field or two fields or all the above?? how would I incorporate those in the stored procedure??



    Code:
    CREATE PROCEDURE [SearchByDateExcls]
    (@StartDate datetime,
    @EndDate datetime)
    AS SELECT      [IR Number], Date, Inspector, Violation, [Violation Type], Loss, [Loss Type]
    FROM          dbo.Revised_MainTable
    GROUP BY [IR Number], Date, Inspector, Violation, [Violation Type], Loss, [Loss Type]
    HAVING       (Date BETWEEN @StartDate AND @EndDate)






    is this correct
    Code:
    CREATE PROCEDURE [SearchByDateExcls]
    (@StartDateServed datetime,
    @EndDateServed datetime, @Enter_LastName nvarchar(25), @Enter_Duration nvarchar(10))
    AS SELECT      IR#, [Date Served], [Reason For Exclusion], Duration, [First Name], [Last Name]
    FROM          [dbo].[Extended Exclusions]
    GROUP BY  IR#, [Date Served], [Reason For Exclusion] , Duration, [First Name], [Last Name]
    HAVING       (Date BETWEEN @StartDateServed AND @EndDateServed, Enter_LastName, @Enter_Duration)
    Last edited by desireemm; 12-12-07 at 19:16.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    of course it does not work. it does not parse.
    Code:
    Msg 102, Level 15, State 1, Procedure SearchByDateExcls, Line 8
    Incorrect syntax near ','.
    mine parses.
    Code:
    CREATE PROCEDURE [SearchByDateExcls]
    @StartDateServed datetime = null,
    @EndDateServed datetime = null,
    @Enter_LastName nvarchar(25) = null, 
    @Enter_Duration nvarchar(10)  = null
    AS 
    SELECT      IR#, [Date Served], [Reason For Exclusion], Duration, [First Name], [Last Name]
    FROM          [dbo].[Extended Exclusions]
    WHERE  (@StartDateServed is null or Date >= @StartDateServed) 
    AND (@EndDateServed is null or Date <= @EndDateServed)
    AND (@Enter_LastName is null or Enter_LastName = @Enter_LastName) 
    AND (@Enter_Duration is null or Enter_Duration = @Enter_Duration)
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by Thrasymachus
    of course it does not work. it does not parse.
    Code:
    Msg 102, Level 15, State 1, Procedure SearchByDateExcls, Line 8
    Incorrect syntax near ','.
    mine parses.
    Code:
    CREATE PROCEDURE [SearchByDateExcls]
    @StartDateServed datetime = null,
    @EndDateServed datetime = null,
    @Enter_LastName nvarchar(25) = null, 
    @Enter_Duration nvarchar(10)  = null
    AS 
    SELECT      IR#, [Date Served], [Reason For Exclusion], Duration, [First Name], [Last Name]
    FROM          [dbo].[Extended Exclusions]
    WHERE  (@StartDateServed is null or Date >= @StartDateServed) 
    AND (@EndDateServed is null or Date <= @EndDateServed)
    AND (@Enter_LastName is null or Enter_LastName = @Enter_LastName) 
    AND (@Enter_Duration is null or Enter_Duration = @Enter_Duration)


    then what I should have said was what am I doing wrong, NOT IS THIS CORRECT

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Ok I got it and it works

    Woohooo I got it


    Code:
    USE [TestExclusion]
    GO
    /****** Object:  StoredProcedure [dbo].[SearchByDateLstNmDuration]    Script Date: 12/13/2007 08:35:17 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[SearchByDateLstNmDuration]
    @StartDateserved datetime = null,
    @EndDateserved datetime = null,
    @Enter_LastName nvarchar(25) = null, 
    @Enter_Duration nvarchar(10)  = null
    AS 
    SELECT      IR#, [Date served], [Reason for Exclusion], Duration, [First Name], [Last Name]
    FROM          [dbo].[Extended Exclusions]
    WHERE  (@StartDateserved is null or [Date served] >= @StartDateserved) 
    AND (@EndDateserved is null or [Date served] <= @EndDateserved)
    AND (@Enter_LastName is null or [Last Name] = @Enter_LastName) 
    AND (@Enter_Duration is null or Duration = @Enter_Duration)

Posting Permissions

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