Sql程序无法正常工作 [英] Sql procedure is not working

问题描述

亲爱的所有



我被困在轰鸣声程序中请帮帮我。



我尝试过：

  DECLARE  < span class =code-sdkkeyword> @ msgid   AS   BIGINT  
 < span class =code-keyword> DECLARE  cur  CURSOR  LOCAL 
  FOR  
  SELECT  msgid 
  FROM  rawtTackHistory_A2Z 
  WHERE  rn.msgid> （ 178370450 ）
  ORDER   BY  
 rn.gps_time 
 
  OPEN  cur 
  FETCH  NEXT  FROM  cur  INTO   @ msgid  
  WHILE   @@ FETCH_STATUS  =  0  
  BEGIN  
  PRINT  '  在游标MsgId中：' +（ VARCHAR （ 50 ）， @ msgid ）
 
  FETCH  NEXT < span class =code-keyword> FROM  cur  INTO   @ msgid  
  END  
 < span class =code-keyword> CLOSE  cur 
  DEALLOCATE  cur 

以上查询运行正常

  DECLARE   @ msgid   AS   BIGINT  
  DECLARE  cur  CURSOR  LOCAL 
  FOR  
  SELECT  msgid 
  FROM  rawtTackHistory_A2Z 
  WHERE  rn.msgid> （ SELECT   TOP   1  MsgID  FROM  HistoryMsgID）
  ORDER   BY  rn。 gps_time 
 
  OPEN  cur 
  FETCH  NEXT  FROM  cur  INTO   @ msgid  
  WHILE   @@ FETCH_STATUS  =  0  
  BEGIN  
  PRINT  ' 在游标MsgId中：' +（ VARCHAR （ 50 ） ， @ msgid ）
 
  FETCH  NEXT  FROM  cur  INTO   @ msgid  
  END  
  CLOSE  cur 
  DEALLOCATE  cur 

我替换消息ID查询然后程序没有响应

SELECT TOP 1来自HistoryMsgID的消息

此查询在00:00秒给出了相同的结果

解决方案

正如@ZafaSultan指出的那样，

  PRINT  ' 在游标MsgId中：' +（ VARCHAR （ 50 ）， @ msgid ）

引用：

'VARCHAR'不是认可的d内置函数名。

应该读取

  PRINT  ' ： + CAST（ @ msgid   AS   VARCHAR ）

你说这是没有反应但你只在光标中有PRINT语句 - 记住输出将出现在SSMS的消息选项卡上，而不是结果选项卡中。



如果您想立即查看结果，请使用

  RAISERROR （' ： + CAST（ @ msgid   AS   VARCHAR ）， 0 ， 1 ） W. ITH  NOWAIT 

它不会阻止你的SP运行（正常错误）



你确定< pre lang =SQL> SELECT TOP 1 MsgID FROM HistoryMsgID

返回值< = 178370450？您在该查询中没有WHERE或ORDER BY子句...也许您的意思是

  SELECT  MAX（MsgID） FROM  HistoryMsgID 

除上述几点外，您的代码没有任何问题 - 除了事实你正在使用游标。由于这只是使输出可见，只需使用select将结果输入结果选项卡。

我个人只会改变你的一些代码，仅用于调试目的。

  DECLARE   @msgid   AS   BIGINT  
  DECLARE   @ selectMsg   AS   BIGINT  
 选择  top   1   @ selectMsg  = MsgID  FROM  HistoryMsgID 
 
   -   现在我们可以看到@selectMsg是什么 
  print   @ selectMsg  
 
  DECLARE  cur  CURSOR  LOCAL 
  FOR  
  SELECT  msgid 
  FROM  rawtTackHistory_A2Z rn 
  WHERE  rn.msgid>  @ selectMsg  
  ORDER   BY  rn.gps_time 
 
  OPEN  cur 
  FETCH  NEXT  FROM  cur  INTO   @ msgid  
  WHILE   @@ FETCH_STATUS  =  0  
  BEGIN  
  PRINT  ' < span class =code-string> in cursor MsgId：' +  CONVERT （ VARCHAR  （ 50 ）， @ msgid ）
 
  FETCH  NEXT  FROM  cur  INTO   @ msgid  
  END  
  CLOSE  cur 
  DEALLOCATE  cur 

