Incorrect syntax near ')'. in sqlserver 2008
===

    
i have create Sp that return record on condition. it is work on my local server but when i deploy on another server im getting error

> 153|error|500|Incorrect syntax near '*'. Incorrect syntax near ')'.
> Incorrect syntax near ')'. Unexpected error occured while retrieving
> data.Please try again later|

This is my query:-

    
     
         DECLARE @MemberID int  
        DECLARE @FolderID INT -- 1 = Inbox, 2 = Draft, 3 = Sent Items, 4 = Deleted Items, 5 = Templates etc    
         DECLARE @Email varchar(50)  
    	DECLARE @OrderBy VARCHAR(50)
    	DECLARE @PageSize INT  
    	DECLARE @PageNumber INT  
    	DECLARE @EnvelopeCurrentStatus varchar(5)  
    	DECLARE @Subject varchar(50)   
    	DECLARE @DateFrom VARCHAR(15)
    	DECLARE @DateTo VARCHAR(15) 
     
     
     
     
    	SET @MemberID =35185  
    	SET @FolderID =1 -- 1 = Inbox, 2 = Draft, 3 = Sent Items, 4 = Deleted Items, 5 = Templates etc    
    	SET @Email ='sd' 
    	SET @OrderBy = 'ti.ID DESC'  
    	SET @PageSize =10  
    	SET @PageNumber =1  
    	SET @EnvelopeCurrentStatus  = '' 
    	SET @Subject  = ''  
    	SET @DateFrom  = '1/1/1990'   
    	SET @DateTo  = '1/1/2990'
    --,@DocGuid uniqueidentifier='00000000-0000-0000-0000-000000000000 '
    
        DECLARE @SQL VARCHAR(MAX)  
        DECLARE @SQL1 VARCHAR(MAX)  
        DECLARE @SQL2 VARCHAR(MAX)  
        DECLARE @SQL3 varchar(Max)  
        DECLARE @Select varchar(50)  
        DECLARE @Expr varchar(max)  
        SET @SQL = ''  
        SET @OrderBy = ''  
        
        SET @Expr = ''  
        IF ((@DateFrom <> '' AND @DateFrom IS NOT NULL) AND (@DateTo <> '' AND @DateTo IS NOT NULL ) )  
               SET @Expr = @Expr + ' AND ti.CreatedOn BETWEEN ''' + @DateFrom + ''' AND ''' + @DateTo+''''
        
        IF @Subject <> '' AND @Subject IS NOT NULL 
         begin  
          BEGIN TRY
          DECLARE @nPortalUID AS uniqueidentifier
          SET @nPortalUID = CAST(@Subject AS uniqueidentifier)
          SET @Expr = @Expr + ' AND  ti.TemplateID= '''+@Subject+' '''
          end try
        
          BEGIN catch
          SET @Expr = @Expr + ' and ti.Subject LIKE ''%'+@Subject+'%'' '
          end catch
         end     
       
         
              
        
        IF @EnvelopeCurrentStatus <> '' AND @EnvelopeCurrentStatus IS NOT NULL  
                SET @Expr = @Expr + ' AND ti.EnvelopeCurrentStatus = ''' + cast( @EnvelopeCurrentStatus AS varchar(5))+ ''''
            
    	IF @OrderBy ='' AND @OrderBy IS NOT NULL   
                SET @OrderBy = 'ti.ID DESC'
        ELSE   
                SET @OrderBy = @OrderBy  
    
    	DECLARE @IsShare BIT
    	DECLARE @ParentID INT
    	SELECT @IsShare = m.MUIsShare, @ParentID = m.createdby FROM tblcontact m WHERE m.ID = @MemberID
    	
    	DECLARE @MemberParentID VARCHAR(MAX)
    	
    	IF @IsShare = 1 
    	BEGIN
    	SET @MemberParentID = cast(@MemberID AS varchar(5))
    		--SET @MemberParentID = cast(@MemberID AS varchar(5))+ ', ' + cast(@ParentID AS varchar(5))
    	END
    	ELSE
    	BEGIN
    		SET @MemberParentID = cast(@MemberID AS varchar(5))
    	END
    
    	SET @SQL1 = 'SELECT * INTO #TMP FROM (  
    					SELECT * FROM (  
                    		SELECT (  
    							SELECT Count(*) FROM TemplateInfo ti  WHERE (ti.FolderID = '+ cast( @FolderID AS varchar(5) )+ ') AND (ti.MemberID IN( '+ @MemberParentID + '))  AND (ti.EnvelopeStatus = 2) '  
        
        SET @SQL2 = ' OR  (ti.ID IN (SELECT TempleteInfoID  FROM  Recipients AS r  WHERE (Email = '''+ @Email+''')  AND (FolderID = '+ cast( @FolderID AS varchar(5))+') ) ) ) AS TotalRecord  
                    , ROW_NUMBER() OVER(ORDER BY '+ @OrderBy + ') AS ROWID , ti.ID  , ti.TemplateID  , ti.EnvelopeStatus  , ti.EnvelopeCurrentStatus  , ti.Subject  , ti.CreatedOn AS SentDate  
                    , ti.CreatedOn  , ti.ModifiedOn  , m.FirstName + '' '' + m.LastName AS Sender  , m.Email AS SenderEmail  
                    FROM TemplateInfo ti  LEFT OUTER JOIN tblcontact AS m ON ti.MemberID = m.ID  
                    WHERE (ti.FolderID = '+ cast( @FolderID AS varchar(5))+ ') AND (ti.MemberID IN( '+ @MemberParentID + '))  AND (ti.EnvelopeStatus = 2) '  
    
        SET @SQL3 = ' OR (ti.ID IN ( SELECT TempleteInfoID   FROM  Recipients AS r  WHERE (Email = '''+ @Email+''')  AND (FolderID = '+ cast( @FolderID AS varchar(5)) + ') ) ) ) AS Template  
    					WHERE ROWID BETWEEN  '+ CAST( (@PageNumber - 1) * @PageSize + 1 AS VARCHAR(5)) +' AND '+CAST (@PageNumber*@PageSize AS  VARCHAR(5)) + ' ) AS TmpTable  
    					
    				SELECT * FROM #TMP  
                    
                    SELECT r.ID  , r.Guid  , r.Name  , r.Email  , r.RecipientOrder  , r.Status  , r.ModifiedOn  , r.TempleteInfoID  
                    , CASE WHEN r.Status = 0 THEN ''Waiting to Sign''  WHEN r.Status = 1 THEN ''Completed''  WHEN r.Status = 2 THEN ''Declined'' END AS StatusText  
                    FROM Recipients r WHERE r.IsActive <> 0 and r.TempleteInfoID IN (SELECT ID FROM  #TMP)  
                    
                    SELECT * FROM UsedCustomFields ucf WHERE ucf.TemplateInfoID IN (SELECT ID FROM  #TMP)  
                    
                    DROP TABLE #TMP '  
                    
        SET @SQL = @SQL1 + @Expr + @SQL2 +@Expr + @SQL3  
        select @SQL
        --PRINT (@SQL)  
        EXEC (@SQL);  
        
        IF @@Error <> 0
        BEGIN
            RAISERROR('Unexpected error occured while retrieving data.Please try again later',16 , 1)
        END


