Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

Question

Here is my query based sp:

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) <> 1                              
                    AND ISNULL(PATIENT_DOCUMENTS.DELETED, 0) <> 1
                    AND  PATIENT.PRACTICE_CODE <> ''' + @practice_code + '''
                    AND PATIENT_DOCUMENTS.confidential <> 1 '
                    --print @sqlstr

        IF (@dateFrom <> 'null')        
BEGIN
    SET @sqlstr = @sqlstr + ' and CONVERT(VARCHAR,ISNULL(PATIENT_DOCUMENTS.CREATED_DTAE,''''),101)  >= CONVERT(VARCHAR,isnull( ''' + @dateFrom + ''',''''),101) and CONVERT(VARCHAR,ISNULL(PATIENT_DOCUMENTS.CREATED_DTAE,''''),101) <= CONVERT(VARCHAR,ISNULL(''' + @dateTo + ''',''''),101)'
--print @sqlstr
END

IF(@startFrom <> 'null')        
BEGIN
    SET @sqlstr = @sqlstr + '  CONVERT(VARCHAR,ISNULL(PATIENT_DOCUMENTS.CONTENT_START_DATE,''''),101) >= CONVERT(VARCHAR,ISNULL(''' + @startFrom + ''',''''),101) and CONVERT(VARCHAR,ISNULL(PATIENT_DOCUMENTS.CONTENT_START_DATE,''''),101) <= 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)<>1                            

 AND PM.Patient_GlobalId='''+ @GLOBALPATIENTACCOUNT + '''  AND PM.PRACTICE_CODE<>''' + @practice_code + '''    and DOCUMENTS.confidential<>1'

IF(@dateFrom <> 'null')


BEGIN
    SET @sqlstr = @sqlstr + 'and ISNULL(CONVERT(VARCHAR,PATIENT_DOCUMENTS.CREATED_DTAE,101),'''')  >= ISNULL(CONVERT(VARCHAR,''' + @dateFrom + ''',101),'''') and ISNULL(CONVERT(VARCHAR,PATIENT_DOCUMENTS.CREATED_DTAE,101),'''')<=ISNULL(CONVERT(VARCHAR,''' + @dateTo + ''',101),'''')'
END

IF (@startFrom <> 'null')
BEGIN
    SET @sqlstr = @sqlstr + 'CONVERT(VARCHAR,PATIENT_DOCUMENTS.CONTENT_START_DATE,101) >= CONVERT(VARCHAR,''' + @startFrom + ''',101) and CONVERT(VARCHAR,PATIENT_DOCUMENTS.CONTENT_START_DATE,101)<=CONVERT(VARCHAR,''' + @startTo + ''',101)'
END

SET @sqlstr = @sqlstr + ') PatDocTable order by PRACTICE_CODE,CREATED_DTAE desc '

EXECUTE sp_executesql @statement = @sqlstr       
        PRINT @sqlstr

It gives me error Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar',but when i change datatype @sqlstr AS nvarchar (max),error omitted,but it trancuate my query.Kindly help me to figure it out?

What you mean truncates code? when you print? If yes, then it is not truncating. It just don't print you all code. — Darka, Oct 13 '14 at 08:24
as the max length of nvarchar(max) is 4000,beyond it ,it trancuates the remaining query — Karim Musa, Oct 13 '14 at 08:26
Take a look at `Avoiding truncation when viewing ` in [SQL NVARCHAR and VARCHAR Limits](http://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits) — bummi, Oct 13 '14 at 08:34
I would like to make 3 very strong recommendations. First is to stop using varchar to store datetime data. Do yourself a massive favor and use the datetime datatypes. That is what they are for. The second recommendation is to stop executing parameters directly in dynamic sql. This is wide open to sql injection. The third thing is to read this article about to best write this type of catch-all query. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ — Sean Lange, Oct 13 '14 at 13:54

score 45 · Answer 1 · answered Sep 20 '16 at 09:07

45

sp_executesql takes NVARCHAR as parameter not VARCHAR, change varchar(max) to nvarchar(max) will fix the problem.

DECLARE @sqlstr AS  nvarchar (max)

answered Sep 20 '16 at 09:07

TheTechGuy

16,560
16
115
136

1

Quick way to test sp_executesql is to use unicode N prefix: exec sp_executesql 'select * from dbo.table' --> DOES NOT WORK exec sp_executesql N'select * from dbo.table'; --> WORKS – unpantofar Dec 16 '21 at 00:55

score 34 · Accepted Answer · answered Oct 14 '14 at 14:41

34

Here is one of the possible solution:

Convert your @sqlstr to varchar(max) and instead of EXECUTE sp_executesql, use EXECUTE(@strsql).In this fashion you got your complete print query and it will also execute your query.Hope that it helps.

answered Oct 14 '14 at 14:41

Muhammad Ali

853
1
10
18

score 11 · Answer 3 · answered Nov 24 '15 at 06:35

Declare variable as a nvarchar type when you are using command exec sp_executesql.Above you used varchar for @sqlstr variable.
Eg.

Declare @Sqlstr nvarchar(max)
SET @Sqlstr='...Your dynamic query...'
exec sp_executesql @Sqlstr

Note:Dont use brackets in exec command

score 0 · Answer 4 · edited Nov 16 '20 at 19:15

0

using sp_executesql is very good option for this type of queries and it is not very friendly for sql injection.

ex:

DECLARE @sqlString nvarchar(500);
DECLARE @paramDefinition nvarchar(500);

DECLARE @sid int = 12546;

SET @sqlString = 'select * from SameTableBase S Where S.Id = @id';
SET @paramDefinition = N'@id int'; 
EXECUTE sp_executesql @sqlString , @paramDefinition , @id = @sid

edited Nov 16 '20 at 19:15

Muhammad Musavi

2,512
2
22
35

answered Apr 16 '15 at 05:46

M2012

3,465
2
20
27

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'

4 Answers4