sql笔记



qk_getBaseInfo 2,''
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[qk_getBaseInfo]') AND type in (N'P', N'PC'))
drop proc qk_getBaseInfo
go
create proc qk_getBaseInfo
@type int
,@filter nvarchar(2000)
as
begin
--客户,部门,职员,物料,仓库,仓位,单位,供应商,提货方式,盘点方案;
set nocount on
if @type=0--客户
begin
      select   FNumber  编码,  FName 名称 ,fitemid ID,fischoice=0 from t_Organization
      where fdeleted=0 and (FNumber like '%'+@filter +'%' or FName like '%'+@filter +'%')
      order by FNumber
end
else if @type =1--部门
begin
      select     FNumber 编码,  FName 名称, fitemid ID,fischoice=0 from t_Department
      where fdeleted=0 and (FNumber like '%'+@filter +'%' or FName like '%'+@filter +'%')
      and FNumber like 'N%'
      order by FNumber
end
else if @type =2--职员
begin
    select a.FNumber 编码,a.FName 名称,isnull(b.FNumber,'')+' '  部门编码,isnull(b.FName,'')+' ' 部门名称,a.FItemID ID,isnull(b.FItemID,0) 部门ID,fischoice=0 from t_Emp  a left join t_Department b on a.FDepartmentID=b.FItemID
    where a.FDeleted =0 and (a.FNumber like '%'+@filter +'%' or a.FName  like '%'+@filter +'%'  or b.FNumber like '%'+@filter +'%' or b.FName  like '%'+@filter +'%')
    and a.FItemID >0
    order by b.FNumber,a.FNumber
end
else if @type =3--物料
begin
   select FNumber 编码,FName 名称,isnull(FModel,'')+' ' 型号,FItemID ID,fischoice=0 from t_ICItem
   where FDeleted =0 and (FNumber like '%'+@filter +'%' or FName like '%'+@filter +'%')
   order by FNumber
end
else if @type =4--仓库
begin
    select FNumber 编码,FName 名称,FItemID ID,fischoice=0 from t_Stock
    where FDeleted =0 and (FNumber like '%'+@filter +'%' or FName like '%'+@filter +'%')
end
else if @type =5--仓位
begin
     select  a.FFullNumber 编码,  a.FName 名称,isnull(c.fname,'')+' '  仓库名称,isnull(c.fnumber,'')+' '  仓库编码  ,isnull(c.fitemid,'0')+' ' 仓库ID,a.fspid ID, fischoice=0
     from t_StockPlace a left join t_StockPlaceGroup b on a.FSPGroupID=b.FSPGroupID left join t_Stock c on c.FSPGroupID=b.FSPGroupID
     where a.fdeleted=0  and (a.FFullNumber like '%'+@filter +'%' or a.FName like '%'+@filter +'%' or c.FNumber like '%'+@filter +'%' or c.FName like '%'+@filter +'%')
end
else if @type =6--单位
begin
     select FNumber 编码,FName 名称,FMeasureUnitID ID,fischoice=0 from t_MeasureUnit
     where FDeleted =0 and (FNumber like '%'+@filter +'%' or FName like '%'+@filter +'%')
end
else if @type =7--供应商
begin
     select FNumber 编码,FName 名称,FItemID ID,fischoice=0 from t_Supplier
     where FDeleted =0 and (FNumber like '%'+@filter +'%' or FName like '%'+@filter +'%')
end
else if @type =8--提货方式
begin
       select     FID 编码,  FName 名称, FInterID  ID,fischoice=0 from t_SubMessage
       where FTypeID =32  and fdeleted=0    and (FID like '%'+@filter +'%' or FName like '%'+@filter +'%')

end
else if @type =9--盘点方案
begin
       select     FProcessID 编码,  isnull(FRemark,'')+' ' 名称,   FMaxBillInterID  ID,fischoice=0 from icstockcheckprocess 
       where  fstatus=0   and ( FProcessID like '%'+@filter +'%' or isnull(FRemark,'')+' ' like '%'+@filter +'%')

end
set nocount off

 
end