makoラボ

日常や勉強会や技術ネタ

SQLServerでCREATE VIEWをsp_executesqlで実行する時にパラメータつけれない

環境はSQLServer2014

SQLServerでどうしてもCREATE VIEWの構文を文字列で組み立てて、sp_executesqlで実行しないといけない場合があったとしてます。 以下のようにするとエラーが発生します。

DECLARE @userName AS NVARCHAR(MAX) = 'YAMADA'
DECLARE @mySql NVARCHAR(MAX) = '
  CREATE VIEW YAMADA AS
  SELECT
    *
  FROM
    MEMBER
  WHERE
    LastName = @name
'

EXECUTE sp_executesql @mySql, N'@name AS VARCHAR(MAX)', @name = @userName

エラー内容はこちら

Incorrect syntax near the keyword 'VIEW'.

日本語だと

キーワード 'VIEW' 付近に不適切な構文があります。

色々と調べてみるとこのやり方じゃダメっぽい

Create View using sp_executesql

stackoverflow.com

上記のリンクに書いてあるようにこちらの書き方だとエラーが発生するようです。

以下のように書けば一応実行できますが、@userNameが外部から入力できる場合はSQLインジェクションの可能性があるのでダメですね・・・

DECLARE @userName AS NVARCHAR(MAX) = 'YAMADA'
DECLARE @mySql NVARCHAR(MAX) = '
  CREATE VIEW YAMADA AS
  SELECT
    *
  FROM
    MEMBER
  WHERE
    LastName = ' + @userName  + '
'

EXECUTE sp_executesql @mySql

一旦、ワークテーブルに突っ込む等別の方法を考える方が良さそうです。