Thursday, March 17, 2011

Executing the dynamic SQL using sp_executesql

  SET @SqlParam =  N'@UsrId BIGINT,@LMBy NVARCHAR(50),@SId BIGINT,@DocId BIGINT, @ProcessId BIGINT, @RoleId BIGINT'   
    
           
  SET @Sql = N'UPDATE WFTransaction SET  UserId= @UsrId,     
  endDate=getdate(),    
  LMBy=@LMBy,    
  LMDt=getdate()    
        FROM WFTransaction AS wft    
        INNER JOIN    
        TTask AS tt    
        ON    
        wft.task_id =tt.tsk_Id            
  WHERE    
  wft.SId=@SId AND    
  wft.Doc_Id=@DocId AND     
  wft.Process_Id=@ProcessId AND     
  UPPER(tt.TSKName) IN('+@TaskName+')'   
   
     IF(UPPER(@RCode) <> 'PU')   
    SET @Sql = @Sql + ' AND wft.[Role]=@RoleId'    
    
  EXECUTE sp_executesql @Sql,@SqlParam,@UsrId=@UsrId,@LMBy=@LMBy,@SId=@SId,@DocId=@DocId,@ProcessId=@ProcessId,@RoleId=@RoleId  
   


No comments:

Post a Comment