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

    Talking Unanswered: A Stored Procedure with more to choose from

    Hello all I am trying to create a stored procedure that will either give the user a chance to use the wild cards or the IR_Number, or subjLastName (from the RevisedSubjects_Table) or a begin Date and an End Date.

    Code:
    CREATE PROCEDURE [dbo].[AdvSrchWildCardsIR]
    (@Enter_IR_Number nvarchar(100))
    
    AS
    SET @Enter_IR_Number = REPLACE(@Enter_IR_Number, '*', '%')
    SELECT      dbo.RevisedSubjects_Table.SubjFirstName, dbo.RevisedSubjects_Table.SubjLastName, dbo.Revised_MainTable.[Violation Type], 
                            dbo.RevisedSubjects_Table.[IR Number], dbo.Revised_MainTable.[I/RDocument], dbo.Revised_MainTable.Date, dbo.Revised_MainTable.Action
    FROM          dbo.Revised_MainTable INNER JOIN
                            dbo.RevisedSubjects_Table ON dbo.Revised_MainTable.[IR Number] = dbo.RevisedSubjects_Table.[IR Number]
    
     
    
       IF patindex( '*', @Enter_IR_Number) > 0 
    
          BEGIN
    
             SET @Enter_IR_Number = replace( @Enter_IR_Number, '*', '%' )
    
     
    
            SELECT      dbo.RevisedSubjects_Table.SubjFirstName, dbo.RevisedSubjects_Table.SubjLastName, dbo.Revised_MainTable.[Violation Type], 
                            dbo.RevisedSubjects_Table.[IR Number], dbo.Revised_MainTable.[I/RDocument], dbo.Revised_MainTable.Date, dbo.Revised_MainTable.Action
    
            FROM          dbo.Revised_MainTable INNER JOIN
                            dbo.RevisedSubjects_Table ON dbo.Revised_MainTable.[IR Number] = dbo.RevisedSubjects_Table.[IR Number]
    
    
          END

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you seem to be missing a WHERE clause

    my advice: do not write the stored proc until the query itself is working correctly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

    Wow

    Quote Originally Posted by r937
    you seem to be missing a WHERE clause

    my advice: do not write the stored proc until the query itself is working correctly

    WOW IT EXECUTED and everything just fine as it is. What a trip, thank you didnt even notice that

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

    Ok

    Heres what I have but I'm struggling on incorporating wild card options



    Code:
    CREATE PROCEDURE [dbo].[SearchByIR_Date]
    @StartDate datetime = null,
    @EndDate datetime = null,
    @Enter_IR_Number nvarchar(100)  = null
    AS 
    SELECT      dbo.RevisedSubjects_Table.SubjFirstName, dbo.RevisedSubjects_Table.SubjLastName, dbo.Revised_MainTable.[Violation Type], 
                            dbo.RevisedSubjects_Table.[IR Number], dbo.Revised_MainTable.[I/RDocument], dbo.Revised_MainTable.Date, dbo.Revised_MainTable.Action
    FROM          dbo.Revised_MainTable INNER JOIN
                            dbo.RevisedSubjects_Table ON dbo.Revised_MainTable.[IR Number] = dbo.RevisedSubjects_Table.[IR Number]
    
    WHERE  (@StartDate is null or Date >= @StartDate) 
    AND (@EndDate is null or Date <= @EndDate)
    AND (@Enter_IR_Number is null or dbo.RevisedSubjects_Table.[IR Number] = @Enter_IR_Number)

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what kind of struggle? i don't see any wildcards

    also, i would like to suggest once again that you first get the query working, with wildcards, before you try to parameterize it inside a stored proc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by r937
    what kind of struggle? i don't see any wildcards

    also, i would like to suggest once again that you first get the query working, with wildcards, before you try to parameterize it inside a stored proc

    ok here's what I got

    Code:
    DECLARE @IR# NVARCHAR(10)
    DECLARE @Duration NVARCHAR(10)
    
    SELECT [Last Name], [First Name], [Issue Date], IR#, [Date Served], Duration, [Reason for Exclusion]
      FROM [dbo].[Extended Exclusions]
    WHERE (IR# = @IR#) AND (Duration LIKE '%' + @Duration + '%')
    Last edited by desireemm; 12-18-07 at 16:35.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    congratulations
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

    Talking Woohoo

    Quote Originally Posted by r937
    congratulations

    did I pass teacher did I pass!!!!!!!!!!!!!!!!!!!!!!!!!!!!


    thank you thank you

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're welcome
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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