SQL Server经验

一.SQL Server 经验

1.一个简单的存储过程

CREATE PROCEDURE [dbo].[usp_GetFolderID](@itemID INT,  
          @folderID INT OUTPUT)  
AS
BEGIN TRY 

     SELECT @folderID = FOLDERID
     FROM TestItemVer
     WHERE ITEM_ID = @itemID  
      
     IF @folderID IS NULL  
      SET @folderID = 0   
      
     RETURN @folderID  
END TRY
BEGIN CATCH
    EXEC usp_ErrorHandle;
END CATCH

2. 存储过程经验
　　1）参数

CREATE PROCEDURE [dbo].[usp_Proc]
(
    @TargetTypeID    int,
    @DataKey        int,    
    @TlmIP            NVARCHAR(200) = null,
    @TlmPort        NVARCHAR(100) = null,
    @TauID            int = null,
    @ExtInfo        NVARCHAR(max) = null
)

　　2）定义变量，表变量

                DECLARE @BaseJobId INT;
        DECLARE @Level INT;
        DECLARE @TasksTable TABLE
        (
            JOB_ID                INT,
            [ORDER]                INT,
            TP_ID                INT,
            ASSIGNED_USERID        NVARCHAR(100),
            TLM_ID                INT,
            EXE_TAU_ID            INT,
            BASE_JOB_ID            INT,
            PARENT_JOB_ID        INT,
            [LEVEL]                INT
        );        

　　3）分层查询（有自关联关系)

WITH lmenu(JOB_ID,[ORDER],TP_ID,ASSIGNED_USERID,TLM_ID,
            EXE_TAU_ID,BASE_JOB_ID,PARENT_JOB_ID,LEVEL) AS  
        (     
            SELECT JOB_ID,[ORDER],TP_ID,ASSIGNED_USERID,TLM_ID,
            EXE_TAU_ID,BASE_JOB_ID,PARENT_JOB_ID,0 LEVEL 
            FROM TestJob 
            WHERE JOB_ID = @DataKey
            UNION ALL     
            SELECT A.JOB_ID,A.[ORDER],A.TP_ID,A.ASSIGNED_USERID,A.TLM_ID,
            A.EXE_TAU_ID,A.BASE_JOB_ID,A.PARENT_JOB_ID,b.LEVEL+1 FROM TestJob A,lmenu B      
            where A.JOB_ID = B.PARENT_JOB_ID 
        )
        INSERT @TasksTable
        SELECT * FROM lmenu

　　4)向表变量插入数据

                DECLARE @jobVTL TABLE(
            JOB_ID INT NOT NULL
            ,[ORDER] INT NOT NULL
        );
        INSERT @jobVTL 
            SELECT JB.JOB_ID,[ORDER]
            FROM @TasksTable JB 
        INNER JOIN dbo.TestPlan TP WITH(NOLOCK)
            ON JB.TP_ID = TP.TP_ID
        INNER JOIN dbo.User_Info UI WITH(NOLOCK)
            ON JB.ASSIGNED_USERID = UI.USER_ID
        LEFT OUTER JOIN dbo.TLM TLM WITH(NOLOCK)
            ON JB.TLM_ID = TLM.TLM_ID
        LEFT OUTER JOIN dbo.TAU TAU WITH(NOLOCK)
            ON JB.EXE_TAU_ID = TAU.TAU_ID        

　　5）统计并排序

                        --Count by order and result                
            DECLARE @tcTestResultCount TABLE(
                 [ORDER] INT NOT NULL
                ,PASS INT NOT NULL 
                ,FAIL INT NOT NULL 
                ,[N/A] INT NOT NULL 
                ,[N/E] INT NOT NULL 
            );        
            INSERT @tcTestResultCount
            SELECT VTL.[ORDER], ISNULL([PASS],0) [PASS],
                    ISNULL([FAIL],0)[FAIL],
                    ISNULL([N/A],0)[N/A],
                    ISNULL([N/E],0)[N/E]
            FROM @jobVTL VTL
            LEFT JOIN 
            (
            SELECT [ORDER], [PASS],[FAIL],[N/A],[N/E] FROM
            @tcTestResult TCR
            pivot(COUNT(TEST_RESULT) FOR TEST_RESULT IN([Pass],[Fail],[N/A],[N/E])
            ) TB 
            ) TCR
            ON VTL.[ORDER] = TCR.[ORDER]
            ORDER BY VTL.[ORDER]    

　　6）计算百分比

DECLARE @tcTestResultTotal TABLE(
                 [ORDER] NVARCHAR(10) NOT NULL
                 ,[COUNT] INT 
                ,PASS INT 
                ,FAIL INT 
                ,[N/A] INT 
                ,[N/E] INT 
                ,PASS_RATE NVARCHAR(10) 
            );    
            
            INSERT @tcTestResultTotal
            SELECT TR.[ORDER], [COUNT],[PASS],[FAIL],[N/A],[N/E],
            CASE WHEN [COUNT] > 0 THEN CONVERT(varchar(20),CAST(([PASS]+0.0)*100/[COUNT] AS DECIMAL(10, 2)))+'%'
                        ELSE '0.00%'
                        END [PASS_RATE]
            FROM @tcTROrderCount TR
            INNER JOIN @tcTestResultCount TRC
            ON TR.[ORDER] = TRC.[ORDER]
            
            SELECT * FROM @tcTestResultTotal

　　7）查询第一条数据

　　　　SELECT TOP 1 ID FROM TESTJOB

　　8）执行存储过程

　　　　EXEC [dbo].[usp_GetObjects] 7, 21838;

2.函数

　　1）创建函数　

CREATE FUNCTION [dbo].[usp_GetDBNull]
(
    @inputString varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @outputString varchar(8000);
    SET @outputString = NULL;
    
    IF @inputString <> '' SET @outputString = @inputString;
    
    RETURN @outputString;
END

　　2）执行函数

　　　SELECT [dbo].[usp_GetDBNull]('');
 　　  SELECT [dbo].[usp_GetDBNull]('ABC');

 　　3）使用函数

SELECT    TP_NAME ItemName,
                SCHEDULED_START_DATE,
                SCHEDULED_END_DATE,
                SCHEDULED_RELEASE_DATE,
                dbo.ufn_GetTestItemCountByTestPlan(TP.TP_ID, 'RQ') AS TOTAL_RQ,
                dbo.ufn_GetTestItemCountByTestPlan(TP.TP_ID, 'TC') AS TOTAL_TC,
                PURPOSE,
                SCOPE,
                ENVIRONMENT,
                [DESCRIPTION],
                @MailTitle MAIL_TITLE,
                @APP_ID APP_ID,
                @APP_ORDER APP_ORDER
        FROM    dbo.TestPlan TP
        WHERE    TP.TP_ID = @DataKey;