[解決済み] 複数の Id 値を受け付ける T-SQL ストアド・プロシージャ

質問

ストアドプロシージャにパラメータとして ID のリストを渡すための優雅な方法はありますか?

たとえば、私は部門1、2、5、7、20をストアドプロシージャで返したいです。過去に、私は以下のコードのようなidのカンマ区切りリストを渡しましたが、それを行うことは本当に汚いと感じています。

SQL Server 2005 は、私が考える唯一の適用可能な制限です。

create procedure getDepartments
  @DepartmentIds varchar(max)
as
  declare @Sql varchar(max)     
  select @Sql = 'select [Name] from Department where DepartmentId in (' + @DepartmentIds + ')'
  exec(@Sql)

どのように解決するのですか？

Erland Sommarskogは、過去16年間、この質問に対する権威ある回答を維持してきました。 SQL Server における配列とリスト .

配列やリストをクエリに渡す方法は、少なくとも1ダースあります。それぞれが独自の長所と短所を持っています。

• テーブル値パラメータ . SQL Server 2008 以降でのみ使用され、おそらく普遍的な "best" アプローチに最も近いものです。
• 反復法 . 区切られた文字列を渡してループさせる。
• CLRを使用する . .NET 言語からの SQL Server 2005 以降のみ。
• XML . 多くの行を挿入するのに非常に適していますが、SELECT には過剰かもしれません。
• 数値の表 . 単純な反復法より高い性能/複雑さ。
• 固定長要素 . 固定長にすることで、区切り文字列よりも速度が向上する
• 数値の関数 . 数の表と固定長のバリエーションで、数は表から取得するのではなく、関数で生成されます。
• 再帰的共通テーブル式 (CTE)を使用します。SQL Server 2005 以降、まだあまり複雑ではなく、反復法より高いパフォーマンスを発揮します。
• 動的SQL . 遅くなることがあり、セキュリティ上の影響がある。
• としてリストを渡す 多くのパラメータ . 面倒でエラーが起こりやすいですが、シンプルです。
• 本当に遅いメソッド . charindex、patindex、LIKEを使用するメソッドです。

本当にお勧めできるのは 記事を読む を読んで、これらのオプションの間のトレードオフについて学んでください。