存储过程基础问题相关

ExeProcDataTable   查表
ExeProcScalar      查询
ExeProcNonQuery    更新，删除,增加
ExeProcDataReader 
ExeProcXmlDocument  生成XML文档

<asp:BoundColumn DataField="UsesDate" DataFormatString="{0:yyyy-MM-dd}" HeaderText="使用时间">

查询以前的
select PublishTime
from bbsNote
where datediff(day,PublishTime,getdate())>1

查询昨天的
select PublishTime
from bbsNote
where datediff(day,PublishTime,getdate())=1

查询当天或一段时间之内（传递参数前一天，后一天）
where PublishTime >cast('2006-06-05' as datetime) and  PublishTime <cast('2006-06-07' as datetime)

DateTime DayNow=Convert.ToDateTime(System.DateTime.Now.ToShortDateString());
    string BeginDate=DayNow.AddDays(-1).ToString(); //昨天
    string EndDate=DayNow.AddDays(1).ToString();//明天
    int n=Method.Index.BBS_Today_GetCount_DayBetween(Convert.ToString(DayNow),EndDate);//今天的统计
    //(BeginDate,Convert.ToString(DayNow));//昨天的统计
    Response.Write(Convert.ToString(n));

public static int BBS_Today_GetCount_DayBetween(string BeginDate,string EndDate)
  {
   object []obj={null,0,BeginDate,EndDate};
   Database.ExeProcDataTable("p_bbs_TitleList_Note_GetCount_Daybetween",obj);
   return (int)obj[1];
  }

select isnull((varTable.TotalCount),0)as TotalCount,(select top 1 Name from table where ID=a._ID)+a.OrganizationName as MyName
from table01 a

case bbsBoard.BoardIsLocked when 0 then'开放' when 1 then'锁定'end as State

表复制
insert into teLevel( LevelName, FullScore, Evaluate_ID, IsModel_ID)
select  LTempletName, LTempletScore, 1,IsModel_ID
from   teLTemplet
where IsModel_ID=1

ALTER PROCEDURE [dbo].[p_abc_edf]
(
  @ID int,
  @output int output
)

AS

if @ID in (select top 1 ID from table)
set @output=1
else
set @output=0

两个表合成一表查
select VarTable.*,table1.*
from
(
    select -1 as ReplyNote_ID,ID
    from Note
    where  Recycle=0
    Union all
    select _ID
    from ReplyNote
    where Wuditing=1 and Recycle=0
) as VarTable
left join table1 on table1 ID=VarTable.ID

模糊查询,数据类型不同
CREATE PROCEDURE [dbo].[p_ab_Option_GetStat]
(
 @Item_ID int
)
AS
 select Option_ID,OptionName,
  (select count(*) from suResearch  where Item_ID=a.Item_ID and Answer like '%,'+cast(Option_ID as varchar)+',%' or Answer like cast(Option_ID as varchar)+',%') as OptionCount
 from suOption a
 left join suItem on suItem.Item_ID=a.Item_ID
    where a.Item_ID=@Item_ID  order by Option_ID

总分或平均分
avg(cast(ScoreCount as float))as avgScore

返回新增记录的ID
insert into ttable (Title,P_ID,UserName)
 values(@Title,@P_ID,@UserName)
set @TeachPlan_ID=@@identity