Help About Stored Procedure In Sql Server 2008

[Palyadav]

How can i add below 4 field in Existing stored procedure ?

DECLARE @StartTime datetime
DECLARE @CurrentDate datetime
Declare @Days int
Declare @Hours int
Declare @Minutes int
declare @Second int
SET @StartTime = EmployeeTempAttendanceDetail.EmpInTime
SET @CurrentDate = EmployeeTempAttendanceDetail.EmpOutTime

SET @Days = DATEDIFF(Day, @StartTime, @CurrentDate)
SET @Hours = DATEDIFF(Hour, @StartTime, @CurrentDate) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @CurrentDate) - ((@Days * 24)*60) - (@Hours * 60) 
SET @Second = DATEDIFF(Second, @StartTime, @CurrentDate) -(((@Days * 24)*60) *60) - ((@Hours * 60)*60) - (@Minutes *60)

This is my Existing stored procedure..

CREATE Procedure [dbo].[spReportDailyAttendance]
(@DivisionId int,
@EmployeeName varchar(50),
@CardNo int,
@DesignationId int,
@TypeId int,
@GradeId int,
@FromDate datetime,
@ToDate datetime,
@Status varchar(50),
@Where varchar(max))
AS
BEGIN
Declare @SQL varchar(max)

SEt @SQL='SELECT     CompanyMaster.CompanyName, DivisionMaster.DivisionName, DepartmentMaster.DepartmentName, BranchMaster.BranchName, 
                      EmployeeTempAttendanceDetail.EmpAttendanceTempId, EmployeeTempAttendanceDetail.EmployeeId, EmployeeTempAttendanceDetail.LogTime, 
                      EmployeeTempAttendanceDetail.ShiftStartTime, EmployeeTempAttendanceDetail.ShiftEndTime, EmployeeTempAttendanceDetail.EarlyComingTime, 
                      EmployeeTempAttendanceDetail.LateComingTime, EmployeeTempAttendanceDetail.EarlyGoingTime, EmployeeTempAttendanceDetail.LateGoingTime, 
                      EmployeeTempAttendanceDetail.StartRecessTime, EmployeeTempAttendanceDetail.EndRecessTime, EmployeeTempAttendanceDetail.Date, 
                      EmployeeTempAttendanceDetail.EmpInTime, EmployeeTempAttendanceDetail.EmpOutTime, EmployeeTempAttendanceDetail.EmpRecessStartTime, 
                      EmployeeTempAttendanceDetail.EmpRecessEndTime, EmployeeTempAttendanceDetail.TotalEmpWorkingHrs, EmployeeTempAttendanceDetail.TotalEmpWorkedHrs, 
                      EmployeeTempAttendanceDetail.NotTotalEmpWorkingHrs, EmployeeTempAttendanceDetail.NotTotalEmpWorkedHrs, EmployeeTempAttendanceDetail.OverTimeWork, 
                      EmployeeTempAttendanceDetail.HalfDay, EmployeeTempAttendanceDetail.Status, EmployeeMaster.EmployeeId AS Expr1, EmployeeMaster.CardNo, 
                      EmployeeMaster.EmployeeName, EmployeeMaster.EmployeeFatherName, EmployeeMaster.EmployeeSurName, EmployeeMaster.DateOfBirth, 
                      EmployeeMaster.JoinDate, EmployeeMaster.Gender, EmployeeMaster.CompanyId, EmployeeMaster.BranchId, EmployeeMaster.DivisionId, 
                      EmployeeMaster.DepartmentId, EmployeeMaster.OverTimeAllow, GradeMaster.GradeID, GradeMaster.GradeName, DesignationMaster.DesignationId, 
                      DesignationMaster.DesignationName, TypeMaster.TypeName, TypeMaster.TypeId
