如何将StoredProcedures转换为视图 [英] How to convert StoredProcedures to views
本文介绍了如何将StoredProcedures转换为视图的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
如何将StoredProcedures转换为视图
Hi,
how to convert StoredProcedures to Views
USE [ActatekDB]
GO
/****** Object: StoredProcedure [dbo].[SP_GenerateAbsentPresentReport] Script Date: 05/19/2014 11:46:11 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
/* Purpose:
Created on 03-11-2010
*/
ALTER PROCEDURE [dbo].[SP_GenerateAbsentPresentReport]
(
@pUserID nvarchar(50),
@pDepartmentID int,
@pStartDateTime datetime,
@pEndDateTime datetime,
@pMondaySelected int,
@pTuesdaySelected int,
@pWednesdaySelected int,
@pThursdaySelected int,
@pFridaySelected int,
@pSaturdaySelected int,
@pSundaySelected int,
@pHolidaySelected int
)
AS
-------------------------------------------------------------------------------------------------------------------
--An absolute 'MUST HAVE' statements in every store procedure //HEAD
Declare @ErrorCode int
Select @ErrorCode = @@Error
Declare @TransactionCountOnEntry int
If @ErrorCode = 0
Begin
Select @TransactionCountOnEntry = @@TranCount
BEGIN TRANSACTION
End
--end absolute 'Must Have' Statement
-------------------------------------------------------------------------------------------------------------------
If @ErrorCode = 0
Begin
-- Create table variable instead of SQL temp table because report wont pick up the temp table
DECLARE @tempAbsentPresentReport TABLE
(
UserID nvarchar(50),
LogDate datetime,
LogDay varchar(20),
Status nvarchar(50),
PivotValue int,
PivotValue2 int
)
-- Declare variables for the while loop
Declare @LogUserID nvarchar(50)
-- Filter off userID, departmentID (get distinct list of users)
Declare LogCursor Cursor For
Select distinct access_event_logs.USERID
From access_event_logs
Left Join access_user on access_user.User_ID = access_event_logs.USERID
Left Join access_user_dept on access_user.User_ID = access_user_dept.User_ID
Where ((Dept_ID = @pDepartmentID) OR (@pDepartmentID IS NULL))
And ((access_event_logs.USERID LIKE '%' + @pUserID + '%') OR (@pUserID IS NULL))
And (access_event_logs.USERID !='UNKNOWN USER') -- Ignore UNKNOWN USER
Order by access_event_logs.USERID
Open LogCursor
Fetch Next
From LogCursor
Into @LogUserID
Declare @LoopStartDateTime datetime
Declare @LoopEndDateTime datetime
Declare @PivotValue2 int -- Report (row group by)
Set @PivotValue2 = 0
Declare @PivotValue int -- Report (column group by)
Set @PivotValue = 0
-- Set the piviot counter, used to determine the coloumn position
Declare @PivotCount int
Set @PivotCount = 1
Declare @MaxCols int -- Number of coloums per page of report
Set @MaxCols = 8
-- Loop thru each user
While @@FETCH_STATUS = 0
Begin
Print 'Process user ID: ' + @LogUserID
-- initialise
Set @LoopStartDateTime = @pStartDateTime
-- inclusive of end day (so add a day to make it the next day)
Set @LoopEndDateTime = DATEADD(day, 1, @pEndDateTime)
Set @PivotValue2 = 0 -- next user, reset pivot value
Set @PivotCount = @MaxCols -- next user, set piviot count to max value
-- Loop thru each day (from start datetime to end datetime)
While (@LoopEndDateTime > @LoopStartDateTime)
Begin
-- Reset piviot counter when reach max coloumns
if @PivotCount= @MaxCols
Begin
set @PivotCount=1
If @PivotValue2 >= 5000000 Set @PivotValue2 = 0 -- reset when reach a limit
set @PivotValue2 = @PivotValue2 + 1
End
Declare @WeekDay varchar(20) -- week day
Set @WeekDay = DATENAME(dw, @LoopStartDateTime)
Declare @Status varchar(50)
Set @Status = '' -- reset
-- Check for holiday
Declare @IsHoliday int
Set @IsHoliday = null
Select @IsHoliday = holidayID
From access_holiday
Where CONVERT(datetime, convert(varchar,access_holiday.iyear) + '-' + convert(varchar,access_holiday.imonth) + '-' + convert(varchar,access_holiday.iday) ) = @LoopStartDateTime
Declare @Proceed int
Set @Proceed = 1 -- initialised
If @IsHoliday is not null -- it is a holiday
Begin
Set @Status = 'Holiday'
If (@pHolidaySelected =1) -- dont want show holiday
Begin
Set @Proceed = 0; -- set flag to indicate no insertion for current interation
End
End
If @Proceed = 1
BEGIN
If LOWER(@WeekDay) = 'monday'
Begin
If (@pMondaySelected =1) -- dont want show monday
Begin
Set @Proceed = 0; -- dont proceed further go to next iteration
End
else
Begin
Set @PivotValue = @PivotCount
Set @PivotCount = @PivotCount +1
Set @WeekDay = 'Mon'
End
End
Else If LOWER(@WeekDay) = 'tuesday'
Begin
If (@pTuesdaySelected =1) -- dont want show tuesday
Begin
Set @Proceed = 0; -- dont proceed further go to next iteration
End
else
Begin
Set @PivotValue = @PivotCount
Set @PivotCount = @PivotCount +1
Set @WeekDay = 'Tue'
End
End
Else If LOWER(@WeekDay) = 'wednesday'
Begin
If (@pWednesdaySelected =1) -- dont want show wednesday
Begin
Set @Proceed = 0; -- dont proceed further go to next iteration
End
else
Begin
Set @PivotValue = @PivotCount
Set @PivotCount = @PivotCount +1
Set @WeekDay = 'Wed'
End
End
Else If LOWER(@WeekDay) = 'thursday'
Begin
If (@pThursdaySelected =1) -- dont want show thursday
Begin
Set @Proceed = 0; -- dont proceed further go to next iteration
End
else
Begin
Set @PivotValue = @PivotCount
Set @PivotCount = @PivotCount +1
Set @WeekDay = 'Thur'
End
End
Else If LOWER(@WeekDay) = 'friday'
Begin
If (@pFridaySelected =1) -- dont want show friday
Begin
Set @Proceed = 0; -- dont proceed further go to next iteration
End
else
Begin
Set @PivotValue = @PivotCount
Set @PivotCount = @PivotCount +1
End
End
Else If LOWER(@WeekDay) = 'saturday'
Begin
If (@pSaturdaySelected =1) -- dont want show saturday
Begin
Set @Proceed = 0; -- dont proceed further go to next iteration
End
else
Begin
Set @PivotValue = @PivotCount
Set @PivotCount = @PivotCount +1
Set @WeekDay = 'Sat'
End
End
Else If LOWER(@WeekDay) = 'sunday'
Begin
If (@pSundaySelected =1) -- dont want show sunday
Begin
Set @Proceed = 0; -- dont proceed further go to next iteration
End
else
Begin
Set @PivotValue = @PivotCount
Set @PivotCount = @PivotCount +1
Set @WeekDay = 'Sun'
End
End
END
If (@Proceed = 1) -- can proceed to insert
Begin
Declare @Present int
Set @Present = null
-- Check whether he/she show up or not
Select @Present = id
From access_event_logs
Where LOWER(EVENTID) = 'in' And USERID = @LogUserID
And LOCALTIMESTAMP >= @LoopStartDateTime And LOCALTIMESTAMP < DATEADD(DAY, 1, @LoopStartDateTime)
If @Present is not null
Begin
If (LEN(@Status) > 0) -- if there are other status already - append at the end
Set @Status = @Status + '(Present)'
Else
Set @Status = 'Present'
End
Else -- absent
Begin
if (LOWER(@Status) != 'holiday')
Set @Status = 'Absent'
End
Print '- Date: ' + Convert(varchar, @LoopStartDateTime, 13) + ', Day:' + @WeekDay + ', status: ' + @Status
Insert into @tempAbsentPresentReport (UserID, LogDate, LogDay, Status, PivotValue, PivotValue2)
values(@LogUserID, DATEADD(HOUR, 0, DATEDIFF(DAY, 0, @LoopStartDateTime)), @WeekDay, @Status,
@PivotValue, @PivotValue2 )
End
Set @LoopStartDateTime = DATEADD(DAY, 1, @LoopStartDateTime) -- increment a day
End
Fetch Next
From LogCursor
Into @LogUserID
End
Close LogCursor
Deallocate LogCursor
Select *
From @tempAbsentPresentReport tempTable
Left Join access_user on access_user.User_ID = tempTable.UserID
Order By tempTable.UserID, LogDate, PivotValue2, PivotValue
End
-------------------------------------------------------------------------------------------------------------------
--An absolute 'MUST HAVE' statement in every store procedure //TAIL
If @@TranCount > @TransactionCountOnEntry
Begin
If @ErrorCode = 0
COMMIT TRANSACTION
Else
ROLLBACK TRANSACTION
End
return @ErrorCode
-------------------------------------------------------------------------------------------------------------------推荐答案
Forget it see all those lovely variables you pass in, views cannot accept variables. The huge IF agglomeration, views cannot is IF - you need to use a CASE statement.
Views can ONLY have select and function calls, converting that abomination to a view is going to be a nightmare.
Forget it see all those lovely variables you pass in, views cannot accept variables. The huge IF agglomeration, views cannot is IF - you need to use a CASE statement.
Views can ONLY have select and function calls, converting that abomination to a view is going to be a nightmare.
这篇关于如何将StoredProcedures转换为视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
