本来的存储过程是下面这样
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info' AND type = 'P')
DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
我把它改为如下的形式为什么不行呢?我的目的是把要查询表格也作为参数来传递
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info' AND type = 'P')
DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20),@TableName varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM @TableName a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info' AND type = 'P')
DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
我把它改为如下的形式为什么不行呢?我的目的是把要查询表格也作为参数来传递
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info' AND type = 'P')
DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20),@TableName varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM @TableName a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
USE pubs
go
create table tb(au_lname int, au_fname varchar(100))
insert into tb select 1,'a'
go
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info' AND type = 'P')
DROP PROCEDURE au_info
GO GO
alter PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20),@TableName varchar(20)
AS
begin
declare @sql varchar(1000)
set @sql='SELECT au_lname, au_fname FROM '+@TableName+' WHERE au_fname = '''+@firstname+'''
AND au_lname ='''+@lastname+''''
exec(@sql)
end
GO
exec au_info 1,'a','tb'
go
drop table tb
我把连接省略了,方法一致。。
go
create table tb(au_lname int, au_fname varchar(100))
insert into tb select 1,'a'
go
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info' AND type = 'P')
DROP PROCEDURE au_info
GO GO
alter PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20),@TableName varchar(20)
AS
begin
declare @sql varchar(1000)
set @sql='SELECT au_lname, au_fname FROM '+@TableName+' WHERE au_fname = '''+@firstname+'''
AND au_lname ='''+@lastname+''''
exec(@sql)
end
GO
exec au_info 1,'a','tb'
go
drop table tb
/*
au_lname au_fname
----------- ----------------------------------------------------------------------------------------------------
1 a(所影响的行数为 1 行)
*/