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

    Unanswered: Does this stored procedure look ok?

    Hi guys, does this stored procedure look ok?? I parsed it just fine with no problems but when I try to execute it I get no results

    Code:
    CREATE PROCEDURE [dbo].[PatronsLookUp]
    
    @Enter_LastName NVARCHAR(50) = NULL,
    @StartDateIssued DATETIME = NULL,
    @EndDateIssued DATETIME = NULL,
    @Enter_IR# NVARCHAR(50) = NULL,
    @Enter_Upheld CHAR(10) = NULL
    
    AS
    
     
    
       IF patindex( '*', @Enter_LastName ) > 0 
    
          BEGIN
    
             SET @Enter_LastName = replace( @Enter_Lastname, '*', '%' )
    
     
    
             SELECT FirstName, Lastname, IR#, [Date Issued], [Date Served], Hearing, Upheld
    FROM dbo.PatronsDipute_Table
    WHERE ([Date Issued] BETWEEN @StartDateIssued AND @EndDateIssued)
    	AND (LastName = @Enter_Lastname OR @Enter_LastName IS NULL)
    	AND ([IR#] = @Enter_IR# or @Enter_IR# IS NULL)
    	AND ([Upheld] = @Enter_Upheld OR @Enter_Upheld IS NULL)  
    
    
            
          END 
    
       ELSE
    
          BEGIN
    
             SELECT FirstName, Lastname, IR#, [Date Issued], [Date Served], Hearing, Upheld
    FROM dbo.PatronsDipute_Table
    WHERE ([Date Issued] BETWEEN @StartDateIssued AND @EndDateIssued)
    	AND (LastName = @Enter_Lastname OR @Enter_LastName IS NULL)
    	AND ([IR#] = @Enter_IR# or @Enter_IR# IS NULL)
    	AND ([Upheld] = @Enter_Upheld OR @Enter_Upheld IS NULL)  
    
    
          END
    Last edited by desireemm; 01-10-08 at 14:52.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What parameters did you use? NULL date parameters would make this procedure rather crabby.

    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have an IF in your proc which checks to see if the parameter contains any asterisks, and if it does, you replace them with percent signs (the sql server wildcard)

    shouldn't the WHERE clause in the IF block use LIKE, while the WHERE clause in the ELSE block uses equality?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    ok made some improvements, but having problems trying to fit in the wild card

    Code:
    ALTER PROCEDURE [PatronsLookUp]
    @Enter_LastName NVARCHAR(50) = NULL,
    @StartDateIssued DATETIME = NULL,
    @EndDateIssued DATETIME = NULL,
    @Enter_IR# NVARCHAR(50) = NULL,
    @Enter_Upheld CHAR(10) = NULL
    AS
    
    SELECT FirstName, Lastname, IR#, [Date Issued], [Date Served], Hearing, Upheld
    FROM dbo.PatronsDipute_Table
    WHERE ([Date Issued] BETWEEN @StartDateIssued AND @EndDateIssued) OR
    	 (LastName = @Enter_Lastname )OR
    	 ([IR#] = @Enter_IR# ) OR
    	([Upheld] = @Enter_Upheld)

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first guess would be:
    Code:
    --  ptp  20080110  See http://www.dbforums.com/showthread.php?t=1626208
    
    CREATE PROCEDURE [PatronsLookUpPtp]
       @Enter_LastName NVARCHAR(50) = NULL
    ,  @StartDateIssued DATETIME = NULL
    ,  @EndDateIssued DATETIME = NULL
    ,  @Enter_IR# NVARCHAR(50) = NULL
    ,  @Enter_Upheld CHAR(10) = NULL
    AS
    
    IF @startDateIssued IS NULL SET @startDateIssued = '1753-01-01'
    IF @endDateIssued IS NULL SET @endDateIssued = '9999-12-31'
    SET @Enter_LastName = Coalesce(Replace(@Enter_LastName, '*', '%'), '%')
    
    SELECT FirstName, Lastname, IR#
    ,  [Date Issued], [Date Served], Hearing
    ,  Upheld
       FROM dbo.PatronsDipute_Table
       WHERE ([Date Issued] BETWEEN @StartDateIssued AND @EndDateIssued)
          AND (LastName LIKE @Enter_Lastname)
          AND ([IR#] = @Enter_IR# OR @Enter_IR# IS NULL)
          AND ([Upheld] = @Enter_Upheld OR @Enter_Upheld IS NULL)  
    
    RETURN
    GO
    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    thank you Pat that worked perfect

Posting Permissions

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