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];
        }