使用临时表的SQL Server连接上下文不能在用SqlDataAdapter.Fill调用的存储过程中使用 [英] SQL Server connection context using temporary table cannot be used in stored procedures called with SqlDataAdapter.Fill

查看：137
本文介绍了使用临时表的SQL Server连接上下文不能在用SqlDataAdapter.Fill调用的存储过程中使用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想要一些信息可用于任何存储过程，例如当前用户。按照此处所示的临时表方法，我尝试了以下操作：



1）打开连接时创建临时表

  private void setConnectionContextInfo（SqlConnection connection）
 {
 if（！AllowInsertConnectionContextInfo）
返回; 
 
 var用户名= HttpContext.Current？.User？.Identity？.Name ?? ; 
 
 var commandBuilder = new StringBuilder（$ @ 
创建表#ConnectionContextInfo（
 AttributeName VARCHAR（64）PRIMARY KEY，
 AttributeValue VARCHAR（1024）
）; 
 
插入#ConnectionContextInfo VALUES（'Username'，@Username）; 
）; 
 
使用（var命令= connection.CreateCommand（））
 {
 command.Parameters.AddWithValue（ Username，username）; 
命令.ExecuteNonQuery（）; 
} 
} 
 
 ///< summary> 
 ///检查当前连接是否存在//关闭并在必要时创建/打开
 ///还通过构建Windows模拟上下文
 /来处理V3所需的特殊身份验证。 //< / summary> 
公共替代无效确保连接（）
 {
 try 
 {
锁（connectionLock）
 {
 if（Connection == null）
 {
 Connection =新的SqlConnection（ConnectionString）; 
 Connection.Open（）; 
 setConnectionContextInfo（Connection）; 
} 
 
 if（Connection.State == ConnectionState.Closed）
 {
 Connection.Open（）; 
 setConnectionContextInfo（Connection）; 
} 
} 
} 
 catch（例外）
 {
 if（Connection！= null&& Connection.State！= ConnectionState。打开）
 Connection.Close（）; 
 
抛出新的ApplicationException（无法打开SQL Server连接。，例如）； 
} 
}

2）已使用用于填充的程序进行了测试 DataTable 使用 SqlDataAdapter.Fill ，并使用以下函数：

  public DataTable GetDataTable（String proc，Dictionary< String，object>参数，CommandType commandType）
 {
 sureConnection（）; 
 
使用（var命令= Connection.CreateCommand（））
 {
 if（Transaction！= null）
 command.Transaction = Transaction; 
 
 SqlDataAdapter适配器=新的SqlDataAdapter（proc，Connection）; 
 adapter.SelectCommand.CommandTimeout = CommonConstants.DataAccess.DefaultCommandTimeout; 
 adapter.SelectCommand.CommandType = commandType; 
 
 if（Transaction！= null）
 adapter.SelectCommand.Transaction =事务; 
 
 ConstructorCommandParameters（adapter.SelectCommand，参数）; 
 
 DataTable dt = new DataTable（）; 
试试
 {
适配器。Fill（dt）; 
 return dt; 
} 
 catch（SqlException ex）
 {
 var err = String.Format（执行存储过程'{0}'-{1}时出错。，proc，ex 。信息）; 
抛出新的TptDataAccessException（err，ex）; 
} 
} 
}

3）调用过程试图获取这样的用户名：

  DECLARE @username VARCHAR（128）=（从#ConnectionContextInfo中选择AttributeValue，其中AttributeName ='Username' ）

但 #ConnectionContextInfo 不再可用



我对数据库放置了一个SQL事件探查器，以检查发生了什么：




  • 临时表已使用某个SPID成功创建

  • 过程已使用相同的SPID被调用



为什么临时表在过程范围内不可用？



在T-SQL中，进行以下工作： / p>


  • 创建临时表

  • 调用需要该特定临时表中的数据的过程

  • 临时表仅显式删除或事后删除r当前作用域结束



