如何在MS SQLServer 存储过程中的Select 语句中检索的表作为参数传入? 恳请各位大哥指教!(200分)

D

div zhang

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-19

• #1

目标是,想在存储过程中以参数形式传寄一个数据表的名称,经过查询,返回一个值.
现在,用一下存储过程来实现,但无法工作,报告@ZMID 变量没有定义.

存储过程如下:

create procedure GetZmIDByBH @TableName Varchar(40),@BH Varchar(40),@ID integer output
as
declare @SQLS varchar(200), @ZMID integer
set SQLS = 'select @ZMID =ID from ' +@TableName + ' where ZMBH =''' + @BH + ''''
exec(@SQLS)
set @ID = @ZMID

 

C

cheka

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-19

• #2

set SQLS = 'select @ZMID =ID from ' +@TableName + ' where ZMBH =''' + @BH + ''''

改为

set SQLS = 'declare @ZMID integerselect '+' select @ZMID =ID from ' +@TableName + ' where ZMBH =''' + @BH + ''''

并把@ZMID在存储过程中的定义去掉

 

C

cheka

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-19

• #3

刚才说得有点问题，那样@ZMID就没法赋给@ID了

试试这样行不行，不要@ID, 在Delphi程序中得到
该Query或是StoredProc的Field

 

G

g622

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-19

• #4

Field[0].value

 

H

Hexi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-19

• #5

用这种方法：
create procedure GetZmIDByBH @TableName Varchar(40),@BH Varchar(40),@ID integer output
as
begin
declare @SQLS varchar(500)
set SQLS = 'create procedure mytempproc
int @Result
as
begin
select @Result =ID from ' +@TableName + ' where
ZMBH =''' + @BH + '''';
end;'
exec(SQL);
exec tempproc @ID;
drop procedure mytempproc;
end

 

H

Hexi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-19

• #6

更正：
exec mytempproc @ID;

 

A

adminis

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-20

• #7

hexi的方案可行，但是有两个地方要加OUTPUT
int @Result output
exec tempproc @ID output

 

D

div zhang

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-04-21

• #8

Hexi的方法可行,但效率比较低.cheka的方法,我再试一试.

经过多方试验,我终于以以下的方法解决了这个问题:

create procedure GetZmIDByBH @TableName Varchar(40),@BH Varchar(40),@ID integer output
as
declare @SQLS nvarchar(200),@ParDefine
set ParDefine = N'@ZMID INT OUTPUT'
set SQLS = N'select @ZMID =ID from ' +@TableName + ' where ZMBH =''' + @BH + ''''
exec sp_executeSQL @SQLS,@ParDefine ,@ID OUTPUT