SQL Server 2000 Stored procedure Help

Posted on 2007-08-01
Last Modified: 2010-03-20
   I have SQL 2000. I have a page with search criteria. I have wrriten the code to retrieve data.
Initially the parameters will have value '' varchar and nothing fro int parameter.
When the condition is selected and the search button is clicked i will pass the parameter to retrieve data.
but my query didn't retrieve any data.
Where there is no entry in my textbox for establishmentname, customercode the value will be passed as ' '.

Could anyone help me? What am I doing wrong?

CREATE PROCEDURE Select_CertificatesToPrint (@EstablishmentName varchar(500), @CustomerCode varchar(15), @ApprovedDateFrom datetime, @ApprovedDateTo datetime, @SchemeID int) AS

SELECT     TOP 100 PERCENT dbo.Ticket.TicketID, dbo.Ticket.TicketNo, dbo.Ticket_Certifications.CertNo, dbo.Ticket_Certifications.NoOfPrints,
                      dbo.Cust_Company.EstablishmentName, dbo.Cust_Company.CompanyID, dbo.Ticket.SchemeID, dbo.Master_Scheme.SchemeCode,
                      dbo.Ticket.DateApproved, dbo.[User].LoginID AS CustomerCode
FROM         dbo.Ticket INNER JOIN
                      dbo.Cust_Company ON dbo.Ticket.CompanyID = dbo.Cust_Company.CompanyID INNER JOIN
                      dbo.Ticket_Certifications ON dbo.Ticket.TicketID = dbo.Ticket_Certifications.TicketID INNER JOIN
                      dbo.Master_Scheme ON dbo.Ticket.SchemeID = dbo.Master_Scheme.SchemeID INNER JOIN
                      dbo.[User] ON dbo.Cust_Company.CompanyID = dbo.[User].CompanyID
WHERE     (dbo.Cust_Company.EstablishmentName <> 'MUIS') AND (dbo.Ticket.DateApproved IS NOT NULL)  

AND (dbo.Ticket.DateApproved >= ISNULL(CONVERT(DATETIME, @DateApprovedFrom, 103), CONVERT(DATETIME, dbo.Ticket.DateApproved, 103))) AND
    (dbo.Ticket.DateApproved <= ISNULL(@DateApprovedTo, dbo.Ticket.DateApproved))  
   (dbo.Ticket.SchemeID = ISNULL(@SchemeID,  dbo.Master_Scheme.SchemeID)) AND
   AND (dbo.Cust_Company.EstablishmentName LIKE ISNULL(@EstablishmentName, dbo.Cust_Company.EstablishmentName) + '%')
(dbo.Cust_Company.EstablishmentName LIKE ISNULL(@EstablishmentName, dbo.Cust_Company.EstablishmentName) + '%') AND
(dbo.[User].LoginID LIKE ISNULL(@CustomerCode, dbo.[User].LoginID) + '%')

Question by:casstd
    LVL 142

    Accepted Solution

    the problem is the ' '  is not null...

    >(dbo.[User].LoginID LIKE ISNULL(@CustomerCode, dbo.[User].LoginID) + '%')
    should be:
    (dbo.[User].LoginID LIKE CASE
          WHEN @CustomerCode IS NULL
          THEN dbo.[User].LoginID
          WHEN LTRIM(@CustomerCode) = ''
          THEN dbo.[User].LoginID
          ELSE @CustomerCode
          END + '%'
    LVL 6

    Author Comment

    Thank you Angel for your help.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now