'INT' is not a recognized CURSOR option.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

'INT' is not a recognized CURSOR option.

Author

Topic

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-18 : 09:45:47

HI,
This is my req...
i have to sum up the qty n that qty should be added to qtyonhand in other table....

Declare Qty INT
Qty =SELECT SUM(Quantity) FROM InventoryLedger WHERE ITEMID=@ItemID

UPDATE IW
SET IW.QtyOnHand=IW.QtyOnHand + Qty - @TAllotedQty
FROM InventoryByWarehouse IW
where
IW.CompanyID = @CompanyID
AND IW.DivisionID = @DivisionID
AND IW.DepartmentID = @DepartmentID
AND IW.ItemID=@ItemID

Thanks...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 09:52:53

quote:
Originally posted by Anushka

HI,
This is my req...
i have to sum up the qty n that qty should be added to qtyonhand in other table....

Declare @Qty INT
SET @Qty =SELECT SUM(Quantity) FROM InventoryLedger WHERE ITEMID=@ItemID

UPDATE IW
SET IW.QtyOnHand=IW.QtyOnHand + Qty - @TAllotedQty
FROM InventoryByWarehouse IW
where
IW.CompanyID = @CompanyID
AND IW.DivisionID = @DivisionID
AND IW.DepartmentID = @DepartmentID
AND IW.ItemID=@ItemID

Thanks...

You should put @ for variable names

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-18 : 10:15:54

Incorrect syntax near select

SET @Qty =SELECT SUM(Quantity) FROM InventoryLedger WHERE ITEMID=@ItemID

we have to keep

SET @Qty ='SELECT SUM(Quantity) FROM InventoryLedger WHERE ITEMID=@ItemID'
itemid is parameter
so is it

I tried like
declare @ItemID NVARCHAR(36)
set @ItemID='bbbbbbbb'
Declare @Qty INT
SET @Qty = 'SELECT SUM(Quantity) FROM InventoryLedger WHERE ITEMID ='+@ItemID+''

PRINT @Qty

but error:Conversion failed when converting the nvarchar value 'SELECT SUM(Quantity) FROM InventoryLedger WHERE ITEMID =bbbbbbbb' to data type int.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-06-18 : 10:20:11

SET @Qty =(SELECT SUM(Quantity) FROM InventoryLedger WHERE ITEMID=@ItemID)

or

select @Qty = SUM(Quantity) FROM InventoryLedger WHERE ITEMID=@ItemID

Em

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-18 : 10:20:37

Use

SELECT @Qty = SUM(Quantity) FROM InventoryLedger WHERE ITEMID=@ItemID

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-18 : 10:21:39

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 10:23:38

quote:
Originally posted by Anushka

Incorrect syntax near select

SET @Qty =SELECT SUM(Quantity) FROM InventoryLedger WHERE ITEMID=@ItemID

we have to keep

SET @Qty ='SELECT SUM(Quantity) FROM InventoryLedger WHERE ITEMID=@ItemID'
itemid is parameter
so is it

I tried like
declare @ItemID NVARCHAR(36)
set @ItemID='bbbbbbbb'
DECLARE @Sql varchar(8000)
SET @Sql = 'Declare @Qty INT;SELECT @Qty =SUM(Quantity) FROM InventoryLedger WHERE ITEMID ='+@ItemID
EXEC(@Sql)
PRINT (@Qty)

but error:Conversion failed when converting the nvarchar value 'SELECT SUM(Quantity) FROM InventoryLedger WHERE ITEMID =bbbbbbbb' to data type int.

Change like above and try

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 10:32:59

Wat about this?

declare @ItemID NVARCHAR(36)
set @ItemID='bbbbbbbb'
DECLARE @Sql varchar(8000)
Declare @Qty INT;
SET @Sql = 'SELECT @Qty =SUM(Quantity) FROM InventoryLedger WHERE ITEMID ='+@ItemID 
EXEC(@Sql)
PRINT (@Qty)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-18 : 10:34:25

>>Must declare the scalar variable "@Qty".

