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.Establish mentName, dbo.Cust_Company.CompanyID , dbo.Ticket.SchemeID, dbo.Master_Scheme.SchemeCo de,
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.Establis hmentName <> '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.Establis hmentName LIKE ISNULL(@EstablishmentName, dbo.Cust_Company.Establish mentName) + '%')
(dbo.Cust_Company.Establis hmentName LIKE ISNULL(@EstablishmentName, dbo.Cust_Company.Establish mentName) + '%') AND
(dbo.[User].LoginID LIKE ISNULL(@CustomerCode, dbo.[User].LoginID) + '%')
GO
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
SELECT TOP 100 PERCENT dbo.Ticket.TicketID, dbo.Ticket.TicketNo, dbo.Ticket_Certifications.
dbo.Cust_Company.Establish
dbo.Ticket.DateApproved, dbo.[User].LoginID AS CustomerCode
FROM dbo.Ticket INNER JOIN
dbo.Cust_Company ON dbo.Ticket.CompanyID = dbo.Cust_Company.CompanyID
dbo.Ticket_Certifications ON dbo.Ticket.TicketID = dbo.Ticket_Certifications.
dbo.Master_Scheme ON dbo.Ticket.SchemeID = dbo.Master_Scheme.SchemeID
dbo.[User] ON dbo.Cust_Company.CompanyID
WHERE (dbo.Cust_Company.Establis
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 (dbo.Cust_Company.Establis
(dbo.Cust_Company.Establis
(dbo.[User].LoginID LIKE ISNULL(@CustomerCode, dbo.[User].LoginID) + '%')
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER