Variable declaration and EXEC command within a Dynamic SQL is not working

Question

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.

What's the error you are getting ? Why are declaring variables inside sampleSQL ? — user9517769
– user9517769, Commented May 9, 2020 at 7:46
I didn't get any error. but it's not working as expected. And if I declare those variables outside the dynamic SQL the entire Query becomes null, — Dhanil Dinesan
– Dhanil Dinesan, Commented May 9, 2020 at 7:47
Tips: The best practice when assembling object names into dynamic SQL statements is to use QuoteName() to avoid problems with odd names, e.g. New Table with a space or reserved words like From. And see SQL Injection. — HABO
– HABO, Commented May 9, 2020 at 14:43
Can you post the value of @MTOHeaderInsertSQL once the code is assembled? Aside: DECLARE @ApprovalStatus INT vs. IF(@ApprovalStatus=''4'') Why are you comparing an int to a string? And IF(@ApprovalStatus=''0'' OR @ApprovalStatus=''3'')? — HABO
– HABO, Commented May 9, 2020 at 14:51

Dale K · Accepted Answer · 2020-05-10 22:25:34Z

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.

Collectives™ on Stack Overflow

Variable declaration and EXEC command within a Dynamic SQL is not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related