As it says you need to declare it

Madhivanan

Failing to plan is Planning to fail

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-18 : 10:35:56

yes it is saying
Must declare the scalar variable "@Qty".

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-18 : 10:37:35

quote:
Originally posted by madhivanan

>>Must declare the scalar variable "@Qty".

As it says you need to declare it

Madhivanan

Failing to plan is Planning to fail

It seems OP deleted the reply. My reply was to the OP

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 10:37:57

quote:
Originally posted by Anushka

yes it is saying
Must declare the scalar variable "@Qty".

I think that was because variable was declared inside the dynamic string. Try taking declare statement out as in last post.

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-18 : 10:56:52

declare @ItemID NVARCHAR(36)
set @ItemID='bbbbbbbb'
DECLARE @Sql varchar(8000)
Declare @Qty INT;
SET @Sql = 'select SUM(Quantity) FROM InventoryLedger WHERE ITEMID ='+@ItemID
EXEC(@Sql)
PRINT (@Qty)
invalid column name 'bbbbbbbb'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-18 : 11:12:03

[code]DECLARE @Qty INT

SELECT @Qty = SUM(Quantity)
FROM InventoryLedger
WHERE ITEMID = @ItemID

UPDATE InventoryByWarehouse
SET QtyOnHand = QtyOnHand + @Qty - @TAllotedQty
FROM InventoryByWarehouse
WHERE CompanyID = @CompanyID
AND DivisionID = @DivisionID
AND DepartmentID = @DepartmentID
AND ItemID = @ItemID[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 11:18:09

quote:
Originally posted by Anushka

declare @ItemID NVARCHAR(36)
set @ItemID='bbbbbbbb'
DECLARE @Sql varchar(8000)
Declare @Qty INT;
SET @Sql = 'select SUM(Quantity) FROM InventoryLedger WHERE ITEMID ='+@ItemID
EXEC(@Sql)
PRINT (@Qty)
invalid column name 'bbbbbbbb'

Any special reason why you used NVARCHAR for @itemID?

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-19 : 01:08:11

hi,

iam getting null for qtyonhand by writing like this:
actually:frm front end i will b getting values like this
select Quantity FROM InventoryLedger
WHERE TransDate = '2008-06-19 00:00:00.000'
so : Qty = 10

select QtyOnHand FROM InventoryByWarehouse
WHERE ItemID = 'bbbbbbbbbbbb'
@TAllotedQty = 20
QtyOnHand=300 +10 -20 =290

DECLARE @Qty INT

SELECT @Qty = Quantity
FROM InventoryLedger
WHERE TransDate = @Trndate

UPDATE InventoryByWarehouse
SET QtyOnHand = QtyOnHand + @Qty - @TAllotedQty
FROM InventoryByWarehouse
WHERE CompanyID = @CompanyID
AND DivisionID = @DivisionID
AND DepartmentID = @DepartmentID
AND ItemID = @ItemID

thanx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 01:20:04

SET QtyOnHand = QtyOnHand + coalesce(@Qty, 0) - @TAllotedQty

E 12°55'05.25"
N 56°04'39.16"

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-19 : 01:38:54

Hi,
wat is this coalesce(@Qty, 0) -
by writing this ,i think qty it is taking as 0 instead of passing parameter(@qty)
...i got the value QtyOnHand - @TAllotedQty by writing like this:
SET QtyOnHand = QtyOnHand + coalesce(@Qty, 0) - @TAllotedQty

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 02:14:16

quote:
Originally posted by Anushka

Hi,
wat is this coalesce(@Qty, 0) -
by writing this ,i think qty it is taking as 0 instead of passing parameter(@qty)
...i got the value QtyOnHand - @TAllotedQty by writing like this:
SET QtyOnHand = QtyOnHand + coalesce(@Qty, 0) - @TAllotedQty

this will ensure it will take value 0 if @Qty is NULL or take the value in variable @Qty

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-19 : 02:15:04

Hey PEOS I got it ,Rechecked....
but wat is this coalesce(@Qty, 0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 02:21:06