sql 语句

语句

添加列语句
–2011-11-15 转出状态
alter table dbo.HX_BUSOPP_MF add TurnState varchar(10)

 --2011-11-15 转出人

alter table dbo.HX_BUSOPP_MF add TurnMan varchar(50)

   --2011-11-15 转出时间

alter table dbo.HX_BUSOPP_MF add TurnDate datetime
删除字段语句
alter table dbo.HX_BUSOPP_MF drop column TURN_DD
带参更新语句

update dbo.HX_BUSOPP_MF set TurnState = ‘T’,TurnMan = @TurnMan, TurnDate = @time1 where CompanyId = @CompanyId and BIL_ID = ‘BO’ AND BIL_NO = @BIL_NO
case when 语句
–sql 查询
SELECT per.USR 用户代号, US.NAME 用户姓名, dept.DEP 部门代号,dept.NAME 部门名称, isnull(menu.Menu,‘’) 菜单代号,isnull(menu.NAME,‘’) 菜单名称,
(case when isnull(per.Price,‘’)= ‘T’ then ‘T’ else ‘F’ end) 价格, (case when isnull(per.cust,‘’)= ‘T’ then ‘T’ else ‘F’ end )客商,
(case when isnull(per.Cost,‘’)= ‘T’ then ‘T’ else ‘F’ end) 成本, (case when isnull(per.SET1,‘’)= ‘T’ then ‘T’ else ‘F’ end )查询,
(case when isnull(per.New,‘’) = ‘T’ then ‘T’ else ‘F’ end) 新增, (case when isnull(per.Edit,‘’)= ‘T’ then ‘T’ else ‘F’ end) 编辑,
(case when isnull(per.Del,‘’) = ‘T’ then ‘T’ else ‘F’ end) 删除, (case when isnull(per.turn,‘’)= ‘T’ then ‘T’ else ‘F’ end) 打印转换
from tsinfo_permission per (NOLOCK)
left join TSINFO_Menu menu (NOLOCK) on per.ProductID = menu.ProductID and per.MENU = menu.Menu
left join HX_USERS US (NOLOCK) on US.USR_NO = per.USR
left join dept on dept.DEP = US.usDept_NO and per.CompanyId = dept.CompanyId
where per.ProductId=‘HMC’and per.CompanyId=‘00’
and menu.Type <> ‘Folder’
order by US.USR_NO ,isnull(menu.Menu,’')