谢谢。

解决方案

次要问题：我现在暂时假设问题中发布的代码不是正在运行的完整代码。不仅存在一些我们看不到被声明的变量（例如 AllowInsertConnectionContextInfo ），而且在 setConnectionContextInfo 方法：创建了 command 对象，但从未将其 CommandText 属性设置为 commandBuilder.ToString（），因此它似乎是一个空的SQL批处理。我确信这实际上是正确的，因为1）我相信提交一个空的批处理会产生一个异常，以及2）这个问题确实提到临时表的创建出现在SQL Profiler输出中。不过，我指出这一点是因为它暗示着可能存在与问题中未显示的与观察到的行为相关的其他代码，这使得给出准确的答案更加困难。



这是合理的，正如@Vladimir的 answer 所述，因为查询在子目录中运行-process（即 sp_executesql ），本地临时对象（表和存储过程）在该子流程完成后无法生存，因此在父上下文中不可用



全局临时对象和永久/非临时对象将在子流程完成后幸存下来，但是这两种选择在其典型用法中都会带来并发问题。 ：在尝试创建表之前，您需要先测试是否存在，和您需要一种将一个进程与另一个进程区分开的方法。因此，这些并不是真正的好选择，至少在它们的典型用法中不是（以后会再介绍）。



假定您不能将任何值传递到存储过程中（否则，您可以按照@Vladimir在其答案中的建议直接输入用户名），您有几种选择：


  1. 给定当前代码，最简单的解决方案是将本地临时表的创建与 INSERT 命令分开（在@Vladimir的答案中也提到了）。如前所述，您遇到的问题是由于查询运行在 sp_executesql 中。使用 sp_executesql 的原因是为了处理参数 @Username 。因此，解决方法很简单，只需将当前代码更改为以下内容即可：

      string _Command = @ 
    创建表#ConnectionContextInfo（
     AttributeName VARCHAR（64）PRIMARY KEY，
     AttributeValue VARCHAR（1024）
    ）;; 
     
    使用（var命令= connection.CreateCommand（））
     {
     command.CommandText = _Command; 
    命令.ExecuteNonQuery（）; 
    } 
     
     _Command = @ 
    插入#ConnectionContextInfo值（ Username，@ Username）； 
    ）； 
     
    使用（var命令= connection.CreateCommand（））
     {
     command.CommandText = _Command; 
     
     //不要使用AddWithValue（）！ 
     SqlParameter _UserName =新的SqlParameter（ @ Username，SqlDbType.NVarChar，128）; 
     _UserName.Value =用户名； 
     command.Parameters.Add（_UserName）; 
     
    命令.ExecuteNonQuery（）; 
    }

    请注意，无法在本地访问临时对象（本地和全局） T-SQL用户定义的函数或表值函数。


  2. 更好的解决方案（最有可能的方法）是使用 CONTEXT_INFO ，本质上是会话内存。它是一个 VARBINARY（128）值，但是由于它不是对象，因此对其进行的更改将在所有子过程中保留下来。这不仅消除了当前面临的麻烦，而且考虑到每次运行此过程都在创建和删除一个临时表时，它还减少了 tempdb I / O，并且进行 INSERT ，然后将所有这3个操作两次写入磁盘：首先在事务日志中，然后在数据文件中。您可以按以下方式使用它：

      string _Command = @ 
     DECLARE @User VARBINARY（128）= CONVERT（VARBINARY（128），@Username）; 
     SET CONTEXT_INFO @User; 
    ; 
     
    使用（var命令= connection.CreateCommand（））
     {
     command.CommandText = _Command; 
     
     //不要使用AddWithValue（）！ 
     SqlParameter _UserName =新的SqlParameter（ @ Username，SqlDbType.NVarChar，128）; 
     _UserName.Value =用户名； 
     command.Parameters.Add（_UserName）; 
     
    命令.ExecuteNonQuery（）; 
    }

    然后在存储过程/用户定义函数/中获取值表值函数/触发方式如下：

      DECLARE @Username NVARCHAR（128）= CONVERT（NVARCHAR （128），CONTEXT_INFO（））;

    对于单个值就可以了，但是如果您需要多个值，或者已经使用 CONTEXT_INFO 进行其他用途，则您需要返回此处介绍的其他方法之一，或者，如果使用SQL Server 2016（或更高版本），则可以使用 SESSION_CONTEXT ，与 CONTEXT_INFO ，但它是一个哈希表/键值对。



    此方法的另一个好处是 CONTEXT_INFO （至少我还没有尝试过 SESSION_CONTEXT ）在T-SQL用户定义函数和表值函数中可用。


  3. 最后，另一个选择是创建一个全局临时表。如上所述，全局对象具有幸存子流程的优点，但是它们也具有使并发复杂化的缺点。很少会获得好处而没有缺点的方法是为临时对象赋予唯一的，基于会话的 name ，而不是添加一列以保存唯一的，基于会话的 value 。使用会话唯一的名称可以消除任何并发问题，同时允许您使用一个对象，该对象在关闭连接后会自动清理（因此不必担心会创建全局临时表然后运行的对象在完成之前发生错误，而使用永久表则需要清除，或者至少在开始时存在检查。）



    请牢记我们无法通过任何限制值存储过程中，我们需要使用在数据层已经存在的值。使用的值为 session_id / SPID。当然，此值在应用程序层中不存在，因此必须重新获取它，但在该方向上没有任何限制。

      int _SessionId; 
     
    使用（var命令= connection.CreateCommand（））
     {
    命令。CommandText= @ SET @SessionID = @@ SPID;; 
     
     SqlParameter _paramSessionID =新的SqlParameter（ @ SessionID，SqlDbType.Int）; 
     _paramSessionID.Direction = ParameterDirection.Output; 
     command.Parameters.Add（_UserName）; 
     
    命令.ExecuteNonQuery（）; 
     
     _SessionId =（int）_paramSessionID.Value; 
    } 
     
    字符串_Command = String.Format（@ 
     CREATE TABLE ## ConnectionContextInfo_ {0}（
     AttributeName VARCHAR（64）PRIMARY KEY，
     AttributeValue VARCHAR（1024）
    ）; 
     
    插入## ConnectionContextInfo_ {0} VALUES（'Username'，@Username）;，_SessionId）; 
     
    使用（var命令= connection.CreateCommand（））
     {
     command.CommandText = _Command; 
     
     SqlParameter _UserName = new SqlParameter（ @ Username，SqlDbType.NVarChar，128）; 
     _UserName.Value =用户名； 
     command.Parameters.Add（_UserName）; 
     
    命令.ExecuteNonQuery（）; 
    }

    然后您可以通过以下方式在存储过程/触发器中获取值：

      DECLARE @Username NVARCHAR（128），
     @UsernameQuery NVARCHAR（4000）; 
     
     SET @UsernameQuery = CONCAT（N'SELECT @tmpUserName = [AttributeValue] 
     FROM ## ConnectionContextInfo_'，@@ SPID，N'WHERE [AttributeName] =``用户名''; '）; 
     
     EXEC sp_executesql 
     @ UsernameQuery，
     N’@ tmpUserName NVARCHAR（128）OUTPUT，
     @Username OUTPUT;

    请注意，在T-SQL用户-定义的函数或表值函数。


  4. 最后，可以使用实/永久（即非临时）表，只要您包括一个列来保存特定于当前会话的值。



    您可以在 tempdb 中创建表（是的，您可以将 tempdb 用作常规数据库，而不必只是以＃或<$开头的临时对象c $ c> ## ）。使用 tempdb 的优点在于，该表不影响其他所有内容（毕竟，它只是临时值，不需要还原，因此使用 SIMPLE 恢复模型的 tempdb 是完美的），并且在实例重新启动时将其清除（仅供参考： tempdb 是全新创建的，每次SQL Server启动时都会作为 model 的副本）。



    就像上面的选项＃3一样，我们可以再次使用 session_id / SPID值，因为它是此Connection上所有操作所共有的（只要连接保持打开状态）。但是，与选项＃3不同，应用程序代码不需要SPID值：可以使用默认约束将其自动插入到每一行中。



    这里的概念是先检查 tempdb 中的永久表是否存在。存在。如果是这样，则请确保当前SPID的表中没有条目。如果不是，则创建表。由于它是一个永久表，即使在当前进程关闭其连接后，它也将继续存在。最后，插入 @Username 参数，SPID值将自动填充。

      //假定_Connection已使用（SqlCommand _Command = _Connection.CreateCommand（））打开
     
     {
     _Command.CommandText = @ 
     IF（OBJECT_ID（N'tempdb.dbo.Usernames'）不为空）
    开始
     IF（存在（选择* 
    来自[tempdb]。[dbo]。[用户名] 
     [SessionID] = @@ SPID 
    ））
    开始
    从[tempdb]。[dbo]中删除。[用户名] 
     WHERE [SessionID] = @@ SPID； 
     END； 
     END； 
     ELSE 
    开始
    创建表[tempdb]。[dbo]。[用户名] 
    （
     [SessionID] INT非空
    约束[PK_Usernames]主键
    约束[DF_Usern ames_SessionID]默认（@@ SPID），
     [用户名] NVARCHAR（128）NULL，
     [InsertTime] DATETIME不是NULL 
     CONSTRAINT [DF_Usernames_InsertTime]默认（GETDATE（））
     ）; 
     END； 
     
    插入[tempdb]。[dbo]。[用户名]（[用户名]）值（@UserName）; 
    ; 
     
     SqlParameter _UserName = new SqlParameter（ @ Username，SqlDbType.NVarChar，128）; 
     _UserName.Value =用户名； 
    命令。 Add（_UserName）; 
     
     _Command.ExecuteNonQuery（）; 
    }

    然后您可以通过以下方式在存储过程/用户定义函数/表值函数/触发器中获取值：

     从[tempdb]。[dbo]中选择[用户名] 
    。[用户名] 
    其中[SessionID] = @@ SPID;

    此方法的另一个好处是，可以在T-SQL用户定义函数和表值函数中访问永久表。



