昨天在.NET区发了一个关于SQL的帖子无人问津

Question

                            sql = @"insert into contact(contactName,nickName,address,city,state,crossStreets,postalCode,otRestaurantId,otNeighborhood,otCuisine,otPrice,colorCode,version,sharedContactId,enteredBy,entryDate)
                    values(@contactName,@nickName,@address,@city,@state,@crossStreets,@postalCode,@otRestaurantId,@otNeighborhood,@otCuisine,@otPrice,@colorCode,@version,@sharedContactId,@enteredBy,getDate());
                    select @@nestlevel,scope_identity()";
                            cmd = new SqlCommand(sql, siteconnc);
                            cmd.Transaction = sqlTran;
                            cmd.Parameters.AddWithValue("@contactName", row["primaryName"]);
                            cmd.Parameters.AddWithValue("@nickName", row["phoneNumber"]);
                            cmd.Parameters.AddWithValue("@address", row["address"]);
                            cmd.Parameters.AddWithValue("@city", row["city"]);
                            cmd.Parameters.AddWithValue("@state", row["state"]);
                            cmd.Parameters.AddWithValue("@crossStreets", row["crossStreet"]);
                            cmd.Parameters.AddWithValue("@postalCode", row["postalCode"]);
                            cmd.Parameters.AddWithValue("@otRestaurantId", row["openTableId"]);
                            cmd.Parameters.AddWithValue("@otNeighborhood", row["neighborhood"]);
                            cmd.Parameters.AddWithValue("@otCuisine", row["cuisine"]);
                            cmd.Parameters.AddWithValue("@otPrice", row["otprice"]);
                            cmd.Parameters.AddWithValue("@colorCode", "normal");
                            cmd.Parameters.AddWithValue("@version", 1);
                            cmd.Parameters.AddWithValue("@sharedContactId", row["contactId"]);
                            cmd.Parameters.AddWithValue("@enteredBy", "Gold Key Solutions");
                            string contactId = cmd.ExecuteScalar().ToString();
某人在.NET里使用这段代码，发现contactId竟然是等于2.有人知道这是为什么么求教

Answer 1

把这个 select @@nestlevel,scope_identity()
改成
select scope_identity()

Answer 2

郁闷的要死啊，根本不知道这个@@nestlevel怎么被修改的
如果在查询分析器里执行这个代码的话@@nestlevel=0

Answer 3

2楼的大哥我是为了验证@@nestlevel的值 才加上的，你的好叫我去掉

