nvarchar'

Voici ma requête basée sur 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

Cela me donne une erreur. La procédure attend le paramètre '@statement' de type 'ntext / nchar / nvarchar' , mais quand je change le type de données @sqlstr AS nvarchar (max) , l'erreur est omise, mais elle trancute ma requête. Aidez-moi à comprendre ?

Voici une des solutions possibles:

Convertissez votre @sqlstr en varchar(max) et au lieu de EXECUTE sp_executesql , utilisez EXECUTE(@strsql) . De cette façon, vous avez votre requête d'printing complète et il exécutera également votre requête. Espérons que cela aide.

Déclarez la variable en tant que type nvarchar lorsque vous utilisez command exec sp_executesql . Ci- dessus, vous avez utilisé varchar pour la variable @sqlstr.
Par exemple.

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

Note: Ne pas utiliser les crochets dans la command exec

sp_executesql prend NVARCHAR en tant que paramètre non VARCHAR , changer varchar(max) en nvarchar(max) va résoudre le problème.

 DECLARE @sqlstr AS nvarchar (max)

L'utilisation de sp_executesql est une très bonne option pour ce type de requêtes et elle n'est pas très conviviale pour l'injection sql.

ex:

 DECLARE @sqlSsortingng nvarchar(500); DECLARE @paramDefinition nvarchar(500); DECLARE @sid = 12546; SET @sqlSsortingng = 'select * from SameTableBase S Where S.Id = @id'; EXECUTE sp_executesql @sqlSsortingng , @paramDefinition , @id = @sid