检索刚刚执行的存储过程名称 [英] Retrieve stored procedure name that has just been executed

问题描述

我在文本文件中有两个存储过程：

-usp_StoredProcedureOne

-usp_StoredProcedureTwo



在C＃中执行它们并将它们保存在SQL Server 2008中后，我想检索它们的名称并存储到文本文件中以进行日志记录。我有下面的代码，用于执行存储过程并使用C＃提交它们。我该如何只检索名称？以下是我的代码：

Hi, I have two stored procedures inside a text file which are:
-usp_StoredProcedureOne
-usp_StoredProcedureTwo

After I have execute them in C# and they are saved in SQL Server 2008, I want to retrieve their names and store into a text file for logging purposes. I have below codes which are used to execute stored procedure and commit them using C#. How can I retrieve the name only? Below are my codes:

using (SqlConnection conn = new SqlConnection(sqlConnectionString))
{
    conn.Open();
    Server server = new Server(new ServerConnection(conn));
    SqlTransaction transaction;
    transaction = conn.BeginTransaction();

    try
    {
        server.ConnectionContext.ExecuteNonQuery(content);
        transaction.Commit();

        //I wanna retrieve the name after commit.
        using (SqlCommand cmd = new SqlCommand("SELECT OBJECT_NAME(@@PROCID)", conn))
        {
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                using (StreamWriter sw = new StreamWriter(@"C:\Users\Desktop\" + DateTime.Now.ToString("yyyyMMdd") + "_Extra.txt", true))
                {
                    sw.WriteLine(reader);
                    sw.Close();
                }
            }
        }

        return true;
    }
    catch(Exception ex){}
}

我尝试了什么：



我尝试过使用SELECT OBJECT_NAME（@@ PROCID），但确实如此不为我返回任何值。

What I have tried:

I have tried using SELECT OBJECT_NAME(@@PROCID) but it did not return any value for me.

推荐答案

您实际上不需要查询数据库来获取过程名称。您可以从 content 变量（其中包含创建过程脚本）获取名称。只需得到创建程序之后的单词。



如果你总是只有这两个程序，为什么不只需使用静态名称创建文件？你已经知道名字应该是什么了。恕我直言，硬编码文件名没有任何问题，如果你知道只有这两个文件。



如果你真的想查询数据库要获取存储过程名称，请查看 [databaseName] .Information_Schema.Routines 视图。您可以使用简单的选择从那里获取过程名称。

You do not really need to query the database to get the procedure name. You can get the name from content variable (which holds create procedure script). Just get the word that follows Create Procedure.

If you will always have only these 2 procedures, why not simply create the files with static names? You already know what the names should be. IMHO there is nothing wrong with hardcoding file name if you know there will be only these two files.

If you really want to query database to get the stored procedure names, take a look at [databaseName].Information_Schema.Routines view. You can get the procedure name from there using a simple select.

这篇关于检索刚刚执行的存储过程名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！