Issue with variable name in Cursor Fetch!

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

Issue with variable name in Cursor Fetch!

Author

Topic

Orbison
Starting Member

14 Posts

Posted - 2009-11-30 : 10:58:29

Hi All,
I have the following fetch statement which returns the correct results when i hardcode in the
value of a particular Product ID instead of using the variable name @Input_Product_ID. When i retrieve
the @Input_Product_ID from a spreadsheet and display its contents before the fetch it looks fine, however when the fetch
gets executed the SQL fetch status returns -1. Both @Input_Product_ID and Product.Product_ID fields
are defined as an INT. Any ideas why i dont get the same results?

Select * from Fact, Product, Commodity, Store, Detail, Section, Department
Where Fact.Store_ID in (1, 2, 3, 4) and
Fact.Commodity_ID = Commodity.Commodity_ID and
Commodity.Section_ID = Section.Section_ID and
Section.Dept_ID = Department.Dept_ID and
Fact.Store_ID = Store.Store_ID and
Fact.Detail_ID = Detail.Detail_ID and
Fact.Product_Desc_ID = (Select Fact.Product_Desc_ID FROM Fact
Where Fact.Backup_ID = Product.Backup_ID and
Product.Product_ID = @Input_Product_ID);

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 11:00:17

how are you assigning value to @Input_Product_ID? can you post that part?

Orbison
Starting Member

14 Posts

Posted - 2009-11-30 : 11:10:52

Hi,
I'm reading the spreadsheet into a Temporary Table and fetching each record within a loop which appears
to be working fine!

--Load Input spreadsheet into Temporary Table

Insert into #InputTable
(Input_Product_ID)
SELECT A.*
FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Orbison\Excel Files\Products.xls;',
'select * from [Sheet1$]') AS A

--Declare Cursors

Declare ReadInptble CURSOR FOR
Select Input_Product_ID
from #InputTable

Open ReadInptble
Open AllProducts

--Fetch from ReadInptble;
Fetch from ReadInptble into @Input_Product_ID;
Select @@FETCH_STATUS

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-11-30 : 12:51:47

a) where is allproducts coming from... it doesn't seem to be defined.
b) read up on numerous horror stories re cursors.

(give yourself plenty of time)

c) what are you trying to do in non-technical terms. we may be able to suggest a non-cursor solution which would be far faster and far more reliable.

Orbison
Starting Member

14 Posts

Posted - 2009-12-01 : 04:26:47

Hi Andrew,
Thanks for your response and i agree cursors shouldn't be used unless you know exactly what your doing! In my case i need to read one record at time and manipulate specific fields etc... so a
Cursor is a very useful option.

Allproducts is the name of the Cursor i initally posted.

Cheers

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-01 : 04:32:57

Hi

try this..instead of cursor


DECLARE @INT				INT
DECLARE @INPUT_PRODUCT_ID	INT

SET @INT	=	1

CREATE TABLE #INPUTTABLE(ID INT IDENTITY(1,1),INPUT_PRODUCT_ID INT)--DATATYPE YOUR WISH

INSERT INTO #INPUTTABLE
(INPUT_PRODUCT_ID)
SELECT A.* 
FROM OPENROWSET ('MICROSOFT.JET.OLEDB.4.0',
'EXCEL 8.0;DATABASE=C:\ORBISON\EXCEL FILES\PRODUCTS.XLS;',
'SELECT * FROM [SHEET1$]') AS A 


WHILE @INT <= ISNULL((SELECT COUNT(*) FROM #INPUTTABLE),0)
BEGIN
		SELECT @INPUT_PRODUCT_ID	=	INPUT_PRODUCT_ID 
		FROM	#INPUTTABLE 
		WHERE ID	=	@INT
		
		SELECT @INPUT_PRODUCT_ID

		SET @INT	=	@INT	+	1

END

-------------------------
R...

Orbison
Starting Member

14 Posts

Posted - 2009-12-01 : 06:37:34

Hi Rajdaksha,
Thanks for your reply, i tried it without using the first cursor to read the Excel sheet (as per your example above) but i'm getting the same result. Again, the value of @INPUT_PRODUCT_ID looks fine before i fetch the second cursor which returns a status of -1.

There must be something wrong with with the following:

Select * from Fact, Product, Commodity, Store, Detail, Section, Department
Where Fact.Store_ID in (1, 2, 3, 4) and
Fact.Commodity_ID = Commodity.Commodity_ID and
Commodity.Section_ID = Section.Section_ID and
Section.Dept_ID = Department.Dept_ID and
Fact.Store_ID = Store.Store_ID and
Fact.Detail_ID = Detail.Detail_ID and
Fact.Product_Desc_ID = (Select Fact.Product_Desc_ID FROM Fact
Where Fact.Backup_ID = Product.Backup_ID and
Product.Product_ID = @Input_Product_ID);

My problem is i cant achieve the above using a select Statement as i need to output a number of different fields from the different records returned onto one single output record.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources