在sql server中解析存储过程时出错

2 回答

• 0

  您可能想要检查UPDATE的语法 . 你需要这样的东西：

  UPDATE table
  SET col1 = ..., col2 = ...
  

  您的更新语句缺少表引用 . 仅列出3部分列名称是不够的（无论如何这都不是好的做法 . ）

  FWIW，我想你可以使用MERGE更简单地做到这一点 . 值得一试 .

  回复于 2024-05-03T15:41:42+08:00
• 0

  你现在可以检查我的程序是对还是错 .

  ALTER PROCEDURE IATFUPLOAD 
      -- Add the parameters for the stored procedure here
      --@p1 int = 0, 
      --@p2 int = 0
  
  AS
  BEGIN
  
      DECLARE @audit_id int, @audittype_id int, @CausingClause_id int
      declare @findings varchar(500), @Rating varchar(500), @lr_ind varchar(500), @root_cause varchar(500), @corr_action varchar(500), @by_whom varchar(500), @remarks varchar(500), @links varchar(500)
      declare @close_date datetime
      declare @count int
      set @count = 0
      declare @category_id_insert int
  
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      -- Insert statements for procedure here
      --SELECT @p1, @p2
      DECLARE db_cursor CURSOR FOR
                              SELECT  
                              AU.id                       AS audit_id,
                              AT.id                       AS audittype_id,
                              CC.id                       AS CausingClause_id,
                              IA.[Finding]                AS findings,
                              IA.[Rating]                 AS Rating,
                              IA.[Reach]          AS lr_ind,
                              IA.[Root_Cause]             AS root_cause,
                              IA.[Corrective_Action]      AS corr_action,
                              IA.[Action_Owner]           AS by_whom,
                              IA.[Remark_Verification]    AS remarks,
                              IA.[Links]                  AS links,
                              IA.[Close_Date]             AS close_date
                      FROM    [dbo].[IMPORT_AUDIT_REPORTS_EXCEL] AS IA,
                              [dbo].[CausingClause] AS CC,
                              [dbo].[Audit_Type] AS AT,                       
                              [dbo].[Audits] AS AU
                      WHERE   IA.ID           = CC.name
                      AND     IA.Audit_Type   = AT.name
                      AND     IA.Audit_No     = AU.id;
  
  OPEN db_cursor  
  FETCH NEXT FROM db_cursor INTO @audit_id, @audittype_id,
  @CausingClause_id,
  @findings,
  @Rating,
  @lr_ind,
  @root_cause,
  @corr_action,
  @by_whom,
  @remarks,
  @links, 
  @close_date
  
  WHILE @@FETCH_STATUS = 0  
  BEGIN  
      SET @category_id_insert=(SELECT id FROM [dbo].CATEGORIES AS CA WHERE CA.name = @Rating)
  
     SET @COUNT = (select count(1)  from [dbo].[AUDIT_REPORTS] as Src where 
                      Src.ID= @audit_id
              AND     Src.audittype_id        = @audittype_id
              AND     Src.CausingClause_id    = @CausingClause_id)
      --PRINT @COUNT;
  
      if @COUNT>0
      UPDATE dbo.AUDIT_REPORTS SET    dbo.AUDIT_REPORTS .[audit_id]           = @audit_id,
                  dbo.AUDIT_REPORTS .[category_id]        = (SELECT id FROM [dbo].CATEGORIES AS CA WHERE CA.name = @Rating),
                  dbo.AUDIT_REPORTS .[ref_id]         = 0,
                  dbo.AUDIT_REPORTS .[ref_ident]          = 'N/A',
                  dbo.AUDIT_REPORTS .[findings]           = @findings,
                  dbo.AUDIT_REPORTS .[corr_action]        = @corr_action,
                  dbo.AUDIT_REPORTS .[lr_ind]         = @lr_ind,
                  dbo.AUDIT_REPORTS .[by_whom]            = @by_whom,
                  dbo.AUDIT_REPORTS .[remarks]            = @remarks,
                  dbo.AUDIT_REPORTS .[due_date]           = NULL,
                  dbo.AUDIT_REPORTS .[close_date]     = @close_date,
                  dbo.AUDIT_REPORTS .[audittype_id]       = @audittype_id,
                  dbo.AUDIT_REPORTS .[root_cause]     = @root_cause,
                  dbo.AUDIT_REPORTS .[links]              = @links,
                  dbo.AUDIT_REPORTS .[CausingClause_id]   =@CausingClause_id
      Else
  
  INSERT into dbo.AUDIT_REPORTS
      (
          [audit_id],
          [category_id],
          [ref_id],
          [ref_ident],
          [findings],
          [corr_action],
          [lr_ind],
          [by_whom],
          [remarks],
          [due_date],
          [close_date],
          [audittype_id],
          [root_cause],
          [links],
          [CausingClause_id]
      )
      VALUES
      (
          @audit_id,
          --0,
          @category_id_insert,
          0,
          'N/A',
          @findings,
          @corr_action,
          @lr_ind,
          @by_whom,
          @remarks,
          NULL,
          @close_date,
          @audittype_id,
          @root_cause,
          @links,
          @CausingClause_id
      );  
  END 
  
  CLOSE db_cursor  
  DEALLOCATE db_cursor                    
  
  
  END
  GO
  

  回复于 2024-05-03T15:41:42+08:00