SQL 2000 Table Variables

[CatParks]

I am using a table variable (as opposed to a temporary table) in a stored procedure. I am calling a cursor from within the proc and it updates the table variable. My problem is that I am being prompted to declare the table variable within the cursor - but I don't want to because it has already been declared within the main proc. Any suggestions?

Thanks,
CP

 

[tlbroadbent]

We need to see you code. You should be able to use a table variable in a cursor declaration. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[CatParks]

THIS IS THE MAIN PROC:

CREATE PROCEDURE dbo.TestTableProc (@PropertyNumber int)
AS
BEGIN
declare @Premium table(
PropertyNumber int,
UnitNumber int,
Unit varchar(10),
Price int null,
Code varchar(20) null,
Description varchar(40) null
);
insert into @Premium
select distinct PropertyNumber,
UnitNumber,
UnitApartmentNumber,
sum(UnitTypePremiumPrice),
null,
null
from UnitPremiumView
where PropertyNumber = @PropertyNumber
group by PropertyNumber, UnitNumber, UnitApartmentNumber;

execute PremiumCursor @PropertyNumber;

select uv.PropertyNumber,
PropertyName,
UnitTypeNumber,
UnitTypeCode,
UnitTypeSquareFeet,
UnitTypeBaseRent,
UnitTypeFloorplan,
uv.UnitNumber,
Unit,
Price,
Code,
Description
from UnitView uv, @Premium pc
where uv.PropertyNumber = @PropertyNumber
and uv.PropertyNumber = pc.PropertyNumber
and uv.UnitNumber = pc.UnitNumber
order by UnitApartmentNumber
END

THIS IS THE CURSOR:

CREATE PROCEDURE dbo.PremiumCursor (@PropertyNumber int)
AS
BEGIN
Declare @Unit varchar(10)
Declare @CodeNext varchar(10)
Declare @CodeList varchar(20)
Declare @DescNext varchar(10)
Declare @DescList varchar(20)

Declare pu1 Cursor For
select distinct UnitApartmentNumber
from UnitPremiumView
for read only

Open pu1

Fetch next From pu1
into @Unit

While @@Fetch_Status = 0
Begin

Declare pc1 Cursor For
select PremiumCode, PremiumDescription
from UnitPremiumView
where UnitApartmentNumber = @Unit
and PropertyNumber = @PropertyNumber
for read only

Open pc1


Fetch Next From pc1
into @CodeNext, @DescNext

select @CodeList = '',
@DescList = ''
While @@Fetch_Status = 0
Begin
select @CodeList = @CodeList + @CodeNext,
@DescList = @DescList + @DescNext

update @Premium
set Code = @CodeList,
Description = @DescList
where Unit = @Unit
and PropertyNumber = @PropertyNumber;


Fetch next from pc1
into @CodeNext,
@DescNext
end

Close pc1
Deallocate pc1

Fetch next from pu1
into @Unit

End

Close pu1
Deallocate pu1

END

 

[tlbroadbent]

I see two problems. The first is the terminology. What you call a cursor is really a procedure that opens a cursor. The second problem is that Table variables cannot be passed as input or output parameters in stored procedures.

You can use a global temporary table.

Another possibility is to create a function that returns a table. See "User-Defined Functions That Return a table Data Type" in SQL BOL. I haven't investigated this yet because we run SQL 7 on all but our test server. However, it looks very promising. Perhaps, someone else in the forum has some experience with these functions. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.