I want to have some information available for any stored procedure, such as current user. Following the temporary table method indicated here, I have tried the following:

1) create temporary table when connection is opened

        private void setConnectionContextInfo(SqlConnection connection)
        {
            if (!AllowInsertConnectionContextInfo)
                return;

            var username = HttpContext.Current?.User?.Identity?.Name ?? "";

            var commandBuilder = new StringBuilder($@"
CREATE TABLE #ConnectionContextInfo(
    AttributeName VARCHAR(64) PRIMARY KEY, 
    AttributeValue VARCHAR(1024)
);

INSERT INTO #ConnectionContextInfo VALUES('Username', @Username);
");

            using (var command = connection.CreateCommand())
            {
                command.Parameters.AddWithValue("Username", username);
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// checks if current connection exists / is closed and creates / opens it if necessary
        /// also takes care of the special authentication required by V3 by building a windows impersonation context
        /// </summary>
        public override void EnsureConnection()
        {
            try
            {
                lock (connectionLock)
                {
                    if (Connection == null)
                    {
                        Connection = new SqlConnection(ConnectionString);
                        Connection.Open();
                        setConnectionContextInfo(Connection);
                    }

                    if (Connection.State == ConnectionState.Closed)
                    {
                        Connection.Open();
                        setConnectionContextInfo(Connection);
                    }
                }
            }  
            catch (Exception ex)
            {
                if (Connection != null && Connection.State != ConnectionState.Open)
                    Connection.Close();

                throw new ApplicationException("Could not open SQL Server Connection.", ex);
            }
        }

2) Tested with a procedure which is used to populate a DataTable using SqlDataAdapter.Fill, by using the following function:

    public DataTable GetDataTable(String proc, Dictionary<String, object> parameters, CommandType commandType)
    {
        EnsureConnection();

        using (var command = Connection.CreateCommand())
        {
            if (Transaction != null)
                command.Transaction = Transaction;

            SqlDataAdapter adapter = new SqlDataAdapter(proc, Connection);
            adapter.SelectCommand.CommandTimeout = CommonConstants.DataAccess.DefaultCommandTimeout;
            adapter.SelectCommand.CommandType = commandType;

            if (Transaction != null)
                adapter.SelectCommand.Transaction = Transaction;

            ConstructCommandParameters(adapter.SelectCommand, parameters);

            DataTable dt = new DataTable();
            try
            {
                adapter.Fill(dt);
                return dt;
            }
            catch (SqlException ex)
            {
                var err = String.Format("Error executing stored procedure '{0}' - {1}.", proc, ex.Message);
                throw new TptDataAccessException(err, ex);
            }
        }
    }

3) called procedure tries to get the username like this:

