none
T-SQLスクリプトの変数 RRS feed

  • 質問

  • データーベースアカウントを作成するスクリプトをこんな感じで書いています。
    -- ログインアカウントとパスワード<br/>
    DECLARE @loginname nvarchar(258) = N'admin';<br/>
    DECLARE @password nvarchar(258) = N'admin';<br/>
    <br/>
    -- 以下は変更禁止<br/>
    SET @loginname = QUOTENAME(@loginname);<br/>
    SET @password = QUOTENAME(@password, N'''');<br/>
    EXECUTE (N'CREATE LOGIN ' + @loginname + N' WITH PASSWORD=N' + @password + N', DEFAULT_DATABASE=[SomeDatabase];'<br/>
    	+ N'USE [SomeDatabase];'<br/>
    	+ N'CREATE USER ' + @loginname + N' FOR LOGIN ' + @loginname + N';'<br/>
    	+ N'EXECUTE sp_addrolemember [db_owner], ' + @loginname + N';');<br/>
    GO<br/>
    
    

    CREATE LOGINやEXECUTE文に変数が使えなく、SQL文を作成するEXECUTEが必要になっていますが、こんなもんなのでしょうか? sp_executesqlも役に立たなさそうで。

    もっとうまい書き方はないものでしょうか?

    2011年1月5日 6:03

回答

  • もっとうまい書き方はないものでしょうか?

    無いようですね。いろいろ調べてみましたが、全て佐祐理さんと同じ書き方(Dynamic SQLと呼ばれているようですね)で解決されていました。変数が使えない以上、Dynamic SQLにするしかないのでしょう。

     


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    • 回答としてマーク 佐祐理 2011年1月26日 5:27
    2011年1月6日 1:57

すべての返信

  • sp_addlogin

    http://msdn.microsoft.com/ja-jp/library/ms173768.aspx

    sp_adduser

    http://msdn.microsoft.com/ja-jp/library/ms181422.aspx

    というシステムストアドプロシージャがあります。

    2011年1月5日 10:43
  • 返信ありがとうございます。

    あげていただいたストアドプロシージャはsp_addrolememberと同じく変数が使えず、EXECUTE文を作るためのEXECUTE文が必要になることには変わらないのではないでしょうか?

    sp_addloginの方は変数が使えて@passwordだけでもquote不要になるのかなぁ…
    そうでなくても@passwordのquote方法がN''に限定されなくなりEXECUTE文がもう少し作りやすくなる?!

    2011年1月6日 0:59
  • もっとうまい書き方はないものでしょうか?

    無いようですね。いろいろ調べてみましたが、全て佐祐理さんと同じ書き方(Dynamic SQLと呼ばれているようですね)で解決されていました。変数が使えない以上、Dynamic SQLにするしかないのでしょう。

     


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    • 回答としてマーク 佐祐理 2011年1月26日 5:27
    2011年1月6日 1:57
  • 変数が使えないというのが、私の知らない問題があるのかもしれませんが、

    一応、下記のSQLでストアドプロシージャは実行できました。

    DECLARE @name nvarchar(255) =N'TEST'
    DECLARE @pass nvarchar(255) =N'passwrod'

    USE master;
    EXEC sp_addlogin @loginame=@name,@passwd=@pass,@defdb=N'SomeDatabase';
    USE [SomeDatabase];
    EXEC sp_adduser @loginame=@name,@name_in_db=@name;
    EXEC sp_addrolemember @rolename=N'db_owner',@member_name=@name;

    ただ、すっかり忘れていました。

    sp_addlogin等は、非推奨(将来の互換性がなくなる)でした。

    で、sp_addloginのソースを見ると、内部では、佐祐理さんと同様の処理をしているので、

    sp_addlogin等を使用しないとすると、

    ご指摘の方法になるのではないかと思います。


    --------------------------------- Infospire Kayano
    2011年1月6日 2:34
  • IS Kayanoさん、trapemiyaさん、返信ありがとうございます。

    IS Kayanoさんの指摘を受け試してみましたが、確かに変数を使用できました。実際のスクリプトと、質問用に簡略化したスクリプトとでどこに差があるのか確認しました。

    -- ログインアカウントとパスワード
    DECLARE @loginname nvarchar(258) = N'admin';
    DECLARE @password nvarchar(258) = N'admin';

    -- 以下は変更禁止
    CREATE DATABASE [SomeDatabase];

    SET @loginname = QUOTENAME(@loginname);
    SET @password = QUOTENAME(@password, N'''');
    EXECUTE (N'CREATE LOGIN ' + @loginname + N' WITH PASSWORD=N' + @password + N', DEFAULT_DATABASE=[SomeDatabase];'
        + N'USE [SomeDatabase];'
        + N'CREATE USER ' + @loginname + N' FOR LOGIN ' + @loginname + N';'
        + N'EXECUTE sp_addrolemember [db_owner], ' + @loginname + N';');
    GO

    実際には、DECLARE文とEXECUTE文の間にCREATE DATABASEがあり、DECLAREした時点にはまだデータベースが存在していませんでした。

    この場合、

    メッセージ 137、レベル 15、状態 2、行 2
    スカラー変数 "@loginname" を宣言してください。
    メッセージ 137、レベル 15、状態 2、行 3
    スカラー変数 "@loginname" を宣言してください。

    のようなエラーになってしまいます。ですので、質問を訂正すると、データベース作成前に宣言した変数の使用方法は? となりますでしょうか。

    もう1点、CREATE LOGINはデータベースコンテキストを切り替える前に実行しますので変数が使えるのですが、パスワード指定するためにはPASSWORD=N''の形式で渡す必要があり、変数が使えなさそうです。
    IS Kayanoさんから提示いただいたsp_addloginであればパスワードも変数で指定できるのですが、既に訂正されていますように非推奨のストアドプロシージャです。こちらについては、CREATE LOGIN文でパスワードを変数で渡す方法は? となりますでしょうか。

    …やはりtrapemiyaさんが回答されている通り、変数が使えないという結論になってしまうのでしょうか。もう少しコメントを待ってみたいと思います。

    2011年1月6日 6:02