--增加分类
GO
IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=47)
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description)  Values(47, 'SC47', '手术工作站', 18, 0, 46, 0, Null)
else
print 'SYS_ScriptCategories.id=47 已经存在.'

GO


--删除旧记录
delete from SYS_Scripts where id=1662 --删除脚本内容
GO


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1662)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(1662, 47, 'SQL_手术病人信息', '检索手术病人信息', '按手术记录ID检索手术病人信息', 1, 0, Null, 1, 'Declare @VAT01 int,@cbm01 int
Set @VAT01 = %d
if exists(select * from VAT1 where VAT01 = @VAT01 and ACF01 = 1)
begin
select @cbm01 = b.CBM01 from VAT1 a join VAF1 b on b.VAF01 = a.VAF01 where a.VAT01 = @VAT01

SELECT a.VAA01 , a.VAA07, a.VAT01 ,b.VAA03, b.VAA04, b.VAA05 , c.ABW02 , b.VAA10 , a.BEE01 , a.BEE03 ,a.VAT33 ,d.BCQ04B ,
  isnull(f.BAK05,e.VAO15) BAK05 , g.BBX01 as NBBX01 ,isnull(g.BBX05,v.VAF22) BBX05 , m.BBX05 MBBX05 , n.BCK03 , a.BCK01
  , a.BBX01 , a.VAA01 , a.VAA07 , VAT09 , VAT10 ,z.VAV05 , z.BBX01 as YBBX01 ,a.VAT04 ,a.ACF01,a.AAB01,a.VAT16,a.ACH01,a.ABD01,a.ACZ01,a.BJY01,a.ACI02,a.BAK02,a.BJX01  
FROM VAT1 a
  JOIN VAA1 b ON a.VAA01 = b.VAA01
  left JOIN VAF1 v ON a.VAF01 = v.VAF01 and v.VAF10 >=8 and v.VAF10 <= 9
  left JOIN BBX1 g ON v.BBX01 = g.BBX01  
  Left JOIN VAE1 d ON a.VAA07 = d.VAE01         
  LEFT JOIN BCK1 n ON v.BCK01A = n.BCK01
  LEFT JOIN BBX1 m ON a.BBX01 = m.BBX01 
  LEFT JOIN (select top 1 VAO15,BAK01A,@VAT01 VAT01 from VAO1 where VAF01 = @cbm01 and VAO22 = 1) e ON e.VAT01 = a.VAT01
  LEFT JOIN BAK1 f ON e.BAK01A = f.BAK01 
  LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
  LEFT JOIN (select Top 1 VAT01 , VAV01 , VAV05 ,BBX01 from VAV1 where VAT01 = @VAT01 and VAV03 =1 and VAV04 =1 ) z  on a.VAT01 = z.VAT01 
where a.VAT01 = @VAT01 
end
else
begin
select @cbm01 = b.CBM01 from VAT1 a join VAF2 b on b.VAF01 = a.VAF01 where a.VAT01 = @VAT01

SELECT a.VAA01 , a.VAA07, a.VAT01 ,b.VAA03, b.VAA04, b.VAA05 , c.ABW02 , b.VAA10 , a.BEE01 , a.BEE03 ,a.VAT33 ,d.BCQ04B ,
  isnull(f.BAK05,e.VAO15) BAK05 , g.BBX01 as NBBX01 ,isnull(g.BBX05,v.VAF22) BBX05 , m.BBX05 MBBX05 , n.BCK03 , a.BCK01
  , a.BBX01 , a.VAA01 , a.VAA07 , VAT09 , VAT10 ,z.VAV05 , z.BBX01 as YBBX01 ,a.VAT04 ,a.ACF01,a.AAB01,a.VAT16,a.ACH01,a.ABD01,a.ACZ01,a.BJY01,a.ACI02,a.BAK02,a.BJX01  
FROM VAT1 a
  JOIN VAA1 b ON a.VAA01 = b.VAA01
  left JOIN VAF2 v ON a.VAF01 = v.VAF01 and v.VAF10 >=8 and v.VAF10 <= 9
  left JOIN BBX1 g ON v.BBX01 = g.BBX01  
  Left JOIN VAE1 d ON a.VAA07 = d.VAE01         
  LEFT JOIN BCK1 n ON v.BCK01A = n.BCK01
  LEFT JOIN BBX1 m ON a.BBX01 = m.BBX01 
  LEFT JOIN (select top 1 VAO15,BAK01A,@VAT01 VAT01 from VAO2 where VAF01 = @cbm01 and VAO22 = 1) e ON e.VAT01 = a.VAT01
  LEFT JOIN BAK1 f ON e.BAK01A = f.BAK01 
  LEFT JOIN ABW1 c ON b.ABW01 = c.ABW01
  LEFT JOIN (select Top 1 VAT01 , VAV01 , VAV05 ,BBX01 from VAV1 where VAT01 = @VAT01 and VAV03 =1 and VAV04 =1 ) z  on a.VAT01 = z.VAT01 
where a.VAT01 = @VAT01 
end', '2016-03-02 11:25:07', '(00399)张豪', 0)
else
print 'SYS_Scripts.id=1662 已经存在.'

GO