当表名和字段名作为变量输入时,在存储过程中如何生成查询语句?

我想做一个通用的存储过程,目的是根据上级编码产生下级编码；
声明如下:
CREATE PROCEDURE pub_createID(
  @TableName nvarchar(100),
  @FieldName nvarchar(100),
  @UpperLevelID nvarchar(100),
  @CurrentID nvarchar(100), OUTPUT
)
其中TableName为表名,FieldName为字段名,我应该如何写查询语句?

直接写不行,会出错:
SELECT * FROM @TableName WHERE @FieldName LIKE '@UpperLevelID%'

如果用sp_executesql系统存储过程虽然可执行,但我取不到记录集：
SELECT @SQLString=N'SELECT * FROM '+@TableName+' WHERE '+@FieldName
  +' like '+@UpperLevelID
exec @sp_result=sp_executesql @SQLString

怎么办？请高手指点！

20 个解决方案

#1

比如根据表名取得结果集

create proc getrcds(@tb varchar(10))
as
exec ('select * from '+@tb)

执行时用exec getrcds 'pubs.dbo.sales'

#2

没有这么简单,我的处理绝不止这一句话exec ('select * from '+@tb),是一段很长的处理过程,用这种方法只能写一句啊!

#3

但最终你要形成一个字符串的,最后通过EXEC (@SQL)就可以返回相应的结果集.
如果我的理解有错,你的情况是什么?

#4

啊,你提醒了我,现在我想到可以先做一个子存储过程把传递的变量组合成一个完整的SQL语句,然后再把结果返回给调用它的存储过程.我先试试.

#5

用dbms_sql试一试。

#6

还是不行!
我的情况是这样的:
在select出记录后,还要逐条记录进行处理,中间的过程较多,不象你举例的那么简单.

create proc getrcds(@tb varchar(10))
as
--这里我要加入定义
exec ('select * from '+@tb)
--这里我还要处理记录集

再指点指点?

#7

to:TomHan(小马哥)
dbms_sql是什么?哪有帮助?

#8

dbms_sql是用来处理动态pl/sql的包。
我写了一个过程，你看看对你是否有帮助。
create or replace procedure dbms_sql_test
(
in_table in varchar2,
in_column in varchar2
)
as

v_cursor number;
v_sql varchar2(200);
v_get varchar2(20);
v_fetch number;
v_dummy number;

begin
v_cursor :=dbms_sql.open_cursor;

v_sql:='  select '||in_column||' from '||in_table;

dbms_output.put_line(v_sql);--在屏幕上显示拼成的sql语句。
dbms_sql.parse(v_cursor,v_sql,dbms_sql.v7);
dbms_sql.define_column(v_cursor,1,v_get,20);
v_dummy :=dbms_sql.execute(v_cursor);

loop
    if dbms_sql.fetch_rows(v_cursor)=0 then
       exit;
    end if;
    dbms_sql.column_value(v_cursor,1,v_get);
    dbms_output.put_line(v_get);--在屏幕上显示每次fetch的结果。
end loop;

dbms_sql.close_cursor(v_cursor);

end;

此过程的作用是向过程传递进表名和列名，在过程中拼出要查询的sql语句，并执行，用游标得到结果集。

如有问题在贴上来。

#9

dbms_sql是oracle的东东。。。
把结果集插入临时表，试一试

#10

没有那么烦

用exec执行动态语句

例如
CREATE PROCEDURE pub_createID
  @TableName nvarchar(100),
  @FieldName nvarchar(100),
  @UpperLevelID nvarchar(100),
  @CurrentID nvarchar(100), OUTPUT
as 
SET NOCOUNT ON
exec('select '+@FieldName +' from '+@TableName +' where 条件')
return

如果条件也是动态的，也可以继续这样加，@FieldName 中要查的字段名已逗号隔开就是了

#11

看起来dbms_sql真是个好东东,可惜我用的是SQL Server7.0,没有调试环境.

#12

I am sorry!

#13

你要对你查出的记录集做什么样的处理？
看看可不可以在查询的时候同时进行。

#14

to hughie(雨神):
我找到记录后,要逐个判断,不易在一句话内写清.
to signboy(横):
我用临时表似乎可以,先用我上面所提到的sp_executesql系统存储过程,把查询结果写在一个临时表中,然后要对临时表怎么操作都可以了.好在我只要取得一个结果,不会再对原来的表写数据,要不还真不知道怎么做.

#15

hanhf(程序狗)　
你要逐个判断的是一个字段，还是什么？
能说说吗？

#16

不知道你的目的是什么？

#17

不知道你的目的是什么？

#18

给你一个解决方案!
第一、建立第一个存储过程，产生一个结果集
alter PROCEDURE pub_createID(
  @TableName nvarchar(100),
  @FieldName nvarchar(100),
  @UpperLevelID nvarchar(100)
)
as
declare 
@sql varchar(200)
begin
select @sql="select  "+@fieldname+" from "+@tablename+" where powerplantcode='"+@UpperLevelID+"'"
exec(@sql)
end
第二、建立第二个存储过程，对结果集进行操作！
create procedure #kkkk
  @TableName nvarchar(100),
  @FieldName nvarchar(100),
  @UpperLevelID nvarchar(100)
as
begin
create table #kkk
()
/*创建临时表*/
insert into #kkk
exec pub_createID @TableName,@FieldName,@UpperLevelID
/*产生结果集*/
/*哈哈可以进行别的操作*/
end
第三、最后调用
declare
  @TableName nvarchar(100),
  @FieldName nvarchar(100),
  @UpperLevelID nvarchar(100)
begin
select @tablename='powerplant',@fieldname='powerplantname',@UpperLevelID='200000'
exec #kkkK  @tablename,@fieldname,@UpperLevelID
end
这是基于我的一个表结构 进行的操作！
看看满足你的要求吗？
如果对！一定要给我分啊？
哈哈！

#19

to:wwl007(疑难杂症)
真的很厉害,我还有最后的疑问:
1.创建的临时表最后要不要删除?
2.如果这个存储过程被多个进程程同时调用的话临时表重名会不会有影响?

#20

这个问题是！临时表在你的调用结束以后就没有了！
因为#指的是一个局部变量！
##指的是全局变量！
如果你知道什么是全局变量什么是局部变量！这个问题我就不用说了！
第 二个问题1如果你用的局部变量！因该没有问题！

#21

• 对于存储过程中表名是变量的游标使用