?
Solved

Code working in v6.5, fails in v7 unless compatability mode switched on

Posted on 2003-03-20
3
Medium Priority
?
180 Views
Last Modified: 2006-11-17
This code has been working happily for years!  When I try it in SQL Server 7
if the line a.LineCodeNo = @nLineCodeNo it gets no data but when a.LineCodeNo = 79 is hard coded the data is there!
Any ideas guys?

DROP TABLE T#TaxCapLineCode
GO

CREATE TABLE T#TaxCapLineCode
      (
      LineCodeNo            smallint,
      LineCodeOrderNo            smallint,
      LineCategoryNo            LineCategoryNoType,
      Description            varchar(80)
      )
GO

DROP TABLE T#TaxCapAmount
GO

CREATE TABLE T#TaxCapAmount
      (
      CompanyNo            CompanyNoType,
      APNo                  APNOType,
      LineCodeNo            smallint,
      LineCodeOrderNo            smallint,
      Amount                  AmountType
      )
GO

IF EXISTS (SELECT Object_Id('pr_TaxCap'))
      DROP PROCEDURE pr_TaxCap
GO

CREATE PROCEDURE pr_TaxCap
      (
      @nCompanyNo            CompanyNoType,
      @nAmountColType            smallint
      )
AS
SET NOCOUNT ON

DECLARE
      @nLineCodeNo            smallint,
      @nLineCodeOrderNo      smallint,
      @nLineCodeCategoryNo      LineCategoryNoType,
      @sLineCodeDescription      varchar(80),

      @nFetchStatusLineCode      smallint,

      @nAmountCompanyNo      CompanyNoType,
      @nAmountAPNo            APNoType,
      @nAmountLineCodeNo      smallint,
      @nAmountLineCodeOrderNo      smallint,
      @nAmountAmount            AmountType,

      @nFetchStatusAmount      smallint,

      @nAmountCol            smallint,
      @nAmountColX            smallint,
      @nAmountColY            smallint,

      @nNonZeroAmount            smallint,

      @TRUETRUE            smallint,
      @FALSEFALSE            smallint,

      @nCol                  smallint,
      @nRow                  smallint,
      @sCellValue            varchar(120)

DECLARE      cr_LineCode SCROLL CURSOR FOR
      SELECT
            LineCodeNo,
            LineCodeOrderNo,
            LineCategoryNo,
            Description
      FROM
            T#TaxCapLineCode a
      ORDER BY
            2

DECLARE      cr_Amount SCROLL CURSOR FOR
      SELECT
            CompanyNo,
            APNo,
            LineCodeNo,
            LineCodeOrderNo,
            Amount
      FROM
            T#TaxCapAmount a
      WHERE
            a.CompanyNo = @nCompanyNo
      AND      
            a.LineCodeNo = @nLineCodeNo
--            a.LineCodeNo = 79

DELETE FROM T#TaxCapLineCode

INSERT INTO T#TaxCapLineCode
      SELECT
            a.LineCodeNo,
            b.OrderNo,
            (SELECT c.LineCategoryNo FROM LineCodeCategory c WHERE a.LineCodeNo = c.LineCodeNo
            AND c.LineCategoryNo IN (0,1)),
            a.Description
      FROM
            LineCode a, LineCodeCategory b
      WHERE
            a.LineCodeNo = b.LineCodeNo
      AND
            b.LineCategoryNo = 6
      ORDER BY
            2

DELETE FROM T#TaxCapAmount

INSERT INTO T#TaxCapAmount
      SELECT
            1007,
            a.APNo,
            b.LineCodeNo,
            b.LineCodeOrderNo,
            0
      FROM
            AP a, T#TaxCapLineCode b
      WHERE
            a.CompanyNo = 1007

UPDATE T#TaxCapAmount SET
      Amount = c.Amount
FROM
      T#TaxCapAmount t,
      AP a,
      T#TaxCapLineCode b,
      LineCodeInput c
WHERE
      t.CompanyNo = c.CompanyNo
AND
      t.APNo = c.APNo
AND
      t.LineCodeNo = c.LineCodeNo
AND
      a.CompanyNo = c.CompanyNo
AND
      a.APNo = c.APNo
AND
      b.LineCodeNo = c.LineCodeNo
