sql杂（今天上班遇到的问题）

select (select count(*) from YM_products where 1=1 and oldOrderDate >='2010-05-09' and oldOrderDate <'20101018' and jhStatus='缺货')
as shortage,
(select count(*) from YM_products where 1=1 and oldOrderDate >='2010-05-09' and oldOrderDate <'20101018' and Status>0 )
as [normal],
 cast((select count(*) from YM_products where 1=1 and oldOrderDate >='2010-05-09' and oldOrderDate <'20101018' and jhStatus='缺货')*1.0*100/(select count(*) from YM_products where 1=1 and oldOrderDate >='2010-05-09' and oldOrderDate <'20101018' and Status>0 ) as numeric(10, 2))
as rate

 

 

1--------截取字符串，前台绑定只要取两位小数  可采用

select cast(13.123456789 as numeric(10, 2))

   后台：

 protected void BindDataList()
    {
        string startDate = "0";
        string endDate = "0";
        try
        {
            string sqlwhere = " 1=1 ";
            string orderby = " order by id desc";

            //上传日期
            if (this.ipstartDate.Value.Trim() != "")
            {
                startDate = this.ipstartDate.Value;
                sqlwhere += " and oldOrderDate >='" + startDate + "' ";
            }
            if (this.ipendDate.Value.Trim() != "")
            {
                DateTime d = DateTime.Parse(this.ipendDate.Value).AddDays(1);
                string dMonth = d.Month >= 10 ? d.Month.ToString() : "0" + d.Month.ToString();
                string dDay = d.Day >= 10 ? d.Day.ToString() : "0" + d.Day.ToString();
                endDate = d.Year.ToString() + dMonth + dDay;
                sqlwhere += " and oldOrderDate <'" + endDate + "' ";
            }

            if (ipSupplierCode.Value.Trim() != "")
            {
                sqlwhere += " and SupplierCode='" + ipSupplierCode.Value + "'";
            }

            DataSet ds = new DataSet();
            string ds_sql = "select (select count(*) from YM_products where" + sqlwhere + " and jhStatus='缺货') as shortage,(select count(*) from YM_products where " + sqlwhere + " and Status>0 )as [normal], cast((select count(*) from YM_products where" + sqlwhere + " and jhStatus='缺货')*1.0*100/(select count(*) from YM_products where " + sqlwhere + " and Status>0 )*1.0 as numeric(10, 2)) as rate";
            //Response.Write(ds_sql);
            ds = DB.getdataset(ds_sql);
            this.DataList1.DataSource = ds;
            this.DataList1.DataBind();
        }
        catch
        {
            MessageBox.ShowAndClose(this.Page, "初始化失败~请重新进入~");
        }

    }

自己在工作中常用的存储过程：
　　   exec sp_help pr_Pager      运行结果如下：

        exec sp_helptext pr_Pager   运行结果如下：

         exec sp_MShelpcolumns _webEmail                   运行结果如下：