时间戳

首页 > 解决方案 > 动态 SQL 中的变量声明和 EXEC 命令不起作用

sql - 动态 SQL 中的变量声明和 EXEC 命令不起作用

问题描述

我写了一些动态 SQL，执行后，它没有按预期工作。我在这里所做的是我在动态 SQL 中声明了一些变量，并为这些变量分配了一些值，然后我使用 EXEC 命令在动态 SQL 中调用另一个 SP。

这是我编写的示例代码

declare 
@TableName VARCHAR(250)='[PATS].Z_MTOReferenceDocument_307CAB4B_CC52_4BBA_8C3E_1481E1447028',
@LoginName VARCHAR(250)='dHANIL',
@Date DATETIME='8-May-2020',
@ProjectID UNIQUEIDENTIFIER='e50e25a7-3d8e-4d1d-b401-942e51ab5f7f',
@DocumentOwnerID UNIQUEIDENTIFIER='fc938df0-8a4e-4c85-b93c-be51373c559f'

declare @sampleSQL nvarchar(max)=''

set @sampleSQL=(' DECLARE @ApprovalStatus INT=NULL,'
                +' @DocumentHeaderID UNIQUEIDENTIFIER,'             
                +' @Status INT'


                +' SELECT @ApprovalStatus=ApprovalStatusCode,@DocumentHeaderID=DH.ID FROM '+@TableName+' TT'
                +' LEFT JOIN [PI].[DocumentHeader] DH ON DH.[DocumentNo]=TT.[Document No] WHERE LatestRevYN=''1'''          
            
                +' IF(@ApprovalStatus=''4'')'
                +' BEGIN'
                    +' EXEC [PI].[ReviseDocument] @DocumentHeaderID,'''+@LoginName+''','''+CAST(@Date AS NVARCHAR(100))+''',''Document revised through Import'',@Status OUTPUT,@ID OUTPUT,@DocumentID OUTPUT'
                +' END')

有趣的是，当它不是动态查询时，它可以工作。在动态查询中使用EXEC命令有什么问题吗？

这是整个查询

  SET @MTOHeaderInsertSQL= ('DECLARE @ID UNIQUEIDENTIFIER ,' --document header id
            +' @DocumentID UNIQUEIDENTIFIER' --MTO document id

            +' IF NOT EXISTS(SET DATEFORMAT dmy;SELECT DISTINCT DH.[DocumentNo] FROM '+@TableName+' TT'
            +' LEFT JOIN [PI].[DocumentHeader] DH ON DH.[DocumentNo]=TT.['+@DocumentNo+'])'
            +' BEGIN'
                +' DECLARE @DocumentNoGnerated VARCHAR(50)'
                --For generating document no
                +' SELECT @DocumentNoGnerated=DocumentNo FROM [PI].[GetNewDocumentNo] ('''+CAST(@ProjectID AS NVARCHAR(100))+''',''MTO'')'
                
                +' SET @ID=NEWID()
                SET @DocumentID=NEWID()'

                --inserting to the document header
                +' INSERT INTO [PI].[DocumentHeader] (ID,ProjectID,DocumentID,DocumentTypeCode,DocumentNo,DocumentRevNo'
                +' ,DocumentDate,DocumentTitle,ClientRefNo,ApprovalStatusCode,LatestApprovalLogID,LatestRevYN'
                +' ,FinalApprovalDate,LatestApprovedDocYN,CancelledYN,CreatedBy,CreatedDate,UpdatedBy,UpdatedDate'
                +' ,DocumentOwnerID,RevisionDate)'--,AreaID,SubAreaID,LocationID)
                +' SELECT @ID,'''+CAST(@ProjectID AS NVARCHAR(100))+''',@DocumentID,''MTO'',@DocumentNoGnerated,''0'''
                +' ,'''+CAST(@Date AS NVARCHAR(100))+''',''Take off document'',NULL,''0'',NULL,''1''' 
                +' ,NULL,''0'',''0'','''+@LoginName+''','''+CAST(@Date AS NVARCHAR(100))+''',NULL,NULL'
                +' ,'''+CAST(@DocumentOwnerID AS NVARCHAR(100))+''','''+CAST(@Date AS NVARCHAR(100))+''''--,ArealID,SubAreaID,LocationID
                +' FROM '+@TableName+''

                --Inserting to MTO Header table
                +' INSERT INTO [PI].[MTOHeader](ID,DocumentHeaderID,ProjectID,MTOType'
                +' ,CreatedBy,CreatedDate,UpdatedBy,UpdatedDate)'
                +' VALUES(@DocumentID,@ID,'''+CAST(@ProjectID AS NVARCHAR(100))+''',''Post Award'''
                +' ,'''+@LoginName+''','''+CAST(@Date AS NVARCHAR(100))+''',NULL,NULL)'

                --Inserting into General Log
                +' INSERT INTO [PI].[GeneralLog] (ID,ProjectID,DocumentTypeCode,LogType'
                +' ,Detail,CreatedBy,CreatedDate,UpdatedBy,UpdatedDate,ReferenceNo,ReferenceID)'
                +' VALUES'
                +' (NEWID(),'''+CAST(@ProjectID AS NVARCHAR(100))+''',''MTO'',''Import'''
                +' ,''MTO Import'','''+@LoginName+''','''+CAST(@Date AS NVARCHAR(100))+''',NULL,NULL,@DocumentNoGnerated,@ID)'
                    
            +' END'
            +' ELSE'
            +' BEGIN'
            
                +' DECLARE @ApprovalStatus INT=NULL,'
                +' @DocumentHeaderID UNIQUEIDENTIFIER,'             
                +' @Status INT'


                +' SELECT @ApprovalStatus=ApprovalStatusCode,@DocumentHeaderID=DH.ID FROM '+@TableName+' TT'
                +' LEFT JOIN [PI].[DocumentHeader] DH ON DH.[DocumentNo]=TT.['+@DocumentNo+'] WHERE LatestRevYN=''1'''          
            
                +' IF(@ApprovalStatus=''4'')'
                +' BEGIN'
                    +' EXEC [PI].[ReviseDocument] @DocumentHeaderID,'''+@LoginName+''','''+CAST(@Date AS NVARCHAR(100))+''',''Document revised through Import'',@Status OUTPUT,@ID OUTPUT,@DocumentID OUTPUT'
                +' END'

                --If document is in Draft or Rejected mode, it will update the latest document using the latest rev flag
                +' IF(@ApprovalStatus=''0'' OR @ApprovalStatus=''3'')'
                +' BEGIN'   
                    +' UPDATE A SET A.DocumentOwnerID='''+CAST(@DocumentOwnerID AS NVARCHAR(100))+''''  
                    +' ,A.UpdatedBy='''+@LoginName+''',A.UpdatedDate='''+CAST(@Date AS NVARCHAR(100))+''''          
                    +' FROM'
                    +' (SELECT ID,ProjectID,DocumentOwnerID,UpdatedBy,UpdatedDate FROM [PI].[DocumentHeader]) A'
                    +' INNER JOIN' 
                    +' (SELECT DH.ID,DH.ProjectID,DH.DocumentOwnerID,DH.UpdatedBy,DH.UpdatedDate FROM '+@TableName+' TT'
                    +' LEFT JOIN [PI].[DocumentHeader] DH ON DH.[DocumentNo]=TT.['+@DocumentNo+'] AND LatestRevYN=''1'''
                    +' WHERE TT.IsError=0) B ON B.ID=A.ID'

                    +' INSERT INTO [PI].[GeneralLog] (ID,ProjectID,DocumentTypeCode,LogType'
                    +' ,Detail,CreatedBy,CreatedDate,UpdatedBy,UpdatedDate,ReferenceNo,ReferenceID)'
                    +' SELECT NEWID(),'''+CAST(@ProjectID AS NVARCHAR(100))+''',''MTO'',''Import'''
                    +' ,''Ownership changed through import'','''+@LoginName+''','''+CAST(@Date AS NVARCHAR(100))+''',NULL,NULL,DH.DocumentNo,DH.ID'
                    +' FROM '+@TableName+' TT'
                    +' LEFT JOIN [PI].[DocumentHeader] DH ON DH.[DocumentNo]=TT.['+@DocumentNo+'] AND LatestRevYN=''1'''                    
                +' END'
            +' END')

