sql字符串函数说明

高中物理教学艺术

sql字符串函数说明.txt
死亡循环 淡定

如果有一天晚上，你从家里出去，你发现楼梯一直往下延伸，永远没有尽头，你永远找不到出口，你会怎么想？
爱情没有永恒，只有彼此的慰藉和责任。

{0:P}百分号格式化字符串
A brother is only a fame//legend哥只是个传说
CREATE PROCEDURE proc_parameterNumber
@SingleNumberId varchar(10),
@SingleNumber varchar(10) output
AS
BEGIN
if not exists (select * from SingleNumber where SingleNumberId=@SingleNumberId)
begin
set @SingleNumber=''
return
end
else
begin
declare @Prefixion varchar(10)
declare @Number bigint
declare @Width tinyint
select @Prefixion=Prefixion,@Number=Number,@Width=Width from SingleNumber where SingleNumberId=@SingleNumberId
update SingleNumber set Number=Number+1 where SingleNumberId=@SingleNumberId
declare @currentNumber varchar(100)
@currentNumber='00000000000000000000000'+@Number
set @SingleNumber=@currentNumber
end
END
GO


/// <summary>
/// 添加项目对应的课程
/// </summary>
/// <param name="courseIds">课程Id数组</param>
/// <param name="projectId">项目id</param>
public static void AddCourseSettings(int[] courseIds, string projectId)
{
SqlParameterList splist = new SqlParameterList();
splist.AddAParameter("@ProjectId", SqlDbType.Char, 8, projectId);
string strSql = "";
for (int i = 0; i < courseIds.Length; i++)
{
splist.AddAParameter("@CourseId" + i.ToString(), SqlDbType.Int, courseIds[i]);
strSql += string.Format(@"
if exists(select * from CourseSetting where ProjectId=@ProjectId and CourseId=@CourseId{0})
return;
else
insert into CourseSetting(ProjectId,CourseId) values (@ProjectId,@CourseId{0});
end", i);
DataAccessor.ExecuteSqlOnly(strSql, false, splist);
}
}







declare @PayAbleTuition int --应交费用
select @PayAbleTuition=PayAbleTuition from PayCondition where PayConditionId=1

if not exists (select TuitionId from Tuition where PayConditionId=1)
--预交 一次交清 保留 @TuitionAmount：当前所交费用
begin
if(@TuitionAmount<@PayAbleTuition)
select '预交'
else if(@TuitionAmount=@PayAbleTuition)
select '一次交清'
else
select '保留'
end
else
--补交 补齐 保留
begin
declare @PaidTuition int --已交费用
select @PaidTuition=sum(TuitionAmount) from Tuition where PayConditionId=1
if(@PaidTuition+@TuitionAmount<@PayAbleTuition)
select '补交'
else if(@PaidTuition+@TuitionAmount=@PayAbleTuition)
select '补齐'
else
select '保留'
end





winForm窗体中，快捷键是(&字母) 如：显示(&D) 则表示"显示"的快捷键是字母D


datediff(datepart,startTime,endTime)
说明：(endtime-starttime的结果转化为datepart)
该函数返回int类型
如：
select datediff(ss,'2010-4-14 10:30:20','2010-4-15 10:30