Synapse SQL 池中的动态 SQLDynamic SQL in Synapse SQL pool

本文提供了在 SQL 池中使用动态 SQL 开发解决方案的技巧。Included in this article are tips for development solutions using dynamic SQL in SQL pool.

动态 SQL 示例Dynamic SQL Example

针对 SQL 池开发应用程序代码时,可能需要借助动态 SQL 来提供灵活、通用且模块化的解决方案。When developing application code for SQL pool, you may need to use dynamic SQL to help deliver flexible, generic, and modular solutions. SQL 池目前不支持 blob 数据类型。SQL pool doesn't support blob data types at this time.

不支持 blob 数据类型可能会限制字符串的大小,因为 blob 数据类型包括 varchar(max) 和 nvarchar(max) 类型。Not supporting blob data types might limit the size of your strings since blob data types include both varchar(max) and nvarchar(max) types.

如果已在应用程序代码中使用这些类型构建大型字符串,则需将代码分解成区块,并改用 EXEC 语句。If you've used these types in your application code to build large strings, you need to break the code into chunks and use the EXEC statement instead.

一个简单的示例:A simple example:

DECLARE @sql_fragment1 VARCHAR(8000)=' SELECT name '
,       @sql_fragment2 VARCHAR(8000)=' FROM sys.system_views '
,       @sql_fragment3 VARCHAR(8000)=' WHERE name like ''%table%''';

EXEC( @sql_fragment1 + @sql_fragment2 + @sql_fragment3);

如果字符串较短,则可以像平时一样使用 sp_executesqlIf the string is short, you can use sp_executesql as normal.

Note

作为动态 SQL 执行的语句仍会受所有 T-SQL 验证规则的约束。Statements executed as dynamic SQL will still be subject to all T-SQL validation rules.

后续步骤Next steps

有关更多开发技巧,请参阅 开发概述For more development tips, see development overview.