Answer 4

   siteConnectionString = siteConnectionString.Replace("{0}", siteModel.HostName);
            siteconnc  = new SqlConnection(siteConnectionString);
            Con = new SqlConnection(siteConnectionString);
            siteconnc.Open();
            sqlTran = siteconnc.BeginTransaction();
            if (tableList.Count > 0)
            {
                                foreach (DataTable dataTable in tableList)
                {
                    foreach (DataRow row in dataTable.Rows)
                    {
                        sql = "select count(*) from contact where sharedContactId = @sharedContactId";
                        cmd = new SqlCommand(sql, siteconnc);
                        cmd.Transaction = sqlTran;
                        cmd.Parameters.AddWithValue("@sharedContactId", row["contactId"]);
                        string count = cmd.ExecuteScalar().ToString();                        if (Convert.ToInt32(count) == 0)
                        {
                            sql = @"insert into contact(contactName,nickName,address,city,state,crossStreets,postalCode,otRestaurantId,otNeighborhood,otCuisine,otPrice,colorCode,version,sharedContactId,enteredBy,entryDate)
                    values(@contactName,@nickName,@address,@city,@state,@crossStreets,@postalCode,@otRestaurantId,@otNeighborhood,@otCuisine,@otPrice,@colorCode,@version,@sharedContactId,@enteredBy,getDate());
                    select @@nestlevel,scope_identity()";
                            cmd = new SqlCommand(sql, siteconnc);
                            cmd.Transaction = sqlTran;
                            cmd.Parameters.AddWithValue("@contactName", row["primaryName"]);
                            cmd.Parameters.AddWithValue("@nickName", row["phoneNumber"]);
                            cmd.Parameters.AddWithValue("@address", row["address"]);
                            cmd.Parameters.AddWithValue("@city", row["city"]);
                            cmd.Parameters.AddWithValue("@state", row["state"]);
                            cmd.Parameters.AddWithValue("@crossStreets", row["crossStreet"]);
                            cmd.Parameters.AddWithValue("@postalCode", row["postalCode"]);
                            cmd.Parameters.AddWithValue("@otRestaurantId", row["openTableId"]);
                            cmd.Parameters.AddWithValue("@otNeighborhood", row["neighborhood"]);
                            cmd.Parameters.AddWithValue("@otCuisine", row["cuisine"]);
                            cmd.Parameters.AddWithValue("@otPrice", row["otprice"]);
                            cmd.Parameters.AddWithValue("@colorCode", "normal");
                            cmd.Parameters.AddWithValue("@version", 1);
                            cmd.Parameters.AddWithValue("@sharedContactId", row["contactId"]);
                            cmd.Parameters.AddWithValue("@enteredBy", "Gold Key Solutions");
                            string contactId = cmd.ExecuteScalar().ToString();

Answer 5

@@NESTLEVEL 
返回对本地服务器上执行的当前存储过程的嵌套级别（初始值为 0）。你用到这里干什么

Answer 6

7楼啊我自然有用处的了。因为原先写着代码的人在contact表里有个触发器的，就是用这个@@nestlevel来判断的

Answer 7

CREATE PROC usp_NestLevelValues AS
    SELECT @@NESTLEVEL AS 'Current Nest Level';
EXEC ('SELECT @@NESTLEVEL AS OneGreater'); 
EXEC sp_executesql N'SELECT @@NESTLEVEL as TwoGreater' ;
GO
EXEC usp_NestLevelValues;
GO
 
/*
下面是结果集： Current Nest Level 
------------------ 
1(1 row(s) affected)OneGreater  
----------- 
2(1 row(s) affected)TwoGreater  
----------- 
3(1 row(s) affected)
 
*/

Answer 8

如果在查询分析器里执行这个SQL，那么@@nestlevel就是0这也好理解，但是就是在这段代码里@@nestlevel竟然是2

Answer 9

大哥，这个是用于存储过程的判断，普通语句后面跟一个都会返回0
如：
select * from tb
insert into tb select 1
select @@NESTLEVEL

Answer 10

不知道你为什么要做这样的测试
可能是ExecuteScalar导致的吧 试试ExecuteNonQuery方法

Answer 11

我想告诉你的事ms说了 @@NESTLEVEL用于存储过程的判断

Answer 12

因为我在当前SQL执行的时候返回是2，该插入表的触发器里该参数就是3，因为被嵌套一层了

Answer 13

同样肯定不是ExecuteScalar这个导致的，我已经验证过了

Answer 14

 siteConnectionString = siteConnectionString.Replace("{0}", siteModel.HostName);
            siteconnc  = new SqlConnection(siteConnectionString);
            Con = new SqlConnection(siteConnectionString);
            siteconnc.Open();
            sqlTran = siteconnc.BeginTransaction();
            if (tableList.Count > 0)
            {
                                foreach (DataTable dataTable in tableList)
                {
                    foreach (DataRow row in dataTable.Rows)
                    {
                        sql = "select count(*) from contact where sharedContactId = @sharedContactId";
                        cmd = new SqlCommand(sql, siteconnc);
                        cmd.Transaction = sqlTran;
                        cmd.Parameters.AddWithValue("@sharedContactId", row["contactId"]);
                        string count = cmd.ExecuteScalar().ToString();                        if (Convert.ToInt32(count) == 0)
                        {
                            sql = @"insert into contact(contactName,nickName,address,city,state,crossStreets,postalCode,otRestaurantId,otNeighborhood,otCuisine,otPrice,colorCode,version,sharedContactId,enteredBy,entryDate)
                    values(@contactName,@nickName,@address,@city,@state,@crossStreets,@postalCode,@otRestaurantId,@otNeighborhood,@otCuisine,@otPrice,@colorCode,@version,@sharedContactId,@enteredBy,getDate());
                    select @@nestlevel,scope_identity()";
                            cmd = new SqlCommand(sql, siteconnc);
                            cmd.Transaction = sqlTran;
                            cmd.Parameters.AddWithValue("@contactName", row["primaryName"]);
                            cmd.Parameters.AddWithValue("@nickName", row["phoneNumber"]);
                            cmd.Parameters.AddWithValue("@address", row["address"]);
                            cmd.Parameters.AddWithValue("@city", row["city"]);
                            cmd.Parameters.AddWithValue("@state", row["state"]);
                            cmd.Parameters.AddWithValue("@crossStreets", row["crossStreet"]);
                            cmd.Parameters.AddWithValue("@postalCode", row["postalCode"]);
                            cmd.Parameters.AddWithValue("@otRestaurantId", row["openTableId"]);
                            cmd.Parameters.AddWithValue("@otNeighborhood", row["neighborhood"]);
                            cmd.Parameters.AddWithValue("@otCuisine", row["cuisine"]);
                            cmd.Parameters.AddWithValue("@otPrice", row["otprice"]);
                            cmd.Parameters.AddWithValue("@colorCode", "normal");
                            cmd.Parameters.AddWithValue("@version", 1);
                            cmd.Parameters.AddWithValue("@sharedContactId", row["contactId"]);
                            cmd.Parameters.AddWithValue("@enteredBy", "Gold Key Solutions");
                            string contactId = cmd.ExecuteScalar().ToString();
有可能是这里嵌套的原因，呵呵，你把它去掉试试。。

Answer 15

Each time a stored procedure calls another stored procedure or executes managed code by referencing a common language runtime (CLR) routine, type, or aggregate, the nesting level is incremented. When the maximum of 32 is exceeded, the transaction is terminated.When @@NESTLEVEL is executed within a Transact-SQL string, the value returned is 1 + the current nesting level. When @@NESTLEVEL is executed dynamically by using sp_executesql the value returned is 2 + the current nesting level.

Answer 16

Each time a stored procedure calls another stored procedure or executes managed code
 by referencing a common language runtime (CLR) routine, type, or aggregate, the 
nesting level is incremented. When the maximum of 32 is exceeded, the transaction is 
terminated.When @@NESTLEVEL is executed within a Transact-SQL string, the value
 returned is 1 + the current nesting level. When @@NESTLEVEL is executed dynamically
 by using sp_executesql the value returned is 2 + the current nesting level.

Answer 17

楼主你没看到我在26楼贴的内容么？
When @@NESTLEVEL is executed within a Transact-SQL string, the value
returned is 1 + the current nesting level. When @@NESTLEVEL
 is executed dynamically
by using sp_executesql the value returned is 2 + the current nesting level.

Answer 18

再给你翻译一下哦：
每次一个存储过程通过引用公共语言运行时 (CLR) 例程、类型或聚合来调用另一个存储过程或执行托管代码
时，嵌套级别都会增加。超过最大级数 32 时，事务即被终止。在 Transact-SQL 字符串中执行 @@NESTLEVEL 时，返回的值为 1 + 当前嵌套级别。通过使用
 sp_executesql 动态执行 @@NESTLEVEL 时，返回的值为 2 + 当前嵌套级别。

Answer 19

有没有看过是不是在ADO.NET中增加了参数导致的？不加参数的话我记得是0，加了参数会增加nestlevel的

Answer 20

现在我彻底找到原因了，当我们对CMD做addwithvalue操作的时候@@nestlevel 自动变成2了至于为什么会这样我目前无从考证。学习的同学大家相互借鉴一下

Answer 21

执行了2条sql语句?
一条insert 一条select

Answer 22

原因我已经找到就是cmd.Parameters.AddWithValue("@contactName", row["primaryName"]);这个操作只要调用ADDWithValue就会变2