I wrote some dynamic SQL and after executing it, it's not working as expected. What I am doing here is I have declared some variables inside the dynamic SQL and I have assigned some values to that variables and then I am calling another SP inside the dynamic SQL using the EXEC command.
Here is the sample code I have written
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')
And the interesting thing is that it's working when it's not a dynamic query. Is there any issue with the EXEC command using inside a dynamic query?
Here is the entire query
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')
I think this will also help to figure out how the query look like.
So this doesn't address the issue of why your dynamic SQL isn't working. My guess is that its a scoping issue, but your dynamic SQL then executing a stored procedure reminds me of the movie "Inception" - a dream within a dream.
My suggestion, based on the code I can see, is only make the necessary part dynamic, which is the tablename. The rest can be static e.g.
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;
Hopefully that concept can be carried through to your actual SQL.
Note: As a general principle use dynamic SQL sparingly and only when necessary. So in your case you only need dynamic SQL for reading from @TableName - so if you read all the information you need from @TableName using dynamic SQL at the start of your code block, then you should then be able to use static SQL for the rest. Which will simply things a lot and make it easier to develop/debug.