Question

写一个存储过程，输入表名然后显示表中的数据。

create or replace procedure show_data
(
tablename in varchar(50)
)
as
begin
execute immediate 'select * from '+tablename;
end;
/
怎么是错的

Answer 1

额
CREATE PROCEDURE P_TEST(
@tableName VARCHAR(50) --查询的表名
)
AS
BEGIN
DECLARE @strSQL VARCHAR(1000)
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = Object_Id(N'' + @tableName + '') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1)
BEGIN
SELECT 0
RETURN
END
SELECT @strSQL = 'SELECT * FROM ' + @tableName
EXEC(@strSQL)
END
执行P_TEST 表名

Answer 2

CREATE PROC showData(@tableName nvarchar(50))
AS
BEGIN
EXEC('SELECT * FROM ' + @tableName)
END

追问

create or replace procedure show_data
(
tablename in varchar(50),
)
as
begin
execute immediate 'select * from '+tablename;
end;
/
为什么是错的啊

追答

create or replace procedure show_data
(
@tablename in varchar(50), --变量要有 @
)
as
begin
execute immediate 'select * from ' + @tablename;
end;