Problem with Multiple search Stored Procedure
-
Hi all, i was trying to do a mutiple select with following fileds ServiceCategoryName(field)->ServiceCategory(Table) ServiceName(field)->Service(Table) ServiceLocation(field)->Location(Table) Created Date,SendersName,SendersEmaild(fields) ->ServiceItemForward (Table) DB:SQLServer 2008 and the sp i wrote is following what changes should i make to this ALTER PROC [dbo].[usp__Search_ServiceItemForward] @CategoryID int, @ServiceID int, @LocationName varchar(100), @createdFromDate date, @createdToDate date, @sender varchar(100), @senderEmail varchar(100) AS if(@createdFromDate is null) set @createdFromDate =dateadd(d,-7, CONVERT(date, GETDATE())) if(@createdToDate is null) set @createdToDate=CONVERT(date, GETDATE()) Begin SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRAN SELECT dbo.ServiceItemForward.* FROM dbo.ServiceItemForward INNER JOIN dbo.ServiceItems ON dbo.ServiceItemForward.ServiceItemID = dbo.ServiceItems.ServiceItemID INNER JOIN dbo.Service ON dbo.ServiceItems.ServiceID = dbo.Service.ServiceID INNER JOIN dbo.ServiceCategory ON dbo.Service.ServiceCategoryID = dbo.ServiceCategory.ServiceCategoryID where (@CategoryID=0 or ServiceCategory.ServiceCategoryID = @CategoryID) and (@ServiceID=0 or Service.ServiceID = @ServiceID) and (@LocationName=0 or ServiceItems.ServiceLocation = @LocationName) and (ServiceItemForward.CreatedDate Between @createdFromDate and @createdToDate) and(@sender is null or ServiceItemForward.SendersName=@sender) and (@senderEmail is null or ServiceItemForward.SendersEmaild=@senderEmail) COMMIT End please help me out in htis.... Thanks in advance
-
Hi all, i was trying to do a mutiple select with following fileds ServiceCategoryName(field)->ServiceCategory(Table) ServiceName(field)->Service(Table) ServiceLocation(field)->Location(Table) Created Date,SendersName,SendersEmaild(fields) ->ServiceItemForward (Table) DB:SQLServer 2008 and the sp i wrote is following what changes should i make to this ALTER PROC [dbo].[usp__Search_ServiceItemForward] @CategoryID int, @ServiceID int, @LocationName varchar(100), @createdFromDate date, @createdToDate date, @sender varchar(100), @senderEmail varchar(100) AS if(@createdFromDate is null) set @createdFromDate =dateadd(d,-7, CONVERT(date, GETDATE())) if(@createdToDate is null) set @createdToDate=CONVERT(date, GETDATE()) Begin SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRAN SELECT dbo.ServiceItemForward.* FROM dbo.ServiceItemForward INNER JOIN dbo.ServiceItems ON dbo.ServiceItemForward.ServiceItemID = dbo.ServiceItems.ServiceItemID INNER JOIN dbo.Service ON dbo.ServiceItems.ServiceID = dbo.Service.ServiceID INNER JOIN dbo.ServiceCategory ON dbo.Service.ServiceCategoryID = dbo.ServiceCategory.ServiceCategoryID where (@CategoryID=0 or ServiceCategory.ServiceCategoryID = @CategoryID) and (@ServiceID=0 or Service.ServiceID = @ServiceID) and (@LocationName=0 or ServiceItems.ServiceLocation = @LocationName) and (ServiceItemForward.CreatedDate Between @createdFromDate and @createdToDate) and(@sender is null or ServiceItemForward.SendersName=@sender) and (@senderEmail is null or ServiceItemForward.SendersEmaild=@senderEmail) COMMIT End please help me out in htis.... Thanks in advance
You do realise in your null date test you are populating
@createdToDatein both tests. It would help to know what the problem is!Never underestimate the power of human stupidity RAH
-
You do realise in your null date test you are populating
@createdToDatein both tests. It would help to know what the problem is!Never underestimate the power of human stupidity RAH
Hi, I have cooreected that statement and the code was not wrkng.....
-
Hi, I have cooreected that statement and the code was not wrkng.....
reogeo2008 wrote:
I have cooreected that statement and the code was not wrkng....
If you post a problem, then describe what the code is supposed to do and what the error is. All we see is your code and the statement that it ain't workin', but you didn't explain what the desired result would be like or what you're trying to achieve. Why is there a commit, if you are only selecting records?
Bastard Programmer from Hell :suss:
-
reogeo2008 wrote:
I have cooreected that statement and the code was not wrkng....
If you post a problem, then describe what the code is supposed to do and what the error is. All we see is your code and the statement that it ain't workin', but you didn't explain what the desired result would be like or what you're trying to achieve. Why is there a commit, if you are only selecting records?
Bastard Programmer from Hell :suss: