프로시저 내부에서 다음과 같은 형식으로 실행을 시키면 에러가 나고 실행이 안되게 된다.

DECLARE @PROC NVARCHAR(MAX)
set @t_str = REPLACE(@t_str,'''','''''')
Set @PROC = N'EXEC ['+@vLinkedServerName+'].['+@TDatabaseName+'].dbo.up_DW2_SM_SpecialDeploy 
                        '''+@ObjectName+''', '''+@t_str+''', '''+@ObjectType+''''

EXEC @PROC        <==  이 부분이 문제

<Solution>
내부 프로시저를 실행시킬 때 위 부분을 EXEC (@PROC) 로 실행하면 된다.
 
<WHY>
EXECUTE를 실행할 때 다음과 같이 실행문을 실행할 때는 괄호를 붙이지 않는다.
SET @PROC = 'up_DW2_DPM_SendMailDeployResult_02 @SInstanceName, @SDatabaseName, @ObjectName'
EXEC @PROC
하지만 위와 같이 동적쿼리를 사용하든가 하는 경우에는 스트링이 넘어가기 때문에 EXEC (@PROC)와 같이 괄호를 붙여서 실행해야 에러가 나지 않는다.

CREATE PROC [dbo].[up_DW2_DPM_DeployDtl_R01]
 @DeployID INT,
 @LandScapeName VARCHAR(50)
 
as
 
BEGIN
 
 DECLARE @RESULT BIT = 1
 
 DECLARE vCursorPtn CURSOR FAST_FORWARD
 FOR SELECT ObjectName, [Version]
          FROM DeployDtl
          WHERE DeployID = @DeployID AND LandScapeName = @LandScapeName
          ORDER BY [Version] ASC

 
 OPEN vCursorPtn
 
 DECLARE @vObjectName VARCHAR(50)
 DECLARE @vVersion INT
 
 FETCH NEXT FROM vCursorPtn INTO @vObjectName, @vVersion
 
 WHILE @@FETCH_STATUS = 0
 BEGIN
     
  IF EXISTS (SELECT ObjectName
                  FROM DeployDtl
                  WHERE LandScapeName = @LandScapeName
                             AND ObjectName = @vObjectName AND @vVersion > [Version])

BEGIN
  
   IF EXISTS (SELECT a.DeployID
                   FROM DeployLog AS a
                            JOIN (SELECT ObjectName, MAX([Version]) AS [Version], DeployID, LandScapeName
                                     FROM DeployDtl
                                     WHERE LandScapeName = @LandScapeName AND ObjectName = @vObjectName
                                     GROUP BY ObjectName, DeployID, LandScapeName
                                     HAVING @vVersion > MAX([Version])) AS b
                                     ON a.DeployID = b.DeployID AND a.LandScapeName = b.LandScapeName)
   
   BEGIN
       SET @RESULT =1
   END
    
   ELSE
   BEGIN
       SET @RESULT = 0
   END
   
  END
  
  ELSE 
  BEGIN
      SET @RESULT = 0
  END
  
  FETCH NEXT FROM vCursorPtn INTO @vObjectName, @vVersion 
 END
 
 CLOSE vCursorPtn
 DEALLOCATE vCursorPtn
 
 SELECT @DeployID AS DeployID, @RESULT AS Result
 
END

SELECT so.name FROM dbo.sysobjects so,dbo.syscomments sc
WHERE so.id=sc.id AND type = 'P' 

AND sc.text LIKE '%Search_Text%' ORDER BY so.name