Cursor error in sp

[azharrumani09]

USE [DIAMDBF]
GO
/****** Object: StoredProcedure [dbo].[STOCKREPORT] Script Date: 01/21/2011 11:32:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[JEWELLSTOCKREPORT]
@SELDBNM SYSNAME,
@FILENO1 nvarchar(7),
@FROMCODE nvarchar(20),
@TOCODE nvarchar(20),
@START nvarchar(12),
@LAST nvarchar(12),
@DATE1 nvarchar(12),
@DATE2 nvarchar(12),
@DATEFORMAT varchar(12),
@GROUPCODE char(10),
@FROMLOT nvarchar(10),
@TOLOT nvarchar(10)
AS

Declare @execstring nvarchar (4000)
Declare @mcount1 numeric(15)
Declare @mcount2 numeric(15)
Declare @sktempnm char(7)

Declare @prod_openqty numeric(12,3)
Declare @recp_ropenqty numeric(12,3)
Declare @recp_rrecdqty numeric(12,3)
Declare @recp_copenqty numeric(12,3)
Declare @recp_crecdqty numeric(12,3)
Declare @issu_ropenqty numeric(12,3)
Declare @issu_rsaleqty numeric(12,3)
Declare @issu_copenqty numeric(12,3)
Declare @issu_csaleqty numeric(12,3)
Declare @prod_clsgqty numeric(12,3)
Declare @TranName varchar(20)
Declare @mprodcd varchar(20)

If left(@dateformat,2) = "dd"
Begin
Select @date1 = convert(smalldatetime, @date1, 103)
Select @date2 = convert(smalldatetime, @date2, 103)
Select @start = convert(smalldatetime, @start, 103)
Select @last = convert(smalldatetime, @last, 103)
End
Else
Begin
Select @date1 = convert(smalldatetime, @date1, 101)
Select @date2 = convert(smalldatetime, @date2, 101)
Select @start = convert(smalldatetime, @start, 101)
Select @last = convert(smalldatetime, @last, 101)
End

Set nocount on

Select @TranName = 'StkTran'

Begin Transaction @TranName

Select @sktempnm = @FILENO1

Select @execstring = 'SELECT * INTO '
Select @execstring = @execstring + quotename(@SELDBNM,"[")+ '.[dbo].' + quotename(@sktempnm,"[")
Select @execstring = @execstring + ' FROM ' + quotename(@SELDBNM,"[")+ '.[dbo].[LOTMAS]'

If @GROUPCODE = ''
BEGIN
If @FROMCODE <> 'ALL'
BEGIN
Select @execstring = @execstring + ' WHERE PROD_CODE BETWEEN '+ quotename(@FROMCODE, "'")
Select @execstring = @execstring + ' AND ' + quotename(@TOCODE,"'")
Select @execstring = @execstring + ' AND ' + ' LOT_NO BETWEEN '+ quotename(@FROMLOT, "'")
Select @execstring = @execstring + ' AND ' + quotename(@TOLOT,"'")
END
END
Else
BEGIN
Select @execstring = @execstring + ' WHERE PROD_GROUP = '+ quotename(@GROUPCODE, "'")
END

Select @execstring = @execstring + ' ORDER BY PROD_CODE,LOT_NO'
PRINT (@execstring)
Execute (@execstring)

if @start <> ''
Begin
Select @execstring = 'Declare Product_Cursor Cursor For Select Prod_Code, open_qty from ' +quotename(@SELDBNM, "[") +'.[dbo].'+ quotename(@sktempnm,"[")

Execute (@execstring)
If @GROUPCODE =''
BEGIN
If @FROMCODE <> 'ALL'
BEGIN
Select @execstring = @execstring + ' WHERE PROD_CODE BETWEEN '+ quotename(@FROMCODE, "'")
Select @execstring = @execstring + ' AND ' + quotename(@TOCODE,"'")
END
END
ELSE
BEGIN
Select @execstring = @execstring + ' WHERE PROD_GROUP = '+ quotename(@GROUPCODE, "'")
END
PRINT (@execstring)

Execute (@execstring)
OPEN Product_Cursor
FETCH NEXT FROM Product_Cursor INTO @mprodcd, @prod_openqty

WHILE @@FETCH_STATUS = 0
BEGIN
Select @recp_ropenqty = 0
Select @recp_rrecdqty = 0
Select @recp_copenqty = 0
Select @recp_crecdqty = 0
Select @issu_ropenqty = 0
Select @issu_rsaleqty = 0
Select @issu_copenqty = 0
Select @issu_csaleqty = 0

Select @execstring = 'Declare Recp_Cursor Cursor For Select SUM(CASE WHEN retu_goods = "T" and inv_date >= '+quotename(@date1,"'")+' and inv_date < '+quotename(@start,"'")+' THEN quantity ELSE 0 END) as ropen_qty, '
Select @execstring = @execstring + ' SUM(CASE WHEN retu_goods = "T" and inv_date between '+quotename(@start,"'")+' and '+quotename(@last,"'")+' THEN quantity ELSE 0 END) as rrecd_qty, '
Select @execstring = @execstring + ' SUM(CASE WHEN retu_goods = "F" and inv_date >= '+quotename(@date1,"'")+' and inv_date < '+quotename(@start,"'")+' THEN quantity ELSE 0 END) as copen_qty, '
Select @execstring = @execstring + ' SUM(CASE WHEN retu_goods = "F" and inv_date between '+quotename(@start,"'")+' and '+quotename(@last,"'")+' THEN quantity ELSE 0 END) as crecd_qty '
Select @execstring = @execstring + ' FROM ' + quotename(@SELDBNM,"[") +'.[dbo].[RECPTRN] WHERE PROD_CODE = '+quotename(@mprodcd,"'")
Select @execstring = @execstring + ' GROUP BY prod_code,lot_no'

PRINT (@execstring)
Execute (@execstring)
OPEN Recp_Cursor
FETCH NEXT FROM Recp_Cursor INTO @recp_ropenqty, @recp_rrecdqty, @recp_copenqty, @recp_crecdqty
CLOSE Recp_Cursor
Deallocate Recp_Cursor

Select @execstring = 'Declare Issu_Cursor Cursor For Select SUM(CASE WHEN retu_goods = "T" and inv_date >= '+quotename(@date1,"'")+' and inv_date < '+quotename(@start,"'")+' THEN quantity ELSE 0 END) as ropen_qty, '
Select @execstring = @execstring + ' SUM(CASE WHEN retu_goods = "T" and inv_date between '+quotename(@start,"'")+' and '+quotename(@last,"'")+' THEN quantity ELSE 0 END) as rrecd_qty, '
Select @execstring = @execstring + ' SUM(CASE WHEN retu_goods = "F" and inv_date >= '+quotename(@date1,"'")+' and inv_date < '+quotename(@start,"'")+' THEN quantity ELSE 0 END) as copen_qty, '
Select @execstring = @execstring + ' SUM(CASE WHEN retu_goods = "F" and inv_date between '+quotename(@start,"'")+' and '+quotename(@last,"'")+' THEN quantity ELSE 0 END) as crecd_qty '
Select @execstring = @execstring + ' FROM ' + quotename(@SELDBNM,"[") +'.[dbo].[ISSUETRN] WHERE PROD_CODE = '+quotename(@mprodcd,"'")
Select @execstring = @execstring + ' GROUP BY prod_code,lot_no'
PRINT (@execstring)
Execute (@execstring)
OPEN Issu_Cursor
FETCH NEXT FROM Issu_Cursor INTO @issu_ropenqty, @issu_rsaleqty, @issu_copenqty, @issu_csaleqty
CLOSE Issu_Cursor
Deallocate Issu_Cursor

Select @prod_clsgqty = @prod_openqty + @recp_copenqty-@issu_copenqty-(@recp_ropenqty+@issu_ropenqty) + (@recp_crecdqty - @recp_rrecdqty) - (@issu_csaleqty - @issu_rsaleqty)

Select @execstring = 'Update '+ quotename(@SELDBNM,"[") +'.[dbo].'+quotename(@sktempnm,"[")
Select @execstring = @execstring +' Set ' + quotename(@SELDBNM,"[") +'.[dbo].'+ quotename(@sktempnm,"[")+'.open_qty = ' + quotename(@SELDBNM,"[") +'.[dbo].'+ quotename(@sktempnm,"[")+'.open_qty + ' + Str(@recp_copenqty-@issu_copenqty-(@recp_ropenqty+@issu_ropenqty),12,3) +', '
Select @execstring = @execstring + quotename(@SELDBNM,"[") +'.[dbo].'+ quotename(@sktempnm,"[")+'.recd_qty = ' + Str(@recp_crecdqty - @recp_rrecdqty,12,3) +', '
Select @execstring = @execstring + quotename(@SELDBNM,"[") +'.[dbo].'+ quotename(@sktempnm,"[")+'.sale_qty = ' + Str(@issu_csaleqty - @issu_rsaleqty,12,3) +', '
Select @execstring = @execstring + quotename(@SELDBNM,"[") +'.[dbo].'+ quotename(@sktempnm,"[")+'.clsg_qty = ' + Str(@prod_clsgqty,12,3)
Select @execstring = @execstring + ' Where Prod_Code = '+ quotename(@mprodcd,"'")
PRINT (@execstring)
Execute (@execstring)

FETCH NEXT FROM Product_Cursor INTO @mprodcd, @prod_openqty

End
CLOSE Product_Cursor
Deallocate Product_Cursor
END

PRINT (@execstring)
Select @execstring = 'Delete from '+ quotename(@SELDBNM,"[") +'.[dbo].'+quotename(@sktempnm,"[") +' Where (open_qty = 0 and recd_qty = 0 and sale_qty = 0 )'
Execute (@execstring)

Commit Transaction StkTran

I AM GETTING FOLLOWING ERROR I TRIED MY BEST:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'KIL1011.dbo.11213 '.

Msg 16916, Level 16, State 1, Procedure JEWELLSTOCKREPORT, Line 108
A cursor with the name 'Product_Cursor' does not exist.
Msg 16916, Level 16, State 1, Procedure JEWELLSTOCKREPORT, Line 110
A cursor with the name 'Product_Cursor' does not exist.
Msg 16916, Level 16, State 1, Procedure JEWELLSTOCKREPORT, Line 165
A cursor with the name 'Product_Cursor' does not exist.
Msg 16916, Level 16, State 1, Procedure JEWELLSTOCKREPORT, Line 166
A cursor with the name 'Product_Cursor' does not exist.

Msg 208, Level 16, State 1, Line 1
Invalid object name 'KIL1011.dbo.11213 '.

(1 row(s) affected)

[buddylee17]

First, please use code blocks when posting code examples.

Example:

[code] --put your code here [/code].

Second, you are using a cursor to loop through and populate dynamic sql. Cursors should be a last resort and the same goes for dynamic sql. This code will be a nightmare to maintain and debug.

That being said, there are occasions that you'll have to use them. I'll assume this is such an occasion.

I'd recommend starting off by replacing Declare @execstring nvarchar (4000) with Declare @execstring nvarchar (MAX) . I have seen issues with dynamic sql where the exec string was too long and got truncated which caused the query to not compile.

I'd also recommend commenting out your execute statements and substituting a PRINT statement. This will give you the static sql string for each loop which you can then paste into the query editor and see what the issue is.

Invalid object name 'KIL1011.dbo.11213 '. Is there an object named 11213 in KIL1011? Try:

Select * From sysobjects Where name like '%11213%'

If this query returns no results, that means the object doesn't exist and you need to take another look at your code.

Post back and let me know how this goes.

[BitBlt]

For the cursors, this looks like a scoping issue. Whenever you create dynamic SQL and execute it, you're creating an "inside" scope. You can't reference anything temporary (your cursor declaration) INSIDE the scope of your exec statement from OUTSIDE it's scope. There is an analogy where you try to create a #temp table inside an execute statement, then can't reference it after the execute finishes. For that example, you can create a ##temp table (global temp table) to get around it...for cursor declaration, not so much.

As far as the invalid table name, we'd need to see the statement you use to call the stored procedure in the first place. Does that database KIL1011 exist? Does the user have permissions?

As a possible next step, maybe you should just dynamically create a separate stored proc, then execute the stored proc, then drop it when you're done. It looks like all the final objects are persistent so you can delete those temporary sp's without repercussions. You just have to make sure your users have the necessary permissions. Also, you should make sure you cobble together sp names that won't stomp on each other if 2 users are trying to run the same procedure at the same time.

I wouldn't personally use this approach, I'd probably try to use temp tables, but I don't know your circumstances.