DECLARE @username VARCHAR(128) = (select AttributeValue FROM #ConnectionContextInfo where AttributeName = 'Username')

but #ConnectionContextInfo is no longer available in the context.

I have put a SQL profiler against the database, to check what is happening:

  • temporary table is created successfully using a certain SPID
  • procedure is called using the same SPID

Why is temporary table not available within the procedure scope?

In T-SQL doing the following works:

  • create a temporary table
  • call a procedure that needs data from that particular temporary table
  • temporary table is dropped only explicitly or after current scope ends

Thanks.

解决方案

MINOR ISSUE: I am going to assume for the moment that the code posted in the Question isn't the full piece of code that is running. Not only are there variables used that we don't see getting declared (e.g. AllowInsertConnectionContextInfo), but there is a glaring omission in the setConnectionContextInfo method: the command object is created but never is its CommandText property set to commandBuilder.ToString(), hence it appears to be an empty SQL batch. I'm sure that this is actually being handled correctly since 1) I believe submitting an empty batch will generate an exception, and 2) the question does mention that the temp table creation appears in the SQL Profiler output. Still, I am pointing this out as it implies that there could be additional code that is relevant to the observed behavior that is not shown in the question, making it more difficult to give a precise answer.

THAT BEING SAID, as mentioned in @Vladimir's fine answer, due to the query running in a sub-process (i.e. sp_executesql), local temporary objects -- tables and stored procedures -- do not survive the completion of that sub-process and hence are not available in the parent context.

Global temporary objects and permanent/non-temporary objects will survive the completion of the sub-process, but both of those options, in their typical usage, introduce concurrency issues: you would need to test for the existence first before attempting to create the table, and you would need a way to distinguish one process from another. So these are not really a great option, at least not in their typical usage (more on that later).

Assuming that you cannot pass in any values into the Stored Procedure (else you could simply pass in the username as @Vladimir suggested in his answer), you have a few options:

  1. The easiest solution, given the current code, would be to separate the creation of the local temporary table from the INSERT command (also mentioned in @Vladimir's answer). As previously mentioned, the issue you are encountering is due to the query running within sp_executesql. And the reason sp_executesql is being used is to handle the parameter @Username. So, the fix could be as simple as changing the current code to be the following:

    string _Command = @"
     CREATE TABLE #ConnectionContextInfo(
     AttributeName VARCHAR(64) PRIMARY KEY, 
     AttributeValue VARCHAR(1024)
     );";

using (var command = connection.CreateCommand())
{
    command.CommandText = _Command;
    command.ExecuteNonQuery();
}

_Command = @"
     INSERT INTO #ConnectionContextInfo VALUES ('Username', @Username);
");

using (var command = connection.CreateCommand())
{
    command.CommandText = _Command;

    // do not use AddWithValue()!
    SqlParameter _UserName = new SqlParameter("@Username", SqlDbType.NVarChar, 128);
    _UserName.Value = username;
    command.Parameters.Add(_UserName);

    command.ExecuteNonQuery();
}

    Please note that temporary objects -- local and global -- cannot be accessed in T-SQL User-Defined Functions or Table-Valued Functions.

  2. A better solution (most likely) would be to use CONTEXT_INFO, which is essentially session memory. It is a VARBINARY(128) value but changes to it survive any sub-process since it is not an object. Not only does this remove the current complication you are facing, but it also reduces tempdb I/O considering that you are creating and dropping a temporary table each time this process runs, and doing an INSERT, and all 3 of those operations are written to disk twice: first in the Transaction Log, then in the data file. You can use this in the following manner:

    string _Command = @"
    DECLARE @User VARBINARY(128) = CONVERT(VARBINARY(128), @Username);
    SET CONTEXT_INFO @User;
     ";

using (var command = connection.CreateCommand())
{
    command.CommandText = _Command;

    // do not use AddWithValue()!
    SqlParameter _UserName = new SqlParameter("@Username", SqlDbType.NVarChar, 128);
    _UserName.Value = username;
    command.Parameters.Add(_UserName);

    command.ExecuteNonQuery();
}

    And then you get the value within the Stored Procedure / User-Defined Function / Table-Valued Function / Trigger via:

    DECLARE @Username NVARCHAR(128) = CONVERT(NVARCHAR(128), CONTEXT_INFO());

    That works just fine for a single value, but if you need multiple values, or if you are already using CONTEXT_INFO for another purpose, then you either need to go back to one of the other methods described here, OR, if using SQL Server 2016 (or newer), you can use SESSION_CONTEXT, which is similar to CONTEXT_INFO but is a HashTable / Key-Value pairs.

    Another benefit of this approach is that CONTEXT_INFO (at least, I haven't yet tried SESSION_CONTEXT) is available in T-SQL User-Defined Functions and Table-Valued Functions.

  3. Finally, another option would be to create a global temporary table. As mentioned above, global objects have the benefit of surviving sub-processes, but they also have the drawback of complicating concurrency. A seldom-used to get the benefit without the drawback is to give the temporary object a unique, session-based name, rather than add a column to hold a unique, session-based value. Using a name that is unique to the session removes any concurrency issues while allowing you to use an object that will get automatically cleaned up when the connection is closed (so no need to worry about a process that creates a global temporary table and then runs into an error before completing, whereas using a permanent table would require cleanup, or at least an existence check at the beginning).

    Keeping in mind the restriction that we cannot pass any value into the Stored Procedure, we need to use a value that already exists at the data layer. The value to use would be the session_id / SPID. Of course, this value does not exist in the app layer, so it has to be retreived, but there was no restriction placed on going in that direction.

    int _SessionId;

using (var command = connection.CreateCommand())
{
    command.CommandText = @"SET @SessionID = @@SPID;";

    SqlParameter _paramSessionID = new SqlParameter("@SessionID", SqlDbType.Int);
    _paramSessionID.Direction = ParameterDirection.Output;
    command.Parameters.Add(_UserName);

    command.ExecuteNonQuery();

    _SessionId = (int)_paramSessionID.Value;
}

string _Command = String.Format(@"
  CREATE TABLE ##ConnectionContextInfo_{0}(
    AttributeName VARCHAR(64) PRIMARY KEY, 
    AttributeValue VARCHAR(1024)
  );

  INSERT INTO ##ConnectionContextInfo_{0} VALUES('Username', @Username);", _SessionId);

using (var command = connection.CreateCommand())
{
    command.CommandText = _Command;

    SqlParameter _UserName = new SqlParameter("@Username", SqlDbType.NVarChar, 128);
    _UserName.Value = username;
    command.Parameters.Add(_UserName);

    command.ExecuteNonQuery();
}

    And then you get the value within the Stored Procedure / Trigger via:

    DECLARE @Username NVARCHAR(128),
        @UsernameQuery NVARCHAR(4000);

SET @UsernameQuery = CONCAT(N'SELECT @tmpUserName = [AttributeValue]
     FROM ##ConnectionContextInfo_', @@SPID, N' WHERE [AttributeName] = ''Username'';');

EXEC sp_executesql
  @UsernameQuery,
  N'@tmpUserName NVARCHAR(128) OUTPUT',
  @Username OUTPUT;

    Please note that temporary objects -- local and global -- cannot be accessed in T-SQL User-Defined Functions or Table-Valued Functions.

  4. Finally, it is possible to use a real / permanent (i.e. non-temporary) Table, provided that you include a column to hold a value specific to the current session. This additional column will allow for concurrent operations to work properly.

    You can create the table in tempdb (yes, you can use tempdb as a regular DB, doesn't need to be only temporary objects starting with # or ##). The advantages of using tempdb is that the table is out of the way of everything else (it is just temporary values, after all, and doesn't need to be restored, so tempdb using SIMPLE recovery model is perfect), and it gets cleaned up when the Instance restarts (FYI: tempdb is created brand new as a copy of model each time SQL Server starts).

    Just like with Option #3 above, we can again use the session_id / SPID value since it is common to all operations on this Connection (as long as the Connection remains open). But, unlike Option #3, the app code doesn't need the SPID value: it can be inserted automatically into each row using a Default Constraint. This simplies the operation a little.

    The concept here is to first check to see if the permanent table in tempdb exists. If it does, then make sure that there is no entry already in the table for the current SPID. If it doesn't, then create the table. Since it is a permanent table, it will continue to exist, even after the current process closes its Connection. Finally, insert the @Username parameter, and the SPID value will populate automatically.

    // assume _Connection is already open
using (SqlCommand _Command = _Connection.CreateCommand())
{
    _Command.CommandText = @"
       IF (OBJECT_ID(N'tempdb.dbo.Usernames') IS NOT NULL)
       BEGIN
          IF (EXISTS(SELECT *
                     FROM   [tempdb].[dbo].[Usernames]
                     WHERE  [SessionID] = @@SPID
                    ))
          BEGIN
             DELETE FROM [tempdb].[dbo].[Usernames]
             WHERE  [SessionID] = @@SPID;
          END;
       END;
       ELSE
       BEGIN
          CREATE TABLE [tempdb].[dbo].[Usernames]
          (
             [SessionID]  INT NOT NULL
                          CONSTRAINT [PK_Usernames] PRIMARY KEY
                          CONSTRAINT [DF_Usernames_SessionID] DEFAULT (@@SPID),
             [Username]   NVARCHAR(128) NULL,
             [InsertTime] DATETIME NOT NULL
                          CONSTRAINT [DF_Usernames_InsertTime] DEFAULT (GETDATE())
          );
       END;

       INSERT INTO [tempdb].[dbo].[Usernames] ([Username]) VALUES (@UserName);
            ";

    SqlParameter _UserName = new SqlParameter("@Username", SqlDbType.NVarChar, 128);
    _UserName.Value = username;
    command.Parameters.Add(_UserName);

    _Command.ExecuteNonQuery();
}

    And then you get the value within the Stored Procedure / User-Defined Function / Table-Valued Function / Trigger via:

    SELECT [Username]
FROM   [tempdb].[dbo].[Usernames]
WHERE  [SessionID] = @@SPID;

    Another benefit of this approach is that permanent tables are accessible in T-SQL User-Defined Functions and Table-Valued Functions.

这篇关于使用临时表的SQL Server连接上下文不能在用SqlDataAdapter.Fill调用的存储过程中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