1. 如何判断是否存在
2. 如何批量设置权限declare @MachineID bigint
declare @MachineID2 varchar(100)
declare @ErrMsg varchar(100)
Set @MachineID=100
Set @ErrMsg=''
--exec('select * from DC_CONTROL where CTLID like ''%'+@MachineID+'%''')
if not Exists(select * from DC_CONTROL where CTLID like @MachineID2%) ---------------------如何判断是否存在
begin
Set @ErrMsg='未能找到该设备,设置失败!'
print @ErrMsg
end
--如果存在,则给改人员设置所有查找出的设备权限(批量设置)
else
begin
declare CTLID_cursor cursor for
--错误 exec('select * from DC_CONTROL where CTLID like ''%'+@MachineID+'%''') ---------------------如何查询
OPEN CTLID_cursor
FETCH NEXT FROM CTLID_cursor
INTO @MachineID
WHILE @@FETCH_STATUS = 0
BEGIN
begin Tran Tran_MachineAnnouncer_Set_insert
print @MachineID
/* insert into MachineAnnouncer_Set(MachineID,AnnouncerID,PurVal,ISDown)
values (@MachineID,@AnnouncerID,@PurVal,@ISDown);
*/
if @@Error<>0
begin
Rollback Tran Tran_MachineAnnouncer_Set_insert
select @ErrMsg='为设备号:['+Ltrim(Rtrim(@MachineID))+'],设置权限失败!'
-- Return -1
end
else
begin
Commit Tran Tran_MachineAnnouncer_Set_insert
-- Return 0
end
FETCH NEXT FROM CTLID_cursor
INTO @MachineID
END
CLOSE CTLID_cursor
DEALLOCATE CTLID_cursor
end
2. 如何批量设置权限declare @MachineID bigint
declare @MachineID2 varchar(100)
declare @ErrMsg varchar(100)
Set @MachineID=100
Set @ErrMsg=''
--exec('select * from DC_CONTROL where CTLID like ''%'+@MachineID+'%''')
if not Exists(select * from DC_CONTROL where CTLID like @MachineID2%) ---------------------如何判断是否存在
begin
Set @ErrMsg='未能找到该设备,设置失败!'
print @ErrMsg
end
--如果存在,则给改人员设置所有查找出的设备权限(批量设置)
else
begin
declare CTLID_cursor cursor for
--错误 exec('select * from DC_CONTROL where CTLID like ''%'+@MachineID+'%''') ---------------------如何查询
OPEN CTLID_cursor
FETCH NEXT FROM CTLID_cursor
INTO @MachineID
WHILE @@FETCH_STATUS = 0
BEGIN
begin Tran Tran_MachineAnnouncer_Set_insert
print @MachineID
/* insert into MachineAnnouncer_Set(MachineID,AnnouncerID,PurVal,ISDown)
values (@MachineID,@AnnouncerID,@PurVal,@ISDown);
*/
if @@Error<>0
begin
Rollback Tran Tran_MachineAnnouncer_Set_insert
select @ErrMsg='为设备号:['+Ltrim(Rtrim(@MachineID))+'],设置权限失败!'
-- Return -1
end
else
begin
Commit Tran Tran_MachineAnnouncer_Set_insert
-- Return 0
end
FETCH NEXT FROM CTLID_cursor
INTO @MachineID
END
CLOSE CTLID_cursor
DEALLOCATE CTLID_cursor
end
解决方案 »
- sql 2005读取xml
- coolingpipe(冷箫轻笛)西西,你的答案是对的,不好意思
- 远程服务器上装了一个SQL Server 2008,在别的机器上访问不了
- 邹大哥(如何把dbf和xls数据追加到sql server)
- exec sp_msforeachtable “” 为什么是双引号而不是单引号
- 求教 ASP + ACCESS update 语句 谢谢
- SQL排序问题
- 求教一个中文识别的问题,请高手帮帮忙,急
- how to view structure of a table ?command model
- 紧急求助SQLSERVER2000问题
- 请用sql语句写一个从1减到100的语句出来。
- 求:update 语句
-- exec('select CTLID from DC_CONTROL where CTLID like ''%'+@MachineID+'%''') ---------------------如何查询??????????????
OPEN CTLID_cursor
declare @MachineID bigint
declare @MachineID2 varchar(100)
declare @ErrMsg varchar(100)
Set @MachineID=100
Set @ErrMsg=''
--如果存在,则给改人员设置所有查找出的设备权限(批量设置)
declare CTLID_cursor cursor for
--select CTLID from DC_CONTROL where CTLID like '100%'
-- exec('select CTLID from DC_CONTROL where CTLID like ''%'+@MachineID+'%''') ---------------------如何查询??????????????
OPEN CTLID_cursor
FETCH NEXT FROM CTLID_cursor
INTO @MachineID
WHILE @@FETCH_STATUS = 0
BEGIN
begin Tran Tran_MachineAnnouncer_Set_insert
print @MachineID
/* insert into MachineAnnouncer_Set(MachineID,AnnouncerID,PurVal,ISDown)
values (@MachineID,@AnnouncerID,@PurVal,@ISDown);
*/
if @@Error<>0
begin
Rollback Tran Tran_MachineAnnouncer_Set_insert
select @ErrMsg='为设备号:['+Ltrim(Rtrim(@MachineID))+'],设置权限失败!'
-- Return -1
end
else
begin
Commit Tran Tran_MachineAnnouncer_Set_insert
-- Return 0
end
FETCH NEXT FROM CTLID_cursor
INTO @MachineID
END
CLOSE CTLID_cursor
DEALLOCATE CTLID_cursor
?
好神奇!!!
select * from DC_CONTROL where CTLID like '%'+LTRIM(@MachineID)+'%'可以执行~~~~
如果前面不加 % 号,要怎么写?