title: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'
body: <p>Here is my <strong>query based sp:</strong></p>
  
  <pre><code>DECLARE @GLOBALPATIENTACCOUNT VARCHAR(25)
  DECLARE @Page_Index BIGINT
  DECLARE @Page_Size BIGINT
  DECLARE @practice_code VARCHAR(20)
  DECLARE @dateFrom VARCHAR(20)
  DECLARE @dateTo VARCHAR(20)
  DECLARE @startFrom VARCHAR(20)
  DECLARE @startTo VARCHAR(20)
  DECLARE @Total_Record DECIMAL(34, 1)
  DECLARE @Total_Pages DECIMAL(34, 1)
  DECLARE @From_row BIGINT
  DECLARE @To_row BIGINT
  
  SET @GLOBALPATIENTACCOUNT = '999090999510103196'
  SET @Page_Index = 1
  SET @Page_Size = 30
  SET @practice_code = 9090999
  SET @dateFrom = '09/13/2014'
  SET @dateTo = '10/13/2014'
  SET @startFrom = 'null'
  SET @startTo = 'null'
  
  DECLARE @sqlstr AS  varchar (max)
  set @sqlstr='';
  SET @sqlstr =@sqlstr +N'select CREATED_DTAE, DOCUMENT_CATEGORY_ID, DESCRIPTION, DOCUMENT_NAME, SHOW_ON_WEB, VIEW_BY_PATIENT, VIEW_DATE, NO_OF_IMAGES, DOCUMENT_STATUS_ID, DOCUMENT_STATUS_DESCRIPTION, 
                          PATIENT_DOCUMENT_ID,ISDICOM,PATIENT_ACCOUNT,DOCUMENT_INDEX, ASSIGNED_TO,        
                          CONTENT_START_DATE, COMMENTS,CHART_ID,LAST_NAME,FIRST_NAME, CONTENT_END_DATE,CREATED_BY,[CREATED DATE], MODIFIED_BY,MODIFIED_DATE,DELETED,SOURCE_PATH,CONFIDENTIAL, 
                          DOC_UPLOAD_NAME,DOC_UPLOAD_STATUS,IS_MISC_DOC,BUTTON,SIGNED,SIGNED_BY,SIGNED_DATE,  PRACTICE_CODE          
                  into #temptbl         
                  FROM        
                  (
                      SELECT ISNULL(CREATED_DTAE,'''')as CREATED_DTAE ,  PATIENT_DOCUMENTS.DOCUMENT_CATEGORY_ID,   ISNULL(UPPER(DT.DESCRIPTION),'''')as DESCRIPTION, 
                      ISNULL(PATIENT_DOCUMENTS.DOCUMENT_NAME,'''') as DOCUMENT_NAME,
                      ISNULL(PATIENT_DOCUMENTS.SHOW_ON_WEB,''0'') AS SHOW_ON_WEB,  ISNULL(PATIENT_DOCUMENTS.VIEW_BY_PATIENT,''0'') AS VIEW_BY_PATIENT,                           
                      ISNULL(CONVERT(VARCHAR(10),PATIENT_DOCUMENTS.VIEW_DATE,101),'''') AS VIEW_DATE,  ISNULL(PATIENT_DOCUMENTS.NO_OF_IMAGES,''0'') AS NO_OF_IMAGES,                   
                      ISNULL((CONVERT(VARCHAR(12), CASE PATIENT_DOCUMENTS.DOCUMENT_STATUS_ID  WHEN 0 THEN NULL    ELSE PATIENT_DOCUMENTS.DOCUMENT_STATUS_ID END )),'''') AS DOCUMENT_STATUS_ID,                            
                      ISNULL(UPPER(DOCUMENTS_STATUS.DOCUMENT_STATUS_DESCRIPTION),'''')  DOCUMENT_STATUS_DESCRIPTION ,  PATIENT_DOCUMENTS.PATIENT_DOCUMENT_ID,                            
                      ISNULL(PATIENT_DOCUMENTS.ISDICOM,''0'') ISDICOM, PATIENT_DOCUMENTS.PATIENT_ACCOUNT, ISNULL(PATIENT_DOCUMENTS.DOCUMENT_INDEX,0) DOCUMENT_INDEX, ISNULL(PATIENT_DOCUMENTS.ASSIGNED_TO,'''') ASSIGNED_TO ,                            
                      CONVERT(VARCHAR,PATIENT_DOCUMENTS.CONTENT_START_DATE,101) CONTENT_START_DATE, REPLACE (PATIENT_DOCUMENTS.COMMENTS,CHAR(10),'' '') AS COMMENTS, PATIENT.CHART_ID,  UPPER(PATIENT.LAST_NAME)LAST_NAME,UPPER(PATIENT.FIRST_NAME) FIRST_NAME ,           
                      CONVERT(VARCHAR,PATIENT_DOCUMENTS.CONTENT_END_DATE,101) CONTENT_END_DATE,  ISNULL(UPPER(PATIENT_DOCUMENTS.CREATED_BY),'''') CREATED_BY,                              
                      CONVERT(VARCHAR,CREATED_DTAE,101) +'' ''+  LTRIM(SUBSTRING(CONVERT(VARCHAR,(CONVERT(DATETIME,CREATED_DTAE)),109),13,5))+'' ''+  SUBSTRING(CONVERT(VARCHAR,(CONVERT(DATETIME,CREATED_DTAE)),109),25,2) AS [CREATED DATE],                            
                      ISNULL(UPPER(PATIENT_DOCUMENTS.MODIFIED_BY),'''') MODIFIED_BY,                            
                      ISNULL(CONVERT(VARCHAR,PATIENT_DOCUMENTS.MODIFIED_DATE,101),'''') +'' ''+  LTRIM(SUBSTRING(CONVERT(VARCHAR,(CONVERT(DATETIME,PATIENT_DOCUMENTS.MODIFIED_DATE)),109),13,5))+'' ''+  SUBSTRING(CONVERT(VARCHAR,(CONVERT(DATETIME,PATIENT_DOCUMENTS.MODIFIED_DATE)),109),25 ,2) AS MODIFIED_DATE,PATIENT_DOCUMENTS.DELETED,ISNULL(PATIENT_DOCUMENTS.SOURCE_PATH,'''') SOURCE_PATH,ISNULL(PATIENT_DOCUMENTS.CONFIDENTIAL,0) CONFIDENTIAL,ISNULL(PATIENT_DOCUMENTS.DOC_UPLOAD_NAME,'''') DOC_UPLOAD_NAME,                            
                      ISNULL(PATIENT_DOCUMENTS.DOC_UPLOAD_STATUS,'''') DOC_UPLOAD_STATUS,CONVERT(BIT,''FALSE'')  IS_MISC_DOC,'''' AS BUTTON,         
                      PATIENT_DOCUMENTS.Signed SIGNED, PATIENT_DOCUMENTS.Sign_by SIGNED_BY, PATIENT_DOCUMENTS.Sign_date  SIGNED_DATE,
                      PATIENT.PRACTICE_CODE AS PRACTICE_CODE 
                      FROM PATIENT, PATIENT_DOCUMENTS 
                      LEFT OUTER JOIN DOCUMENTS_STATUS  ON PATIENT_DOCUMENTS.DOCUMENT_STATUS_ID = DOCUMENTS_STATUS.DOCUMENT_STATUS_ID,
                      DOCUMENT_CATEGORIES DT  
                      WHERE PATIENT_DOCUMENTS.DOCUMENT_CATEGORY_ID = DT.DOCUMENT_CATEGORY_ID 
                      AND PATIENT_DOCUMENTS.PATIENT_ACCOUNT = PATIENT.PATIENT_ACCOUNT AND PATIENT.Patient_GlobalId= ''' + @GLOBALPATIENTACCOUNT +'''   
                      AND ISNULL(PATIENT.DELETED,0) &lt;&gt; 1                              
                      AND ISNULL(PATIENT_DOCUMENTS.DELETED, 0) &lt;&gt; 1
                      AND  PATIENT.PRACTICE_CODE &lt;&gt; ''' + @practice_code + '''
                      AND PATIENT_DOCUMENTS.confidential &lt;&gt; 1 '
                      --print @sqlstr
  
          IF (@dateFrom &lt;&gt; 'null')        
  BEGIN
      SET @sqlstr = @sqlstr + ' and CONVERT(VARCHAR,ISNULL(PATIENT_DOCUMENTS.CREATED_DTAE,''''),101)  &gt;= CONVERT(VARCHAR,isnull( ''' + @dateFrom + ''',''''),101) and CONVERT(VARCHAR,ISNULL(PATIENT_DOCUMENTS.CREATED_DTAE,''''),101) &lt;= CONVERT(VARCHAR,ISNULL(''' + @dateTo + ''',''''),101)'
  --print @sqlstr
  END
  
  IF(@startFrom &lt;&gt; 'null')        
  BEGIN
      SET @sqlstr = @sqlstr + '  CONVERT(VARCHAR,ISNULL(PATIENT_DOCUMENTS.CONTENT_START_DATE,''''),101) &gt;= CONVERT(VARCHAR,ISNULL(''' + @startFrom + ''',''''),101) and CONVERT(VARCHAR,ISNULL(PATIENT_DOCUMENTS.CONTENT_START_DATE,''''),101) &lt;= CONVERT(VARCHAR,isnull(''' + @startTo + ''',''''),101)'
  END
  
  SET @sqlstr = @sqlstr + 'UNION ALL                            
  
   SELECT  ISNULL(DOCUMENTS.CREATED_DATE,'''') AS CREATED_DTAE,  CATAGORY_TYPE_ID AS DOCUMENT_CATEGORY_ID, ISNULL(DOCUMENT_CATEGORIES.DESCRIPTION,'''') DESCRIPTION,                  
  
   (SELECT TOP 1 DOCUMENTS_CATEGORY_MAIN_PATH FROM DOCUMENTS_CATEGORY_MAIN    WHERE DOCUMENTS_CATEGORY_MAIN_DESCRIPTION=''ATTACHMENTS'' )+''\'' +DOCUMENTS.FILE_NAME_FORPATH AS [DOCUMENT_NAME],  isnull(DOCUMENTS.SHOW_ON_WEB,0) AS SHOW_ON_WEB,''0'' AS VIEW_BY_PATIENT  
  
   ,ISNULL(NULL,'''') AS VIEW_DATE,    ''0'' AS NO_OF_IMAGES,ISNULL(DOCUMENTS.DOCUMENT_STATUS_ID,'''')  DOCUMENT_STATUS_ID,ISNULL(DOCUMENTS_STATUS.DOCUMENT_STATUS_DESCRIPTION,'''') DOCUMENT_STATUS_DESCRIPTION,  DOC_ID AS PATIENT_DOCUMENT_ID,                        
  
   ISNULL(NULL,''0'') AS ISDICOM, DOCUMENTS.PATIENT_ACCOUNT, ISNULL(NULL,0) AS DOCUMENT_INDEX,ISNULL(DOCUMENTS.ASSIGNED_TO,'''') ASSIGNED_TO,  CONVERT(VARCHAR,DOCUMENTS.CONTENT_START_DATE,101) AS CONTENT_START_DATE, 
   ISNULL(DOCUMENTS.NOTES,'''') AS COMMENTS, '''' AS CHART_ID,  UPPER(PM.LAST_NAME)LAST_NAME,UPPER(PM.FIRST_NAME) FIRST_NAME,                            
   ISNULL(CONVERT(VARCHAR,DOCUMENTS.CONTENT_END_DATE,101),'''') AS CONTENT_END_DATE,  ISNULL(DOCUMENTS.CREATED_BY,'''') CREATED_BY,   ISNULL(DOCUMENTS.CREATED_DATE,'''') AS [CREATED DATE],                  
   ISNULL(DOCUMENTS.MODIFIED_BY,'''') MODIFIED_BY, ISNULL(DOCUMENTS.MODIFIED_DATE,'''') MODIFIED_DATE, DOCUMENTS.DELETED,  '''' AS SOURCE_PATH,  ISNULL(DOCUMENTS.CONFIDENTIAL,0) CONFIDENTIAL,  '''' AS DOC_UPLOAD_NAME, '''' AS DOC_UPLOAD_STATUS,                       
   CONVERT(BIT,''TRUE'')  IS_MISC_DOC,'''' AS BUTTON , DOCUMENTS.SIGNED AS SIGNED, DOCUMENTS.SIGN_BY AS SIGNED_BY, DOCUMENTS.SIGN_DATE AS  SIGNED_DATE,  
   PM.PRACTICE_CODE AS PRACTICE_CODE 
   FROM PATIENT_DOCUMENTS_OTHERS DOCUMENTS                            
   LEFT OUTER JOIN DOCUMENT_CATEGORIES ON DOCUMENTS.CATAGORY_TYPE_ID=DOCUMENT_CATEGORIES.DOCUMENT_CATEGORY_ID                            
   INNER JOIN DOCUMENTS_CATEGORY_MAIN DCM ON  DOCUMENTS.DOCUMENTS_CATEGORY_MAIN_ID =DCM.DOCUMENTS_CATEGORY_MAIN_ID                            
   INNER JOIN PATIENT PM ON PM.PATIENT_ACCOUNT=DOCUMENTS.PATIENT_ACCOUNT                            
   LEFT OUTER JOIN DOCUMENTS_STATUS ON DOCUMENTS_STATUS.DOCUMENT_STATUS_ID=DOCUMENTS.DOCUMENT_STATUS_ID 
   left outer join PATIENT_DOCUMENTS on DOCUMENTS.Doc_Id=PATIENT_DOCUMENTS.PATIENT_DOCUMENT_ID                           
   WHERE ISNULL(DOCUMENTS.DELETED,0)&lt;&gt;1                            
  
   AND PM.Patient_GlobalId='''+ @GLOBALPATIENTACCOUNT + '''  AND PM.PRACTICE_CODE&lt;&gt;''' + @practice_code + '''    and DOCUMENTS.confidential&lt;&gt;1'
  
  IF(@dateFrom &lt;&gt; 'null')
  
  
  BEGIN
      SET @sqlstr = @sqlstr + 'and ISNULL(CONVERT(VARCHAR,PATIENT_DOCUMENTS.CREATED_DTAE,101),'''')  &gt;= ISNULL(CONVERT(VARCHAR,''' + @dateFrom + ''',101),'''') and ISNULL(CONVERT(VARCHAR,PATIENT_DOCUMENTS.CREATED_DTAE,101),'''')&lt;=ISNULL(CONVERT(VARCHAR,''' + @dateTo + ''',101),'''')'
  END
  
  IF (@startFrom &lt;&gt; 'null')
  BEGIN
      SET @sqlstr = @sqlstr + 'CONVERT(VARCHAR,PATIENT_DOCUMENTS.CONTENT_START_DATE,101) &gt;= CONVERT(VARCHAR,''' + @startFrom + ''',101) and CONVERT(VARCHAR,PATIENT_DOCUMENTS.CONTENT_START_DATE,101)&lt;=CONVERT(VARCHAR,''' + @startTo + ''',101)'
  END
  
  SET @sqlstr = @sqlstr + ') PatDocTable order by PRACTICE_CODE,CREATED_DTAE desc '
  
  EXECUTE sp_executesql @statement = @sqlstr       
          PRINT @sqlstr  
  </code></pre>
  
  <p>It gives me error <strong>Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'</strong>,but when i change datatype <strong>@sqlstr</strong> AS  <strong>nvarchar (max)</strong>,error omitted,but it trancuate my query.Kindly help me to figure it out?</p>