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.

注意

作為動態 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.