无法删除存储过程中临时表的一列
kane 2003-10-19 01:32:12 给临时表动态增加列没有问题,但是删除一列时,发生错误,提示
列名 'LID' 无效。
存储过程如下:
ALTER PROCEDURE upLocationTextBookDetailByDate
(
@start datetime,
@end datetime
)
AS
CREATE TABLE #LocationTextBookDetail
(
LID int NULL,
教材编码 varchar(60) NULL,
教材名称 varchar(60) NULL,
单价 float NULL
)
Create Table #TextBookNumber
(
TextBookID int NOT NULL,
Num int NULL
)
--动态给临时表#TextBookNumber增加列
DECLARE @locationName varchar(60), @locationID int,@strsql varchar(100)
DECLARE location_cursor CURSOR FOR SELECT LocationName FROM LocationInfo
OPEN location_cursor
FETCH location_cursor INTO @locationName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('ALTER TABLE #LocationTextBookDetail ADD ' + @locationName + ' int NOT NULL DEFAULT (0)')
FETCH location_cursor INTO @locationName
END
CLOSE location_cursor
DEALLOCATE location_cursor
--end-----------
--修改临时表中数据
INSERT INTO #LocationTextBookDetail(LID, 教材编码, 教材名称, 单价) (SELECT TextBookID,TextBookNumber,TextBookName,
TextBookPrice FROM TextBook WHERE TextBookID IN (select distinct TextBookID from Plan_Course_TextBook where PC_ID IN
(select PC_ID from Plan_Course where
CourseID IN (select distinct CourseID from ElectiveCourse where
datediff(d,@start,ElectiveTime)>=0 and datediff(d,@end,ElectiveTime)<=0 and TextBookYN = 1))))
DECLARE location_cursor CURSOR FOR SELECT LocationName,LocationID FROM LocationInfo
OPEN location_cursor
FETCH location_cursor INTO @locationName, @locationID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TextBookNumber EXEC upLocationGetTextBookByDate @locationID,@start,@end
SELECT @strsql = 'EXEC upLocationGetTextBook ' + CAST(@locationID AS varchar(2))
-- EXEC('UPDATE #LocationTextBookDetail SET ' + @locationName + '=A.NUM (' + @strsql +
-- ') AS A WHERE #LocationTextBookDetail.TextBookID = A.TextBookID')
EXEC('UPDATE #LocationTextBookDetail SET ' + @locationName + '=(SELECT NUM FROM #TextBookNumber
WHERE #LocationTextBookDetail.LID = #TextBookNumber.TextBookID) WHERE LID IN
(SELECT TextBookID FROM #TextBookNumber)')
FETCH location_cursor INTO @locationName, @locationID
DELETE #TextBookNumber
END
CLOSE location_cursor
DEALLOCATE location_cursor
--end--------------
--执行这句错误,没有这条语句执行正确
ALTER TABLE #LocationTextBookDetail DROP COLUMN LID
SELECT * FROM #LocationTextBookDetail
DROP TABLE #TextBookNumber
DROP TABLE #LocationTextBookDetail