查询某个存储过程是否已存在,如果存在就删除这个存储过程,使用系统表 sysobjects :
1 if exists (select 1 from dbo.sysobjects where name = 'SP_GetStaffRolesInfo')
2 drop procedure SP_GetStaffRolesInfo
3 GO
查询某一列在某个表中是否存在,如果不存在就添加此列,使用系统表 syscolumns 和 object_id()函数:
1 if not exists(select 1 from syscolumns where id=object_id(N'StaffRoles') and name = 'StaffRole_MemberCharge_TiCheng_Base')
2 alter table StaffRoles add StaffRole_MemberCharge_TiCheng_Base decimal(8,4) default(0.0000)
查询某个表在数据库中是否存在,如果不存在就创建这个表,使用系统表 sysobjects :
1 if not exists(select 1 from sysobjects where name='MemberChargeLevel')
2 begin
3 create table MemberChargeLevel
4 (
5 MemberCharge_Id int IDENTITY(1,1) PRIMARY KEY,
6 MemberCharge_Name varchar(40),
7 MemberCharge_BeginRecive decimal(8,2) NULL DEFAULT(0),
8 MemberCharge_EndRecive decimal(8,2) NULL DEFAULT(0),
9 MemberCharge_TiChengPoint decimal(8,4) NULL DEFAULT(0),
10 MemberCharge_DelState int NULL DEFAULT(0),
11 MemberCharge_Description varchar(200) NULL DEFAULT('')
12 )
13 end
14 GO
查询某个表某行数据指定列的值是否满足条件,如果满足条件则执行更新操作:
1 if exists(select SettingInfo_ID from SystemSettingInfo where SettingInfo_Name='启用某某系统' and SettingInfo_Value='是')
2 begin
3 update SmsAddressGroup
4 set SmsAddressGroup_Name = @StaffRole_Name
5 where
6 SmsAddressGroup_OriginalGroupID = @StaffRole_ID
7 and SmsAddressGroup_ParentID = 1
8 end