Link to home
Start Free TrialLog in
Avatar of casstd
casstd

asked on

SQL Server 2000 Stored procedure Help

Hi,
   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) + '%')
GO

 
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of casstd
casstd

ASKER

Thank you Angel for your help.