MSSQL Procudure Sample

代码：

USE [Internal_Timesheet] 
GO
/****** Object:  StoredProcedure [dbo].[ManageTSReminder]    Script Date: 06/27/2014 18:16:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[managerTSReminder]
AS

-- =============================================
-- Author:        xxxxxx
-- Create date: 06/27/2014
-- Description:    Checking Missed TimeSheet
-- =============================================

DECLARE     @Uid varchar(25),
            @FirstName varchar(25),
            @LastName varchar(25),
            @team varchar(25),
            @Uid2 varchar(25),
            @currentdate DATETIME,
            @USlist varchar(MAX),
            @CNlist varchar(MAX)

SET @currentdate = CURRENT_TIMESTAMP
SET @USlist = ''
SET @CNlist = ''                
            
DECLARE curP CURSOR 
FOR

    SELECT uid, first_name, last_name, team
    FROM Internal_Timesheet.dbo.user_info
    WHERE status = 1
    AND uid <> 'xxxxxxx'

OPEN curP
FETCH NEXT FROM curP INTO @Uid, @FirstName, @LastName, @team

WHILE (@@FETCH_STATUS = 0)
BEGIN
    
    DECLARE curP2 CURSOR 
    FOR
    
        SELECT uid FROM Internal_Timesheet.dbo.projects_user_timesheet_xref
        WHERE uid = @Uid
        AND week_start_date < (DATEADD(wk, DATEDIFF(wk, 0,@CURRENTDATE),0))

    OPEN curP2
    FETCH NEXT FROM curP2 INTO @Uid2
    
    IF (@@FETCH_STATUS <> 0)
        BEGIN
            IF(@team = 'US') BEGIN
                SET @USlist = @USlist + @FirstName + ' ' + @LastName + ';
'            
            END
            ELSE IF(@team = 'CN') BEGIN
                SET @CNlist = @CNlist + @FirstName + ' ' + @LastName + ';
'
            END
        END
    
    CLOSE curP2
    DEALLOCATE curP2
    
    Fetch Next From curP Into @Uid, @FirstName, @LastName, @team
END
    PRINT 'US Team: ' + @USlist
    --EXEC msdb.dbo.sp_send_dbmail @profile_name='profilename',
                                --@recipients='xxxxx@xxxxx.com',
                                --@subject='Weekly TimeSheet Missing List',
                                --@body='The system can not find the following user's TimeSheet: 

--@CNlist 

--If you have any questions, please contact the system admin.'
    
    PRINT 'CN Team: ' + @CNlist
    --EXEC msdb.dbo.sp_send_dbmail @profile_name='profilename',
                                --@recipients='xxxxx@xxxxx.com',
                                --@subject='Weekly TimeSheet Missing List',
                                --@body='The system can not find the following user's TimeSheet: 

--@USlist 

--If you have any questions, please contact the system admin.'

CLOSE curP
DEALLOCATE curP



Check Email status

SELECT * FROM msdb.dbo.sysmail_sentitems

SELECT * FROM msdb.dbo.sysmail_mailitems

SELECT * FROM msdb.dbo.sysmail_log