大家好,请教大家一下,我在存储过程中,需要对两个二进制数进行按位或运算,但是系统报错:“数据类型 binary 和 binary 在 boolean OR 运算符中不兼容。”
请各位高手指教!!!具体代码如下:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goAlter PROCEDURE [dbo].[CollectSchedulDataMonitor]
@result binary(16) = 0x0000000000000000 output
AS Declare @PLCId int
Declare @Tagid int
Declare @Value float
Declare @stime datetime
Declare @etime datetime
Declare @countErr int
Declare @count int
Declare @flag binary(16)
Declare @org binary(16)set @etime=getdate()
set @stime=dateadd(n,-15,@etime)
set @flag = 0x0000000000000001
set @org = 0x0000000000000000set @PLCId=1
While @PLCId<=10
Begin
set @countErr = 0
set @count = 0
Declare tagidCursor Cursor For
Select Tagid From Controldata.CollectScheduleDataDictionary Where PLCId=@PLCId
Open tagidCursor
Fetch Next From tagidCursor Into @Tagid
While @@FETCH_STATUS = 0
Begin
Declare dataValueCursor Cursor For
Select value From ControLdata.Monitor Where tagid = @Tagid And logtime Between @stime And @etime
Fetch Next From dataValueCursor Into @Value
Open dataValueCursor
While @@FETCH_STATUS =0
Begin
set @count = @count + 1
If @Value = Null
Begin
set @countErr = @countErr + 1
End
Fetch Next From dataValueCursor Into @Value
End
Close dataValueCursor
DealLocate dataValueCursor
If(@countErr/@count > 0.2)
Begin
Set @org = @org | @flag
End
Set @flag = @flag * 2
Fetch Next From tagidCursor Into @Tagid
End
Close tagidCursor
DealLocate tagidCursor
EndReturn @result其中错误语句:是Set @org = @org | @flag另外,SQL中是否不支持位左移呢?我只好用Set @flag = @flag*2 来实现左移
请各位高手指教!!!具体代码如下:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goAlter PROCEDURE [dbo].[CollectSchedulDataMonitor]
@result binary(16) = 0x0000000000000000 output
AS Declare @PLCId int
Declare @Tagid int
Declare @Value float
Declare @stime datetime
Declare @etime datetime
Declare @countErr int
Declare @count int
Declare @flag binary(16)
Declare @org binary(16)set @etime=getdate()
set @stime=dateadd(n,-15,@etime)
set @flag = 0x0000000000000001
set @org = 0x0000000000000000set @PLCId=1
While @PLCId<=10
Begin
set @countErr = 0
set @count = 0
Declare tagidCursor Cursor For
Select Tagid From Controldata.CollectScheduleDataDictionary Where PLCId=@PLCId
Open tagidCursor
Fetch Next From tagidCursor Into @Tagid
While @@FETCH_STATUS = 0
Begin
Declare dataValueCursor Cursor For
Select value From ControLdata.Monitor Where tagid = @Tagid And logtime Between @stime And @etime
Fetch Next From dataValueCursor Into @Value
Open dataValueCursor
While @@FETCH_STATUS =0
Begin
set @count = @count + 1
If @Value = Null
Begin
set @countErr = @countErr + 1
End
Fetch Next From dataValueCursor Into @Value
End
Close dataValueCursor
DealLocate dataValueCursor
If(@countErr/@count > 0.2)
Begin
Set @org = @org | @flag
End
Set @flag = @flag * 2
Fetch Next From tagidCursor Into @Tagid
End
Close tagidCursor
DealLocate tagidCursor
EndReturn @result其中错误语句:是Set @org = @org | @flag另外,SQL中是否不支持位左移呢?我只好用Set @flag = @flag*2 来实现左移
但是我在建立作业执行的时候失败,说是游标未打开
请问SP里游标可以嵌套吗?或者为什么说游标未打开呢?
#2.下面两行代码互换一下位置
FETCH NEXT FROM dataValueCursor INTO @Value
OPEN dataValueCursor
明天上班试试
谢谢楼上
因为SQL中不支持二进制的运算,我照之前的方法,将@org类型改为了char(16)= '0000000000000000'
@result仍然为binary(16)=0x0000000000000000
这个程序是用于状态监视报警的,正常情况不发生报警的情况是循环完成后@org仍然为'0000000000000000'。在循环完成后,利用Set @result = Convert(binary, @org),将@org的值赋给@result输出。
我运行这个SP后,因为在sql中无法直接看到二进制结果,我将其导出到excel中,以文本显示出来的结果确实“30303030303030303030303030303030”
奇怪的是两点:1、结果应该是16位的,却变成了32位。数据库中设定的也是binary(16);2、预期的结果值应当是“0000000000000000”,而实际值确实是“303030303030”。
请问是不是在@result = Convert(binary, @org)时将每个0转换成了30呢?按理说二进制应该是0,1,怎么结果却有3呢???