给数据库传递组合字符串参数，类似数组的解决方法

      在实际项目中，常会有这样的操作，给定一个表格，然后每一条记录有一个选择框，然后就根据选择的记录进行相应的操作，这种情形下，无法预计选择多少个，如果使用组合字串传给SQL，极有可能因长度不够而不能全部操作选定的记录。

     我的解决办法：因为我通常习惯使用ID来做为记录标识，就建立一个临时的表TBTEMID，然后在程序里将选择的记录ID依次加入这个表中，再使用游标遍历这个表执行相应的操作，操作完毕后将这个表中的记录清除。

      一个完整操作的示例代码，主要由SQL语句组成

string sql = "--DECLARE @OPERATOR VARCHAR(20) /r/n" + "--SELECT @OPERATOR='ADMIN' /r/n" + "--INSERT INTO TBTEMIDS(ID) /r/n" + "--SELECT 103 /r/n" + "--UNION /r/n" + "--SELECT 98 /r/n" + "--UNION /r/n" + "--SELECT 99 /r/n" + "--UNION /r/n" + "--SELECT 104 /r/n" + "--UNION /r/n" + "--SELECT 105 /r/n" + "DECLARE @ID INT /r/n" + "DECLARE ABC CURSOR FOR /r/n" + "SELECT ID FROM TBTEMIDS /r/n" + "OPEN ABC /r/n" + "FETCH NEXT FROM ABC INTO @ID /r/n" + "WHILE @@FETCH_STATUS=0 /r/n" + "BEGIN /r/n" + " /r/n" + " INSERT INTO TBTEMRECIPE(SICKID,NUMS,PRICE,MONEY,RECIPEID) /r/n" + " SELECT SICKID,NUMS,PRICE,MONEY,ID FROM TBRECIPE WHERE ID=(SELECT RECIPEID FROM TBRECIPEEXECUTE WHERE ID=@ID) /r/n" + " UPDATE TBRECIPEEXECUTE SET ISEXECUTE=1 WHERE ID=@ID /r/n" + " FETCH NEXT FROM ABC INTO @ID /r/n" + "END /r/n" + "CLOSE ABC /r/n" + "DEALLOCATE ABC /r/n" + "TRUNCATE TABLE TBTEMIDS /r/n" + "DECLARE @SICKID INT /r/n" + "DECLARE @NUMS INT /r/n" + "DECLARE @PRICE INT /r/n" + "DECLARE @MONEY INT /r/n" + "DECLARE @RECIPEID INT /r/n" + "DECLARE @TEMSICK INT /r/n" + "DECLARE @TEMMONEY INT /r/n" + "DECLARE @BILLNUMS INT /r/n" + "SELECT @BILLNUMS=0 /r/n" + "SELECT @TEMMONEY=0 /r/n" + "--取排序号 /r/n" + "DECLARE @NUMBER INT /r/n" + "SELECT TOP 1 @NUMBER=NUMBER FROM TBSICKCHARGETOTALBILL WHERE DATEDIFF(d,CREATETIME,GETDATE())=0 ORDER BY NUMBER DESC /r/n" + "IF(@NUMBER IS NULL) /r/n" + " SELECT @NUMBER=1 /r/n" + "ELSE /r/n" + " SELECT @NUMBER=@NUMBER+1 /r/n" + "DECLARE @STRNUMBER VARCHAR(4) /r/n" + "SELECT @STRNUMBER=CONVERT(VARCHAR(4),@NUMBER) /r/n" + "DECLARE @TEMSN VARCHAR(20) /r/n" + "DECLARE @DATE DATETIME /r/n" + "SELECT @DATE=GETDATE() /r/n" + "DECLARE @YEAR INT /r/n" + "DECLARE @MONTH INT /r/n" + "DECLARE @DAY INT /r/n" + "SELECT @YEAR=YEAR(@DATE) /r/n" + "SELECT @MONTH=MONTH(@DATE) /r/n" + "SELECT @DAY=DAY(@DATE) /r/n" ......"; if (ids != "") { string tem = "INSERT INTO TBTEMIDS(ID) "; string[] idnum = ids.Split(','); for (int i = 0; i < idnum.Length; i++) { tem += " /r/nSELECT " + idnum[i] + " /r/n"; if (i < idnum.Length - 1) { tem += "UNION"; } } sql = tem + sql; SqlParameter[] sps = new SqlParameter[1]; sps[0] = SQLDB.CreateParameter("@Operator", SqlDbType.VarChar, admin, 20, ParameterDirection.Input); SQLDB.ExecuteNonQuery(sql, sps); }

实际是这个SQL语句有几百行，我觉得如果将这些语句写成存储过程，虽然调用起来是方便些，可不太安全，首先一些业务逻辑暴露了，其次是担心客户方的管理员是个半吊子乱动给搞坏了就不好收拾了，干脆就都写进程序里，效率应该影响不大，一个语句查找修改十几个表的记录，呵呵，也只是在这个项目里才开始用。