Stored procedure giving error while implementing

While implementing the procedure I am getting errors:

Msg 156, Level 15, State 1, Procedure GET_USER_REPORT_DATA, Line 19
Incorrect syntax near the keyword 'select'.

Msg 102, Level 15, State 1, Procedure GET_USER_REPORT_DATA, Line 23
Incorrect syntax near ')'.

Here is the procedure

ALTER PROCEDURE [dbo].[GET_USER_REPORT_DATA]
    @From_Date datetime,
    @To_Date datetime
AS
BEGIN
    Select *
    into #GetUserTable
    from
        (select distinct 
             a.N_UserMkey, b.mkey,
             ISNULL(b.first_name + ' ' , '') + ISNULL(b.last_name,'') NAME
         from 
             inward_doc_tracking_trl AS a
         inner join  
             user_mst AS b on a.N_UserMkey = b.mkey
         where 
             a.U_datetime between @From_Date and @To_Date

         select distinct 
             a.mkey, b.ref_mkey
         from 
             inward_doc_tracking_hdr AS a
         inner join 
             inward_doc_tracking_trl AS b on a.mkey = b.ref_mkey 
                                          and a.U_datetime between @From_Date and @To_Date
        ) as xx

    SELECT * FROM #GetUserTable

    DROP TABLE #GetUserTable
END

---

试试这个，也看看我的评论

ALTER PROCEDURE [dbo].[GET_USER_REPORT_DATA]
    @From_Date datetime,
    @To_Date datetime
AS
BEGIN

    Select *
    --into #GetUserTable   --Try to never use temp table. Here no need to use temp table
    from
        (
            select distinct a.N_UserMkey, b.mkey,
                ISNULL(b.first_name + ' ' , '') + ISNULL(b.last_name,'') NAME
            from inward_doc_tracking_trl AS a
                inner join user_mst AS b on a.N_UserMkey = b.mkey
            where a.U_datetime between @From_Date and @To_Date
            union
            select distinct a.mkey, b.ref_mkey ,'' NAME   -- In your union tale, if you add runtime column then add value based on your datatype. If you add in first query, then name must give, but if you query is second or third then not compulsory
            from inward_doc_tracking_hdr AS a
                inner join inward_doc_tracking_trl AS b
                        on a.mkey = b.ref_mkey and a.U_datetime between @From_Date and @To_Date
        ) as xx

        --SELECT * FROM #GetUserTable
        --DROP TABLE #GetUserTable
END

---

我想你可能会忘记在你的内部查询中将你的select语句放在两个之间

ALTER PROCEDURE [dbo].[GET_USER_REPORT_DATA]
@From_Date DATETIME ,
@To_Date DATETIME
AS
BEGIN
    SELECT  *
    INTO    #GetUserTable
    FROM    ( SELECT DISTINCT
                        a.N_UserMkey ,
                        b.mkey ,
                        ISNULL(b.first_name + ' ', '')
                        + ISNULL(b.last_name, '') NAME
              FROM      inward_doc_tracking_trl a
                        INNER JOIN user_mst b ON a.N_UserMkey = b.mkey
              WHERE     a.U_datetime BETWEEN @From_Date AND @To_Date
              UNION
              SELECT DISTINCT
                        a.mkey ,
                        b.ref_mkey ,
                        ISNULL(c.first_name + ' ', '')
                        + ISNULL(c.last_name, '') NAME
              FROM      inward_doc_tracking_hdr a
                        INNER JOIN inward_doc_tracking_trl b ON a.mkey = b.ref_mkey
                                                          AND a.U_datetime BETWEEN @From_Date AND @To_Date
                        INNER JOIN user_mst c ON c.N_UserMkey = b.mkey
            ) AS xx   


    SELECT  *
    FROM    #GetUserTable
    DROP TABLE #GetUserTable
END

链接地址: http://www.djcxy.com/p/24754.html

上一篇: 如何使用SQL重命名数据库表中的列？

下一篇: 存储过程在执行时给出错误