How to write temporary variable in cursor and insert its value to a table?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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:

SQL

<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.

SQL

<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

SQL

Thanku..

Posted 11-Sep-15 19:50pm

Member 11377180

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-09-11T20:09:00

Solution 1

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

SQL

@OP=Qty*1

use

SQL

SET @OP=Qty*1

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

SQL

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

Posted 11-Sep-15 20:09pm

Wendelius

Comments

Member 11377180 12-Sep-15 2:49am

BEGIN

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

else
Begin
set @OP=Qty* -1
end
Insert into HSR_OP_Bal(Showroomcode,Stockno,Startdate,Servicetag,OP) values(@Showroomcode,@Stockno,@Startdate,@Servicetag,@OP)

end

Im getting this error:Incorrect syntax near the keyword 'else'.

Wendelius 12-Sep-15 2:55am

As in the example I provided, move the BEGIN before Declare @OP int. Otherwise the if branch contains only the declare statement.

Member 11377180 12-Sep-15 5:07am

thanks..its working. but output is: Only first row is taken into cursor and its executing continously... is there anything im missing out? pls check my update proc below:

USE [Opening1]
GO
/****** Object: StoredProcedure [dbo].[HSR] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROC [dbo].[HSR]
AS
BEGIN
Declare @Showroomcode varchar(50),
@Startdate date,
@Stockno int,
@Servicetag varchar(50),
@TransType int,
@Qty int

BEGIN
DECLARE HSRCursor Cursor
STATIC FOR

select distinct a.Showroomcode,a.Stockno,b.Startdate,a.Servicetag, a.Qty ,a.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 HSRCursor INTO @Showroomcode,@Stockno,@Startdate,@Servicetag,@Qty,@TransType
WHILE @@FETCH_STATUS = 0
begin

If ( @TransType=1100 or @Transtype=1300)
set @Qty=@Qty*1
else
set @Qty=@Qty* -1

Insert into HSR_OP_Bal(Showroomcode,Startdate,Stockno,Servicetag,OP) values(@Showroomcode,@Startdate,@Stockno,@Servicetag,@Qty)

end
end

CLOSE HSRCursor
DEALLOCATE HSRCursor
end
end

Wendelius 12-Sep-15 7:23am

As far as I can see, you don't have a FETCH NEXT statement inside the WHILE loop. Try adding the FETCH NEXT after the insert statement before the end that ends the loop.

A tip: Use proper indenting when writing T-SQL blocks. It helps you to see the structure of the program.

Member 11377180 12-Sep-15 8:18am

Yes you are correct. I didnt put Fetch at the end since I thought it will go to first Fetch statement(one before while) after executing while loop. All working fine now,Thanks alot for the help!!

Wendelius 12-Sep-15 9:07am

You're welcome :)