如何在游标中编写临时变量并将其值插入表中？ [英] How to write temporary variable in cursor and insert its value to a table?

问题描述

我有3张桌子。请检查我的以下代码。我想在HSR_OP_Bal的OP中插入@OP。 @OP是IF Else输出的临时列。我得到以下错误：

'@OP'附近的语法不正确
关键字'end'附近的语法不正确。 
。

存储过程：

< pre> USE [Shoper9HO] 
  GO  
  / *   *****对象：StoredProcedure [dbo]。[HSR] ****** /  
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
 创建  PROC  [dbo]。[HSR] 
  AS  
  BEGIN  
 声明  @ ShowroomCode   varchar （ 50 ），
 
  @ Startdate   date ，
  @Qty   int ，
  @ StockNo   varchar （ 50 ），
  @ TransType   int ，
  @ Servicetag   varchar （ 50 ）
 
 
 
  BEGIN  
 < span class =code-keyword> DECLARE  HSRCursor 光标 
 STATIC  FOR  
 
 选择  distinct  a.Showroomcode，a.Stockno，Startdate，Servicetag，数量，Transtype 
 来自 ConsIMEItrndata a，
（选择 Showroomcode，startdate，stockno，OpeningQty 来自 IMTable）b 
 其中​​ a.Showroomcode = b.Showroomcode 
 和 a.Stockno = b.Stockno 
 
 
  OPEN  HSRCursor 
  IF   @@ CURSOR_ROWS > ;  0  
  BEGIN  
  FETCH  NEXT  FROM  HSRCur  INTO   @ Showroomcode ， @ Stockno ， @ Startdate ， @ Servicetag ， @ Qty ， @ Transtype  
  WHILE   @@ FETCH_STATUS  =  0  
  BEGIN  
 
 
 
 如果（@ TransType = 1100 或 @ Transtype = 1300）
 声明  @ OP   int  
 开始 
 @ OP =数量* 1 
 结束 
 开始 
 
  else  @ OP = Qty * -1 
  end  
 < span class =code-keyword>将 插入 HSR_OP_Bal（Showroomcode，Stockno，Startdate，Servicetag，OP）值（ @ Showroomcode ， @ Stockno ， @Startdate ， @ Servicetag ， @ OP ）
 
 
  end  
  end  
 关闭 HSRCursor 
  DEALLOCATE  HSRCursor 
  end  
 
  end  
 
   -   其中Startdate = @ Startdate  

请帮助解决上述错误。

我也想要写下面的查询作为最终输出包含在内部程序中，请编写完整的程序。

< pre>选择Showroomcode，Stockno，Startdate，Modelno，Servicetag，Sum（OP） as  OpeningBal 
 
 来自 HSR_OP_Bal 
 其中 Startdate = Frontend selecteddate 
 组 按 Showroomcode，Stockno，Startdate，Modelno，Servicetag 
   Sum（OP）> 0 

感谢.. 

解决方案

为变量赋值时，请使用SET语句。代替

 @ OP =数量* 1 

使用

  SET  @ OP =数量* 1 

此外，您似乎有不匹配的开始...结束块所以代码可能应该像

 如果（@ TransType = 1100 < span class =code-keyword>或 @ Transtype = 1300）
 开始 
 声明  @ OP   int  
  SET  @ OP =数量* 1 
 结束 
 其他  SET  @ OP =数量* -1 
 结束 

I have 3 tables. Please check my below code. I want to insert @OP in OP of HSR_OP_Bal. @OP is a temporary column for IF Else output. Im getting following errors:

Incorrect syntax near '@OP'
Incorrect syntax near the keyword 'end'.
.

Stored procedure:

<pre>USE [Shoper9HO]
GO
/****** Object:  StoredProcedure [dbo].[HSR] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROC [dbo].[HSR]
AS
BEGIN
Declare @ShowroomCode varchar(50),

@Startdate date,
@Qty int,
@StockNo varchar(50), 
@TransType int,
@Servicetag varchar(50) 



BEGIN
DECLARE HSRCursor Cursor
STATIC FOR 

select distinct a.Showroomcode,a.Stockno,Startdate,Servicetag, Qty ,Transtype
from ConsIMEItrndata a,
(select Showroomcode,startdate,stockno,OpeningQty from IMTable) b
where a.Showroomcode=b.Showroomcode
and   a.Stockno=b.Stockno 


OPEN HSRCursor
IF @@CURSOR_ROWS > 0
BEGIN 
FETCH NEXT FROM HSRCur INTO @Showroomcode,@Stockno,@Startdate,@Servicetag,@Qty,@Transtype
WHILE @@FETCH_STATUS = 0
BEGIN
    
	

    If ( @TransType=1100 or @Transtype=1300) 
    Declare @OP int
    Begin
     @OP=Qty*1
     end
     Begin
				
	else  @OP=Qty* -1
	end  
Insert into HSR_OP_Bal(Showroomcode,Stockno,Startdate,Servicetag,OP) values(@Showroomcode,@Stockno,@Startdate,@Servicetag,@OP)

			
 end
 end
    CLOSE HSRCursor
	DEALLOCATE HSRCursor
  end

  end

--where Startdate=@Startdate

Please help with above error .
Also I want to write this below query to be included inside procedure as final output,please write complete procedure.

<pre>Select Showroomcode,Stockno,Startdate,Modelno,Servicetag,Sum( OP) as OpeningBal
				
	from HSR_OP_Bal 
	where Startdate= Frontend selecteddate
	Group by Showroomcode,Stockno,Startdate,Modelno,Servicetag
    Having Sum(OP)>0

Thanku..

解决方案

When assigning a value to a variable, use SET statement. INstead of

@OP=Qty*1

use

SET @OP=Qty*1

Also it looks like you have mismatched begin...end blocks so the code should probably be something like

    If ( @TransType=1100 or @Transtype=1300)
    Begin
      Declare @OP int
      SET @OP=Qty*1
     end
    else  SET @OP=Qty* -1
end

这篇关于如何在游标中编写临时变量并将其值插入表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！