Dear All

I am stuck in bellow procedure please help me out.

What I have tried:

DECLARE @msgid AS BIGINT
		DECLARE cur CURSOR LOCAL 
		FOR
		    SELECT msgid
		    FROM   rawtTackHistory_A2Z rn
		    WHERE  rn.msgid > (178370450 )
		    ORDER BY
		           rn.gps_time
		
		OPEN cur
		FETCH NEXT FROM cur INTO @msgid                            
		WHILE @@FETCH_STATUS = 0
		BEGIN
		    PRINT 'in  cursor MsgId :' + (VARCHAR(50), @msgid)
		    
		    FETCH NEXT FROM cur INTO @msgid
		END
		CLOSE cur
		DEALLOCATE cur

above query run fine

DECLARE @msgid AS BIGINT
		DECLARE cur CURSOR LOCAL 
		FOR
		    SELECT msgid
		    FROM   rawtTackHistory_A2Z rn
		    WHERE  rn.msgid > (SELECT TOP 1 MsgID FROM HistoryMsgID)
		    ORDER BY rn.gps_time
		
		OPEN cur
		FETCH NEXT FROM cur INTO @msgid                            
		WHILE @@FETCH_STATUS = 0
		BEGIN
		    PRINT 'in  cursor MsgId : ' + (VARCHAR(50), @msgid)
		    
		    FETCH NEXT FROM cur INTO @msgid
		END
		CLOSE cur
		DEALLOCATE cur

I replace message id with query then procedure goes unresponsive
SELECT TOP 1 MsgID FROM HistoryMsgID
this query give me same result in 00:00 sec

解决方案

As @ZafaSultan has pointed out, there is an error with

PRINT 'in  cursor MsgId : ' + (VARCHAR(50), @msgid)

Quote:

'VARCHAR' is not a recognized built-in function name.

It should read

PRINT 'in cursor MsgId : ' + CAST(@msgid AS VARCHAR)

You are saying it is "unresponsive" but you only have PRINT statements in your cursor - remember that the output will appear on the "Messages" tab in SSMS, not in the "Results" tab.

If you want to see the results immediately then use

RAISERROR('in cursor MsgId : ' + CAST(@msgid AS VARCHAR),0,1) WITH NOWAIT

It won't stop your SP from running (as a normal error would)

Are you sure that

SELECT TOP 1 MsgID FROM HistoryMsgID

is returning a value <= 178370450 ? You have no WHERE or ORDER BY clause on that query ... perhaps you meant

SELECT MAX(MsgID) FROM HistoryMsgID

Apart from the above points there is nothing wrong with your code - apart from the fact you are using a CURSOR. As this is all to make the output visible, just use the select to get the results into the Results tab.

Personally I would change just a little bit of your code, for Debugging purposes only.

DECLARE @msgid AS BIGINT
DECLARE @selectMsg AS BIGINT
select top 1 @selectMsg = MsgID FROM HistoryMsgID

--Now Here we can see what @selectMsg is
print @selectMsg

DECLARE cur CURSOR LOCAL 
FOR
    SELECT msgid
    FROM   rawtTackHistory_A2Z rn
    WHERE  rn.msgid > @selectMsg
    ORDER BY rn.gps_time

OPEN cur
FETCH NEXT FROM cur INTO @msgid                            
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'in  cursor MsgId : ' + CONVERT(VARCHAR(50), @msgid)
    
    FETCH NEXT FROM cur INTO @msgid
END
CLOSE cur
DEALLOCATE cur

这篇关于Sql程序无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！