How to Compare the user Given date with the table date using Store Procedure.
hi I have one date fields represents StartDate Using CalenderExtender and textbox field Represents ID. Im passing the 2 parameter from Code-Behind(
ASP.NET) to Stored Procedure..... IN the Table having 4 columns **ID**,**StartDate**,**Enddate** and **ReturntoWorkDate**: My need is If i pass the parameter from Code-Behind it should compare with **StartDate**, **enddate** and **ReturntoWorkDate** and it should displays the record from the Table. Examlple In the table **ID** : 001 **startdate** : 12-11-2010 **EndDate** : 12-19-2010 **ReturntoWorkDate** : 12-20-2010 i'm passing this parameters **ID** : 001 and **Startdate** : 12-13-2010 **OUTPUT:** **ID** :001 || **Startdate** : 12-11-2010 || **Enddate** : 12-19-2010 || **ReturntoWorkDate** : 12-20-2010 For Above Question **NEED STORED PROCEDURE ONLY** This is the code..... Alter PROCEDURE [dbo].[sp_SearchLeaveDate] @employeeid varchar(30), @leavestartdate varchar(30) As Begin Declare @employee varchar(max) declare @leavestart varchar(Max) select @employee = employeeid, @leavestart = leavestartdate from LeaveRequest where employeeid = @employeeid and (between leavestartdate >=@leavestartdate or leaveenddate <= @leavestartdate) end Can any one Help me please.............
This answer has nothing to do with your question, but I want to give you an advice. You NOT should prefix your procedures with sp_ This is why: > Don't use the prefix "sp_" in the > stored procedure name if you need to > create a stored procedure to run in a > database other than the master > database. The prefix "sp_" is used in > the system stored procedures names. > Microsoft does not recommend to use > the prefix "sp_" in the user-created > stored procedure name, because SQL > Server always looks for a stored > procedure beginning with "sp_" in the > following order: the master database, > the stored procedure based on the > fully qualified name provided, the > stored procedure using dbo as the > owner, if one is not specified. So, > when you have the stored procedure > with the prefix "sp_" in the database > other than master, the master database > is always checked first, and if the > user-created stored procedure has the > same name as a system stored > procedure, the user-created stored > procedure will never be executed. Read more at: [
i think this will work....... for my self it's working.... ALTER PROCEDURE [dbo].[sp_SearchLeaveDate] @employeeid varchar(30), @leavestartdate varchar(30) As Begin Declare @employee varchar(max) declare @leavestart Datetime set @leavestart = @leavestartdate select count(*) from LeaveRequest where employeeid = @employeeid and @leavestart between leavestartdate and returndate and status = 'Pending' or status = 'Approved' End