[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL Server 2000 Stored procedure Help

Posted on 2007-08-01
Medium Priority
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 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 200 total points
ID: 19607220
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 + '%'

Author Comment

ID: 19613994
Thank you Angel for your help.


Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

873 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