Author |
Topic |
immad
Posting Yak Master
230 Posts |
Posted - 2013-09-23 : 07:11:24
|
this is my procedureALTER procedure [dbo].[hh] @EmpID VARCHAR(50), @FromDate DATETIME, @ToDate DATETIME, @Dept VARCHAR(50), @Remarks VARCHAR(50), @CID VARCHAR(50), @BID VARCHAR(50), @Grade VARCHAR(50), @UserID VARCHAR(50)ASBEGINCREATE TABLE #P2( CID INT, BID INT, EBID INT, EmployeeCode INT, DID INT, Date DATETIME, [Time IN] DATETIME, IAttendID INT, [Time Out] DATETIME, OAttendID INT, DiffHrs INT, DiffMin INT, Spend NVARCHAR(50), SID INT, Shift NVARCHAR(50), LTime DATETIME, HDTime DATETIME, EarlyArival DATETIME, ShiftTimeDiff NVARCHAR(50), ExcShort NVARCHAR(50), SpendTimeDiffInSec NVARCHAR(50), EShortTimeinSEC NVARCHAR(50), DutyHours INT, Remarks NVARCHAR(50), Comments NVARCHAR(50), InBetween INT, EShortTime NVARCHAR(50), userid VARCHAR(50))INSERT INTO #P2EXEC [26Procedure12] @EmpID,@FromDate,@ToDate,@Dept,@Remarks,@CID,@BID,@Grade,@useridUPDATE #P2SET CheckTime = DATEADD(DAY, -1, CheckTime)WHERE Status = 'O' and checktime>'2013-09-23 00:00:00.000' and datepart(hh,CheckTime) BETWEEN 0 AND 11i want to update that after insert in #P2 is that query correctplease help me outimmad uddin ahmed |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-23 : 08:26:10
|
quote: Originally posted by immad this is my procedureALTER procedure [dbo].[hh] @EmpID VARCHAR(50), @FromDate DATETIME, @ToDate DATETIME, @Dept VARCHAR(50), @Remarks VARCHAR(50), @CID VARCHAR(50), @BID VARCHAR(50), @Grade VARCHAR(50), @UserID VARCHAR(50)ASBEGINCREATE TABLE #P2( CID INT, BID INT, EBID INT, EmployeeCode INT, DID INT, Date DATETIME, [Time IN] DATETIME, IAttendID INT, [Time Out] DATETIME, OAttendID INT, DiffHrs INT, DiffMin INT, Spend NVARCHAR(50), SID INT, Shift NVARCHAR(50), LTime DATETIME, HDTime DATETIME, EarlyArival DATETIME, ShiftTimeDiff NVARCHAR(50), ExcShort NVARCHAR(50), SpendTimeDiffInSec NVARCHAR(50), EShortTimeinSEC NVARCHAR(50), DutyHours INT, Remarks NVARCHAR(50), Comments NVARCHAR(50), InBetween INT, EShortTime NVARCHAR(50), userid VARCHAR(50))INSERT INTO #P2EXEC [26Procedure12] @EmpID,@FromDate,@ToDate,@Dept,@Remarks,@CID,@BID,@Grade,@useridUPDATE #P2SET CheckTime = DATEADD(DAY, -1, CheckTime)WHERE Status = 'O' and checktime>'2013-09-23 00:00:00.000' and datepart(hh,CheckTime) BETWEEN 0 AND 11i want to update that after insert in #P2 is that query correctplease help me outimmad uddin ahmed
I don't think this is correct. In the update statement, you are trying to update the column CheckTime in table #P2. Yet, in the create table statement for #P2, I don't see a column named CheckTime.Can you explain in more detail what you are trying to do? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-09-23 : 12:03:52
|
1.if you're planning to use INSERT...EXEC the strcture of table should exactly match procedure output2. If you need an additional column in table you need to create it after using ALTER TABLE ...ADD syntax and then UPDATE it------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|