这是我的存储过程:create Procedure proc_delete_Users
@UsersId varchar(100)
AS
begin Transaction
declare @errorSum int
set @errorSum = 0
exec('delete from OrderBook where orderId in (select Id from Orders where UserId in('+@UsersId+'))')
set @errorSum = @errorSum + @@error
exec('delete from Orders where UserId in('+@UsersId+')')
set @errorSum = @errorSum + @@error
exec('delete from Users where id in('+@UsersId+')')
set @errorSum = @errorSum + @@error
if(@errorSum <> 0)
begin
rollback transaction
end
else
begin
commit transaction
end
go这是我调用存储过程的方法:public static int DeleteUserByIds(string id)
{
string sql = "proc_delete_Users";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@UsersId",SqlDbType.VarChar,100)
};
param[0].Value = id; return DBHelper.ExecuteNonQuery(sql, param);
}这是我的DBHelper中ExecuteNonQuery方法 public static int ExecuteNonQuery(string sql, params SqlParameter[] param)
{
int row = 0;
try
{
cmd = new SqlCommand(sql, Connect);
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
row = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return row;
}最后运行的时候,会出现这个错误第 1 行: 'proc_delete_Users' 附近有语法错误。
@UsersId varchar(100)
AS
begin Transaction
declare @errorSum int
set @errorSum = 0
exec('delete from OrderBook where orderId in (select Id from Orders where UserId in('+@UsersId+'))')
set @errorSum = @errorSum + @@error
exec('delete from Orders where UserId in('+@UsersId+')')
set @errorSum = @errorSum + @@error
exec('delete from Users where id in('+@UsersId+')')
set @errorSum = @errorSum + @@error
if(@errorSum <> 0)
begin
rollback transaction
end
else
begin
commit transaction
end
go这是我调用存储过程的方法:public static int DeleteUserByIds(string id)
{
string sql = "proc_delete_Users";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@UsersId",SqlDbType.VarChar,100)
};
param[0].Value = id; return DBHelper.ExecuteNonQuery(sql, param);
}这是我的DBHelper中ExecuteNonQuery方法 public static int ExecuteNonQuery(string sql, params SqlParameter[] param)
{
int row = 0;
try
{
cmd = new SqlCommand(sql, Connect);
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
row = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return row;
}最后运行的时候,会出现这个错误第 1 行: 'proc_delete_Users' 附近有语法错误。
exec('delete from OrderBook where orderId in (select Id from Orders where UserId in('''+@UsersId+'''))')
为啥一定要用ExecuteNonQuery呢执行存储过程应该要设置cmd.CommandType = CommandType.StoredProcedure;
exec里面的sql在in里面没有用字符串括起来。declare @usersid varchar(100)
set @usersid='adsf'
print'delete from OrderBook where orderId in (select Id from Orders where UserId in('+@UsersId+'))'结果:
delete from OrderBook where orderId in (select Id from Orders where UserId in(adsf))
实际测试结果,如果id是int型的是可以正确执行的,而且可以执行以逗号分隔的形式
字符串未进行测试, oracle里也是可以的,不知道sqlserver里怎么样
谢谢帮助