sql server存储过程的笔记记录

1·测试代码，自我记录知识点方便回忆应用

USE [AIS20111111172448]//数据库的名称

GO
/****** Object:  StoredProcedure [dbo].[aaaamyfirst]    Script Date: 03/15/2017 10:22:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE  [dbo].[aaaamyfirst]//创建你的存储过程名 PROCEDURE  可选择执行是否需要参数
@fbillno varchar(20)=null,
@fname nvarchar(20)=null,
@starttime datetime=null,
@endtime datetime=null
AS
BEGIN
declare @sql varchar(2000)//注意这边的长度问题，有时候可选择print全部的select语句，看看是不是长度不够而引起报错
declare @addsql varchar(1000)
set @sql='Select
a.FBillNo as 销售订单编号,
kh.FName as 客户名称,
a.FDate as 订单日期, 
yw.FName as 业务员,
wl.FNumber as 产品代码,
wl.FName as 产品名称,
wl.FModel as 规格型号,
mu.FName as 单位,
b.FAuxQty as 订单数量,
sum(te.FQty) as "发货申请数量（累计）",
sum(c.FAuxQty) as "发货通知数量（累计）",
sum(te.FAmount) as "发货申请金额（累计）",
sum(c.FAmount) as "发货通知金额（累计）"
FROM SEOrder a
inner join SEOrderEntry b on a.FInterID=b.FInterID
inner join t_BOS250000001Entry2 te on te.FID_SRC=b.FInterID
and te.FEntryID_SRC=b.FEntryID//两表多个匹配对象可以用and继续连接
left join t_Organization kh on kh.FItemID=a.FCustID
left join t_Emp yw on yw.FItemID=a.FEmpID
left join t_ICItem wl on wl.FItemID=b.FItemID
left join t_MeasureUnit mu on mu.FItemID=b.FUnitID
left join SEOutStockEntry c on c.FInterID=b.FInterID
where 1=1'
set @addsql=' '
end
if (@fbillno is not null)
BEGIN
set @addsql=@addsql+' and a.FBillNo=''' +@fbillno + ''''
END
if (@fname is not null)
BEGIN
set @addsql=@addsql+' and kh.FName='''+@fname + ''''
END
if (@starttime is not null)
BEGIN
set @addsql=@addsql+' and a.FDate>='''+convert(varchar(20),@starttime,120) + ''''
print @addsql
END
if (@endtime is not null)
BEGIN
set @addsql=@addsql+' and a.FDate<='''+convert(varchar(20),@endtime,120) + ''''//拼接时遇到报错，要将字符串包裹在‘’中注意引号规则
print @addsql
END
BEGIN
set @sql=@sql+@addsql+' group by a.FBillNo ,//多表操作用到了函数后，最后需要进行group操作
kh.FName ,
a.FDate , 
yw.FName ,
wl.FNumber ,
wl.FName ,
wl.FModel ,
mu.FName ,
b.FAuxQty'
exec(@sql)//exec（）放一个变量执行
end