FROM                  EmployeeDesignationDetail INNER JOIN
                      DesignationMaster ON EmployeeDesignationDetail.DesignationId = DesignationMaster.DesignationId INNER JOIN
                      EmployeeTempAttendanceDetail INNER JOIN
                      EmployeeMaster ON EmployeeTempAttendanceDetail.EmployeeId = EmployeeMaster.EmpId INNER JOIN
                      CompanyMaster ON EmployeeMaster.CompanyId = CompanyMaster.CompanyId INNER JOIN
                      BranchMaster ON EmployeeMaster.BranchId = BranchMaster.BranchId INNER JOIN
                      DepartmentMaster ON EmployeeMaster.DepartmentId = DepartmentMaster.DepartmentId INNER JOIN
                      DivisionMaster ON EmployeeMaster.DivisionId = DivisionMaster.DivisionId ON EmployeeDesignationDetail.EmployeeId = EmployeeMaster.EmpId INNER JOIN
                      EmployeeGradeDetail INNER JOIN
                      GradeMaster ON EmployeeGradeDetail.GradeId = GradeMaster.GradeID ON EmployeeMaster.EmpId = EmployeeGradeDetail.EmployeeId INNER JOIN
                      EmployeeTypeDetail INNER JOIN
                      TypeMaster ON EmployeeTypeDetail.EmployeeTypeId = TypeMaster.TypeId ON EmployeeMaster.EmpId = EmployeeTypeDetail.EmployeeId Where 1=1 and EmployeeTempAttendanceDetail.[Date] >='''+Cast(@FromDate as varchar(50))+''' and EmployeeTempAttendanceDetail.[Date]<='''+Cast(@ToDate as varchar(50))+''''
                      
                      
    IF @DivisionId != 0 BEGIN
		SET @SQL = @SQL + N' AND EmployeeMaster.DivisionId = ' +  CAST(@DivisionId as varchar(100))
	END                  
    IF @EmployeeName IS NOT NULL BEGIN
		SET @SQL = @SQL + N' AND EmployeeMaster.EmployeeName = ''' + REPLACE(@EmployeeName,'''','''''') + ''' '
	END
	IF @CardNo != 0  BEGIN
		SET @SQL = @SQL + N' AND EmployeeMaster.CardNo = ' + CAST(@CardNo as varchar(100))
	END                  
    IF @DesignationId != 0  BEGIN
		SET @SQL = @SQL + N' AND EmployeeMaster.DesignationId = ' +  CAST(@DesignationId as varchar(100))
	END
	IF @TypeId != 0  BEGIN
		SET @SQL = @SQL + N' AND EmployeeMaster.TypeId = ' +  CAST(@TypeId as varchar(100))
	END                  
    IF @GradeId != 0  BEGIN
		SET @SQL = @SQL + N' AND EmployeeMaster.GradeId = ' +  CAST(@GradeId as varchar(100))
	END
	IF @Status != 0  BEGIN
		SET @SQL = @SQL + N' AND EmployeeTempAttendanceDetail.Status = ' +  CAST(@Status as varchar(100))
	END

EXEC(@SQL)
END

How can I achieve it ?
Please anyone help me.
Thanks

[Rabbit]

Add in what manner?

[Palyadav]

I want to add as a column field after TypeMaster.Type Id


thanks

[Rabbit]

Append them to the SQL string.

[Palyadav]

You are right sir..but i have not get any idea how can i add in existing SP.I want add 3 fields(totalhou r,totalminute,t otalsecond) in my existing SP.

[Rabbit]

Appending to a string in a stored procedure is no different than appending to a string in any other SQL. I can only assume that you mean you don't know how to edit an existing stored procedure. In that case, instead of CREATE, use ALTER.

[Palyadav]

I have tried to add that 3 field

DATEDIFF(Hour, EmployeeTempAttendanceDetail.EmpInTime,EmployeeTempAttendanceDetail.EmpOutTime) as TotalHour,
					  DATEDIFF(Minute, EmployeeTempAttendanceDetail.EmpInTime,EmployeeTempAttendanceDetail.EmpOutTime) - (TotalHour * 60) as TotalMinute,
					  DATEDIFF(Second, EmployeeTempAttendanceDetail.EmpInTime,EmployeeTempAttendanceDetail.EmpOutTime) - ((TotalHour * 60)*60) - (TotalMinute *60) as TotalSecond

but it throw the error..
Plz help me how can i add this 3 field in my existing stored procedure ?

[Rabbit]

We need to see how you added it to your current code. And also the text of the error message.

[anterec]

Just to add, it isn't clear from your description what it is that you want to do.

Are you looking to include extra fields in the data returned by the stored procedure, or are you looking to add parameters?

[Palyadav]

Thanks this question is solved.