我认为这也将有助于弄清楚查询的样子。

标签： sqlsql-servertsqldynamic-sql

解决方案

所以这并不能解决为什么你的动态 SQL 不起作用的问题。我的猜测是这是一个范围界定问题，但是您的动态 SQL 然后执行存储过程让我想起了电影“盗梦空​​间”——梦中之梦。

根据我看到的代码，我的建议是只使必要的部分动态化，即表名。其余的可以是静态的，例如

DECLARE @TableName VARCHAR(250) = '[PATS].Z_MTOReferenceDocument_307CAB4B_CC52_4BBA_8C3E_1481E1447028'
  , @LoginName VARCHAR(250) = 'dHANIL'
  , @Date DATETIME = '8-May-2020'
  , @ProjectID UNIQUEIDENTIFIER = 'e50e25a7-3d8e-4d1d-b401-942e51ab5f7f'
  , @DocumentOwnerID UNIQUEIDENTIFIER = 'fc938df0-8a4e-4c85-b93c-be51373c559f';

DECLARE @SampleSQL NVARCHAR(MAX) = '';

DECLARE @ApprovalStatus INT = NULL, @DocumentHeaderID UNIQUEIDENTIFIER, @Status INT;

SET @SampleSQL = ' SELECT @ApprovalStatus = ApprovalStatusCode, @DocumentHeaderID = DH.ID FROM ' + @TableName + ' TT'
  + ' LEFT JOIN [PI].[DocumentHeader] DH ON DH.[DocumentNo] = TT.[Document No] WHERE LatestRevYN = ''1''';

EXEC SP_EXECUTESQL @SampleSQL, N'@ApprovalStatus INT OUTPUT, @DocumentHeaderID UNIQUEIDENTIFIER OUTPUT', @ApprovalStatus = @ApprovalStatus OUTPUT, @DocumentHeaderID = @DocumentHeaderID OUTPUT;

IF @ApprovalStatus = 4
BEGIN
  EXEC [PI].[ReviseDocument] @DocumentHeaderID, @LoginName, @Date, 'Document revised through Import', @Status OUTPUT, @ID OUTPUT, @DocumentID OUTPUT;
END;

希望这个概念可以贯彻到您的实际 SQL 中。

注意：作为一般原则，仅在必要时谨慎使用动态 SQL。因此，在您的情况下，您只需要动态 SQL 来读取@TableName- 因此，如果您@TableName在代码块的开头使用动态 SQL 读取了所需的所有信息，那么您应该能够在其余部分使用静态 SQL。这将简化很多事情并使开发/调试变得更容易。

推荐阅读