我被指派在SQL Server中编写过程。我在case语句中对过程的代码使用了一些查询。不知何故，SSMS告诉查询中使用的表是无效的。我尝试过刷新本地缓存，但没有成功。我必须做什么必要的改变？
以下是错误的程序代码：
CREATE TABLE cconvert (
lc_id INT PRIMARY KEY
,lc_name VARCHAR(30) NOT NULL UNIQUE
,ac_name VARCHAR(30) NOT NULL
,cf FLOAT
)
CREATE TABLE invoice_amount (
inv_id INT PRIMARY KEY
,inv_amt FLOAT NOT NULL
,lc_name VARCHAR(30) NOT NULL
,ac_name VARCHAR(30) NOT NULL
,conv_inv_amt FLOAT
)
INSERT INTO cconvert
VALUES (
1
,'USD'
,'USD'
,1
)
INSERT INTO cconvert
VALUES (
2
,'British Pound'
,'USD'
,0.637989
)
INSERT INTO cconvert
VALUES (
3
,'Indian Rupee'
,'USD'
,63.511192
)
INSERT INTO cconvert
VALUES (
4
,'Australian Dollar'
,'USD'
,1.264736
)
INSERT INTO cconvert
VALUES (
5
,'Canadian Dollar'
,'USD'
,1.218956
)
INSERT INTO cconvert
VALUES (
6
,'Singapore Dollar'
,'USD'
,1.330278
)
INSERT INTO cconvert
VALUES (
7
,'Swiss Franc'
,'USD'
,0.933432
)
INSERT INTO cconvert
VALUES (
8
,'Malaysian Ringgit'
,'USD'
,3.584753
)
INSERT INTO cconvert
VALUES (
9
,'Japanese Yen'
,'USD'
,120.762671
)
INSERT INTO cconvert
VALUES (
10
,'Chinese Yuan Renminbi'
,'USD'
,6.199559
)
SELECT *
FROM cconvert
SELECT *
FROM invoice_amount
INSERT INTO invoice_amount
VALUES (
1
,16880.16000
,'British Pound'
,'USD'
,NULL
)
INSERT INTO invoice_amount
VALUES (
2
,30035.19000
,'Indian Rupee'
,'USD'
,NULL
)
INSERT INTO invoice_amount
VALUES (
3
,2550.71000
,'Australian Dollar'
,'USD'
,NULL
)
INSERT INTO invoice_amount
VALUES (
4
,21.33000
,'Canadian Dollar'
,'USD'
,NULL
)
INSERT INTO invoice_amount
VALUES (
5
,38560.78000
,'Singapore Dollar'
,'USD'
,NULL
)
INSERT INTO invoice_amount
VALUES (
6
,38560.78000
,'Swiss Franc'
,'Swiss Franc'
,NULL
)
INSERT INTO invoice_amount
VALUES (
7
,98.84000
,'Malaysian Ringgit'
,'Malaysian Ringgit'
,NULL
)
INSERT INTO invoice_amount
VALUES (
8
,30583.38000
,'Japanese Yen'
,'USD'
,NULL
)
INSERT INTO invoice_amount
VALUES (
9
,3468.9000
,'Chinese Yuan Renminbi'
,'USD'
,NULL
)
INSERT INTO invoice_amount
VALUES (
10
,16300.55000
,'USD'
,'USD'
,NULL
)
DELETE
FROM cconvert
DROP TABLE cconvert
GO
CREATE PROCEDURE show_invoice_details
AS
BEGIN
DECLARE @table_size INT
,@i INT
,@lc_name VARCHAR(30)
,@ac_name VARCHAR(30)
,@conv_inv_amt FLOAT
,@cf FLOAT
,@inv_amt FLOAT
DECLARE inv_id_cursor CURSOR
FOR
SELECT *
FROM invoice_amount
ORDER BY inv_id
OPEN inv_id_cursor
FETCH inv_id_cursor
INTO @i
,@inv_amt
,@lc_name
,@ac_name
,@conv_inv_amt
WHILE @@FETCH_STATUS = 0
BEGIN
IF @conv_inv_amt IS NOT NULL
BEGIN
FETCH inv_id_cursor
INTO @i
,@inv_amt
,@lc_name
,@ac_name
,@conv_inv_amt
CONTINUE
END
ELSE
BEGIN
IF @lc_name = @ac_name
SET @cf = 1
ELSE
BEGIN
SET @cf = CASE
WHEN @lc_name = 'British Pound'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN @lc_name = 'Indian Rupee'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN @lc_name = 'Australian Dollar'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN @lc_name = 'Canadian Dollar'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN @lc_name = 'Singapore Dollar'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN @lc_name = 'Swiss Franc'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN @lc_name = 'Malaysian Ringgit'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN @lc_name = 'Japanese Yen'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN @lc_name = 'Chinese Yuan Renminbi'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN @lc_name = 'USD'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
END
SET @conv_inv_amt = @inv_amt * @cf
UPDATE invoice_amount
SET conv_inv_amt = @conv_inv_amt
END
END
CLOSE inv_id_cursor
DEALLOCATE inv_id_cursor
SELECT *
FROM invoice_amount
END
GO
EXEC show_invoice_details
DROP show_invoice_details GO
查看：
drop table cconvert
...
when @lc_name = 'British Pound' then (select cf from cconvert where lc_name = @lc_name)
...
PROCEDURE中有一些语法错误>请查找更新的代码
CREATE PROCEDURE show_invoice_details
AS
BEGIN
DECLARE @table_size INT
,@i INT
,@lc_name VARCHAR(30)
,@ac_name VARCHAR(30)
,@conv_inv_amt FLOAT
,@cf FLOAT
,@inv_amt FLOAT
DECLARE inv_id_cursor CURSOR
FOR
SELECT *
FROM invoice_amount
ORDER BY inv_id
OPEN inv_id_cursor
FETCH inv_id_cursor
INTO @i
,@inv_amt
,@lc_name
,@ac_name
,@conv_inv_amt
WHILE @@FETCH_STATUS = 0
BEGIN
IF @conv_inv_amt IS NOT NULL
BEGIN
FETCH inv_id_cursor
INTO @i
,@inv_amt
,@lc_name
,@ac_name
,@conv_inv_amt
CONTINUE
END
ELSE
BEGIN
IF @lc_name = @ac_name
SET @cf = 1
ELSE
BEGIN
SET @cf = CASE @lc_name
WHEN 'British Pound'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN 'Indian Rupee'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN 'Australian Dollar'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN 'Canadian Dollar'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN 'Singapore Dollar'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN 'Swiss Franc'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN 'Malaysian Ringgit'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN 'Japanese Yen'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN 'Chinese Yuan Renminbi'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
WHEN 'USD'
THEN (
SELECT cf
FROM cconvert
WHERE lc_name = @lc_name
)
END
SET @conv_inv_amt = @inv_amt * @cf
UPDATE invoice_amount
SET conv_inv_amt = @conv_inv_amt
END
END
CLOSE inv_id_cursor
END
DEALLOCATE inv_id_cursor
END