stored procedure aww! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stored procedure aww!

Author

Topic

kshitizgp
Starting Member

31 Posts

Posted - 2011-12-30 : 11:07:37

Guyz i need changes in this stored procedure "between"
like ven i enter a start date and end date on as 26 dec twice i get error
set @Result = (select COUNT(*) from Employee_Leave where Employee_Id = @Employee_Id and Leave_Date = @start_Date AND )

and does not duplicate the row`s
what i neeed is that when the user enters from 24 to 28 dec .. it should save al these ..
Now am not able to store after 26 ...(27,28)
how shuld i structure my query ... hope u guyz getting it !

StoredProcedure [dbo].[AddLeaveForm]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[AddLeaveForm]

@Employee_Id numeric(18,0),

@Leave_Status varchar(50),
@Created_By numeric(18,0) ,
@Is_Active bit = 1 ,

@Modified_By numeric(18,0) = NULL,
@Modified_Date datetime = NULL,
@start_Date datetime,
@end_Date datetime

AS
BEGIN

BEGIN
while(@start_Date <= @end_Date)
BEGIN
declare @Result numeric(18,0)

set @Result = (select COUNT(*) from Employee_Leave where Employee_Id = @Employee_Id and Leave_Date BETWEEN @start_Date AND @end_Date)

if(@Result = 0)
begin

INSERT INTO

[Employee_Leave]

(
[Employee_Id] ,
[Leave_Date]
,[Leave_Status]
,[Created_By]
,[Created_Date]
)

VALUES
(
@Employee_Id ,
@start_Date
,@Leave_Status
,@Created_By
,CURRENT_TIMESTAMP

)
end
else
begin

select -1

end
set @start_Date = DATEADD(DD, 1, @start_Date);

END -- End While Loop
END
END
select * from Employee_Leave;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-01 : 02:17:20

i think you need only this


ALTER PROCEDURE [dbo].[AddLeaveForm]

@Employee_Id numeric(18,0),

@Leave_Status varchar(50),
@Created_By numeric(18,0) , 
@Is_Active bit = 1 ,

@Modified_By numeric(18,0) = NULL,
@Modified_Date datetime = NULL,
@start_Date datetime, 
@end_Date datetime

AS

INSERT INTO 

[Employee_Leave]

(
[Employee_Id] ,
[Leave_Date]
,[Leave_Status]
,[Created_By]
,[Created_Date]
)
SELECT 
@Employee_Id , 
[Date]
,@Leave_Status
,@Created_By
,CURRENT_TIMESTAMP
FROM dbo.CalendarTable(@start_Date, @end_Date,0,0)

the code for CalendarTable you can find here

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources