SQL技巧(sp_procedure_params_rowset,SQL中设置数据值为null)

SQL技巧(sp_procedure_params_rowset,SQL中设置数据值为null)

1.在SQL Server数据库中要把某个字段改null? 语句：set xxxx=null; 快捷键：ctrl + 0;

2.得到任意一个存储过程的参数列表 方法1: select c.name from syscolumns c,sysobjects o where o.id=c.id and o.xtype='P' and o.name='your proc name' 方法2: exec sp_procedure_params_rowset @procedure_name = 'storeproc name' 例如： 1)、 select c.name,c.colorder from syscolumns c,sysobjects o where o.id=c.id and o.xtype='P' and o.name='proc_EMS_SearchEquipment' 得到 @type 1 @model 2 @location 3 @description 4 2)、sp_procedure_params_rowset @procedure_name='proc_EMS_SearchEquipment' DBName dbo proc_EMS_SearchEquipment;1 @RETURN_VALUE 0 4 0 NULL 0 3 NULL NULL 10 NULL NULL int int DBName dbo proc_EMS_SearchEquipment;1 @type 1 1 0 NULL 1 3 NULL NULL 10 NULL NULL int int DBName dbo proc_EMS_SearchEquipment;1 @model 2 1 0 NULL 1 129 50 50 NULL NULL NULL varchar varchar DBName dbo proc_EMS_SearchEquipment;1 @location 3 1 0 NULL 1 129 50 50 NULL NULL NULL varchar varchar DBName dbo proc_EMS_SearchEquipment;1 @description 4 1 0 NULL 1 129 100 100 NULL NULL NULL varchar varchar

3.怎样使用该函数 //执行存储过程 public DataSet ExecuteDataSet(string storedProcedure, params object[] param) { SqlCommand selectCommand = this.GenerateCommand(storedProcedure, param); selectCommand.CommandTimeout = 300; DataSet dataSet = null; SqlDataAdapter adapter = new SqlDataAdapter(selectCommand); try { dataSet = new DataSet(); adapter.Fill(dataSet); } catch (Exception exception) { throw exception; } finally { if (!this.UnderTran) { this.Connect.Close(); } } return dataSet; } //将传入的object[]值赋给对应存储过程参数 private SqlCommand GenerateCommand(string cmdstr, params object[] val) { SqlCommand command = new SqlCommand(cmdstr, this.Connect); command.CommandType = CommandType.StoredProcedure; if (!this.UnderTran && (this.Connect.State != ConnectionState.Open)) { this.Connect.Open(); } else { command.Transaction = this.Tran; } if ((val != null) && (val.Length > 0)) { SqlCommand selectCommand = new SqlCommand("sp_procedure_params_rowset", this.Connect); if (this.UnderTran) { selectCommand.Transaction = this.Tran; } selectCommand.CommandType = CommandType.StoredProcedure; selectCommand.Parameters.Add("@procedure_name", cmdstr); DataSet dataSet = new DataSet(); new SqlDataAdapter(selectCommand).Fill(dataSet); DataTable table = dataSet.Tables[0]; table.Rows.RemoveAt(0); for (int i = 0; i < val.Length; i++) { if (val[i] != null) { command.Parameters.Add(table.Rows[i]["PARAMETER_NAME"].ToString(), val[i]); } } } return command; } //调用函数 public DataTable SearchEquipment(int type,string model,string location,string description)//OSR.Entity.EMSData { object[] obj = new object[4]; obj[0] = type; obj[1] = model; obj[2] = location; obj[3] = description; return visitor.ExecuteDataSet("proc_EMS_SearchEquipment", obj).Tables[0]; }