AND
      b.LineCategoryNo = 0
AND
      a.CompanyNo = 1007

UPDATE T#TaxCapAmount SET
      Amount = c.AmountX
FROM
      T#TaxCapAmount t,
      AP a,
      T#TaxCapLineCode b,
      LineCodeCalculate c
WHERE
      t.CompanyNo = c.CompanyNo
AND
      t.APNo = c.APNo
AND
      t.LineCodeNo = c.LineCodeNo
AND
      a.CompanyNo = c.CompanyNo
AND
      a.APNo = c.APNo
AND
      b.LineCodeNo = c.LineCodeNo
AND
      b.LineCategoryNo = 1
AND
      a.CompanyNo = 1007

SELECT
      CompanyNo,
      APNo,
      LineCodeNo,
      LineCodeOrderNo,
      Amount
FROM
      T#TaxCapAmount a
WHERE
      CompanyNo = 1007
AND
      LineCodeNo = 79

SELECT @nCompanyNo = 1007, @nLineCodeNo = 79
SELECT "@nCompanyNo = ",@nCompanyNo
SELECT "@nLineCodeNo = ",@nLineCodeNo

OPEN cr_Amount

FETCH FIRST FROM cr_Amount INTO
      @nAmountCompanyNo,
      @nAmountAPNo,
      @nAmountLineCodeNo,
      @nAmountLineCodeOrderNo,
      @nAmountAmount

SELECT @nFetchStatusAmount = @@FETCH_STATUS

select       "cr_Amount", @nAmountCompanyNo,      @nAmountAPNo, @nAmountLineCodeNo, @nAmountLineCodeOrderNo, @nAmountAmount

SELECT @nFetchStatusAmount

CLOSE cr_Amount

DEALLOCATE cr_Amount

DEALLOCATE cr_LineCode

RETURN 0

SET NOCOUNT OFF
GO

GRANT EXECUTE ON pr_TaxCap TO Development
GO
GRANT EXECUTE ON pr_TaxCap TO GroupTax
GO

EXECUTE pr_TaxCap 1007,1
GO

CompanyNo   APNo   LineCodeNo LineCodeOrderNo Amount            
----------- ------ ---------- --------------- -----------------
1007        1      79         1               172172966.00
1007        2      79         1               391191030.00
1007        3      79         1               681778625.00
1007        4      79         1               757373346.00
1007        5      79         1               798477987.00
1007        6      79         1               514067984.00
1007        7      79         1               512468651.00
1007        8      79         1               198844008.00
1007        9      79         1               123968772.00
1007        10     79         1               75812375.00
1007        11     79         1               208189020.00
1007        12     79         1               393989000.00
1007        13     79         1               1406271000.00
1007        14     79         1               947468000.00
1007        15     79         1               2367227000.00
1007        16     79         1               2122024000.00
1007        17     79         1               2153175000.00
1007        18     79         1               1222752000.00
1007        19     79         1               1071000000.00
1007        20     79         1               1200000000.00

                           
-------------- -----------
@nCompanyNo =  1007

                       
--------------- ------
@nLineCodeNo =  79

                                                             
--------- ----------- ------ ------ ------ -----------------
cr_Amount NULL        NULL   NULL   NULL   NULL

       
------
-1

0
Comment
Question by:barnesd1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 

Expert Comment

by:tmorell
ID: 8173717
Try setting the value @nLineCodeNo before the declare of the cr_Amount cursor and see if that takes care of the issue.
0
 
LVL 4

Author Comment

by:barnesd1
ID: 8173988
It would appear that there is a bug with SQL Server 7.  According to MS Knowledge Base Article 217032, Variable Arguments are not refreshed after cursor is declared.  What a nuisance.  Has anyone else has this?
0
 
LVL 9

Accepted Solution

by:
xenon_je earned 600 total points
ID: 8175077
The value you wanna pass to an declare cursor has to be set before this statement. So you have to do something like this:
****
declare @xx int
//some code that initializez the @xx
declare cursor ...that uses @xx
*******

if you do something like
declare cursor ...that uses @xx--it will use the value just setted before, and not the value of @xx seted before the open my_cursor.....
sorry...you have to change a little bit your code...

gl   xenon
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question