ご存じの通り、SQLにおいてはSELECT句などでの末尾のカンマはエラーとなります。
SELECT Col1, Col2, Col3, --Error! 末尾のカンマはダメ FROM YourTable
普通のSQLを書いている分には気を付ければ良いだけではあるのですが、ストアドプロシージャなどで動的にSQLを組み立てる際にはこの規則がちょっとした面倒を引き起こします。
サンプル
今回扱うサンプルは動的SQLを扱った本"Dynamic SQL"(Pollack, Edward著 Apress)に登場するストアドプロシージャを参考にしました。
この本はSQLServerにおける動的SQLに関する様々なテクニックを取り扱っている良い本です。
-- https://github.com/Apress/dynamic-sql-2e/blob/master/ch06.sql#L64 より抜粋 SELECT @sql_command = ' SELECT Product.Name AS product_name, Product.ProductNumber AS product_number,'; IF @show_product_model = 1 OR @show_catalog_description = 1 SELECT @sql_command = @sql_command + ' ProductModel.Name AS product_model_name, ProductModel.CatalogDescription AS productmodel_catalog_description,'; IF @show_product_subcategory = 1 OR @show_subcategory_modified_date = 1 SELECT @sql_command = @sql_command + ' ProductSubcategory.Name AS product_subcategory_name, ProductSubcategory.ModifiedDate AS product_subcategory_modified_date,'; IF @show_product_sizemeasurecode = 1 SELECT @sql_command = @sql_command + ' SizeUnitMeasureCode.Name AS size_unit_measure_code,'; IF @show_product_weightunitmeasurecode = 1 SELECT @sql_command = @sql_command + ' WeightUnitMeasureCode.Name AS weight_unit_measure_code,'; IF @show_color = 1 OR @show_safetystocklevel = 1 OR @show_reorderpoint = 1 OR @show_standard_cost = 1 SELECT @sql_command = @sql_command + ' Product.Color AS product_color, Product.SafetyStockLevel AS product_safety_stock_level, Product.ReorderPoint AS product_reorderpoint, Product.StandardCost AS product_standard_cost'; -- In the event that there is a comma at the end of our command string, remove it before continuing: IF (SELECT SUBSTRING(@sql_command, LEN(@sql_command), 1)) = ',' SELECT @sql_command = LEFT(@sql_command, LEN(@sql_command) - 1); SELECT @sql_command = @sql_command + ' FROM Production.Product' -- Put together the JOINs based on what tables are required by the search. IF (@product_model IS NOT NULL OR @show_product_model = 1 OR @show_catalog_description = 1) SELECT @sql_command = @sql_command + ' LEFT JOIN Production.ProductModel ON Product.ProductModelID = ProductModel.ProductModelID'; IF (@product_subcategory IS NOT NULL OR @show_subcategory_modified_date = 1 OR @show_product_subcategory = 1) SELECT @sql_command = @sql_command + ' LEFT JOIN Production.ProductSubcategory ON Product.ProductSubcategoryID = ProductSubcategory.ProductSubcategoryID'; IF (@product_sizemeasurecode IS NOT NULL OR @show_product_sizemeasurecode = 1) SELECT @sql_command = @sql_command + ' LEFT JOIN Production.UnitMeasure SizeUnitMeasureCode ON Product.SizeUnitMeasureCode = SizeUnitMeasureCode.UnitMeasureCode'; IF (@product_weightunitmeasurecode IS NOT NULL OR @show_product_weightunitmeasurecode = 1) SELECT @sql_command = @sql_command + ' LEFT JOIN Production.UnitMeasure WeightUnitMeasureCode ON Product.WeightUnitMeasureCode = SizeUnitMeasureCode.UnitMeasureCode'; SELECT @sql_command = @sql_command + ' WHERE 1 = 1';
上記の例ではSELECT
句を組み立てた後、末尾のカンマが存在している場合は削除するという処理を行っています。
IF (SELECT SUBSTRING(@sql_command, LEN(@sql_command), 1)) = ',' SELECT @sql_command = LEFT(@sql_command, LEN(@sql_command) - 1);
もちろん、これは問題なく動作するのですが、随所に現れるSELECT @sql_command = @sql_command + '...
という表記と相まって独特の取っ付きづらさがあると感じます。
CONCAT_WS関数
こういう時にはCONCAT_WS
関数が便利です。SQL Server 2017にて追加された新しめの関数で、セパレーター指定ができる(With Separator)のCONCAT関数です。
都合の良いことに、連結文字列がNULLの場合はセパレーターは重複しません。
CONCAT_WS
関数を用いて上記のストアドプロシージャを書き直したものは下記のようになります。
CREATE OR ALTER PROCEDURE [dbo].[search_products2] @product_name NVARCHAR(50) = NULL, @product_number NVARCHAR(25) = NULL, @product_model NVARCHAR(50) = NULL, @product_subcategory NVARCHAR(50) = NULL, @product_sizemeasurecode NVARCHAR(50) = NULL, @product_weightunitmeasurecode NVARCHAR(50) = NULL, @show_color BIT = 0, @show_safetystocklevel BIT = 0, @show_reorderpoint BIT = 0, @show_standard_cost BIT = 0, @show_catalog_description BIT = 0, @show_subcategory_modified_date BIT = 0, @show_product_model BIT = 0, @show_product_subcategory BIT = 0, @show_product_sizemeasurecode BIT = 0, @show_product_weightunitmeasurecode BIT = 0 AS BEGIN SET NOCOUNT ON; IF COALESCE(@product_name, @product_number, @product_model, @product_subcategory, @product_sizemeasurecode, @product_weightunitmeasurecode) IS NULL RETURN; -- Add "%" delimiters to parameters that will be searched as wildcards. SET @product_name = '%' + @product_name + '%'; SET @product_number = '%' + @product_number + '%'; SET @product_model = '%' + @product_model + '%'; -- Define the parameter list for filter criteria. DECLARE @parameter_list NVARCHAR(MAX) = CONCAT_WS(', ' , '@product_name NVARCHAR(50)' , '@product_number NVARCHAR(25)' , '@product_model NVARCHAR(50)' , '@product_subcategory NVARCHAR(50)' , '@product_sizemeasurecode NVARCHAR(50)' , '@product_weightunitmeasurecode NVARCHAR(50)' ); DECLARE @sql_template NVARCHAR(MAX) = 'SELECT @select_clause FROM @from_clause WHERE @where_clause' DECLARE @select_clause NVARCHAR(MAX) = CONCAT_WS(char(13)+char(10) + ' , ' , 'P.Name AS [product_name]' , 'P.ProductNumber AS [product_number]' , IIF(@show_product_model = 1 OR @show_catalog_description = 1, 'PM.Name AS [product_model_name] , PM.CatalogDescription AS [productmodel_catalog_description]', NULL) , IIF(@show_product_subcategory = 1 OR @show_subcategory_modified_date = 1, 'PS.Name AS [product_subcategory_name] , PS.ModifiedDate AS [product_subcategory_modified_date]', NULL) , IIF(@show_product_sizemeasurecode = 1, 'SizeUnitMeasureCode.Name AS [size_unit_measure_code]', NULL) , IIF(@show_product_weightunitmeasurecode = 1, 'WeightUnitMeasureCode.Name AS [weight_unit_measure_code]', NULL) , IIF(@show_color = 1 OR @show_safetystocklevel = 1 OR @show_reorderpoint = 1 OR @show_standard_cost = 1, 'P.Color AS [product_color] , P.SafetyStockLevel AS [product_safety_stock_level] , P.ReorderPoint AS [product_reorderpoint] , P.StandardCost AS [product_standard_cost]', NULL) ); -- In the event that there is a comma at the end of our command string, remove it before continuing: DECLARE @from_clause NVARCHAR(MAX) = CONCAT_WS(char(13)+char(10) + ' ' , 'Production.Product AS P' , IIF(@product_model IS NOT NULL OR @show_product_model = 1 OR @show_catalog_description = 1, 'LEFT JOIN Production.ProductModel AS PM ON P.ProductModelID = PM.ProductModelID', NULL) , IIF (@product_subcategory IS NOT NULL OR @show_subcategory_modified_date = 1 OR @show_product_subcategory = 1, 'LEFT JOIN Production.ProductSubcategory AS PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID', NULL) , IIF (@product_sizemeasurecode IS NOT NULL OR @show_product_sizemeasurecode = 1, 'LEFT JOIN Production.UnitMeasure AS SizeUnitMeasureCode ON P.SizeUnitMeasureCode = SizeUnitMeasureCode.UnitMeasureCode', NULL) , IIF (@product_weightunitmeasurecode IS NOT NULL OR @show_product_weightunitmeasurecode = 1, 'LEFT JOIN Production.UnitMeasure AS WeightUnitMeasureCode ON P.WeightUnitMeasureCode = SizeUnitMeasureCode.UnitMeasureCode', NULL) ); -- Build the WHERE clause based on which tables are referenced and required by the search. DECLARE @where_clause NVARCHAR(MAX) = CONCAT_WS(char(13)+char(10) + ' AND ' , '(1 = 1)' , IIF(@product_name IS NOT NULL, '(P.Name LIKE @product_name)', NULL) , IIF(@product_number IS NOT NULL, '(P.ProductNumber LIKE @product_number)', NULL) , IIF(@product_model IS NOT NULL, '(PM.Name LIKE @product_model)', NULL) , IIF(@product_subcategory IS NOT NULL, '(PS.Name = @product_subcategory)', NULL) , IIF(@product_sizemeasurecode IS NOT NULL, '(SizeUnitMeasureCode.Name = @product_sizemeasurecode)', NULL) , IIF(@product_weightunitmeasurecode IS NOT NULL, '(WeightUnitMeasureCode.Name = @product_weightunitmeasurecode)', NULL) ); DECLARE @sql_command NVARCHAR(MAX) = @sql_template; SET @sql_command = REPLACE(@sql_command, '@select_clause', @select_clause); SET @sql_command = REPLACE(@sql_command, '@from_clause', @from_clause); SET @sql_command = REPLACE(@sql_command, '@where_clause', @where_clause); PRINT @sql_command; EXEC sp_executesql @sql_command, @parameter_list, @product_name, @product_number, @product_model, @product_subcategory, @product_sizemeasurecode, @product_weightunitmeasurecode; END
IIF
関数を用いて、条件に当てはまらない場合にNULLを返すことでセパレーターの重複や末尾への出現を回避できます。
また、セパレーターとして改行を利用する場合は、そのままソース内で改行をしてもいいですし、上記のようにchar(13)+char(10)
(CR+LF)としてもOKです。
でも動的SQLの見た目にこだわるのは確認用途くらいだと思うので、この辺りはほどほどにしています。
CREATE OR ALTER
は以前の記事を参考にしてください。
こうした変更が読みやすくなるのか、という点では各々意見があるでしょうが、私はこっちの方が好きです。