日期:2014-05-19  浏览次数:20671 次

表名为参数的函数该怎么写,以下代码该怎么改?
CREATE   function   get_unitCode_str   (@max1   int,@min1   int,@tablename   varchar(30))
returns   varchar(5000)
as
begin
declare   @str   varchar(5000),@EvalDepart   varchar(100),@count   int
set   @str= ' '
set   @count=0;
declare   fj11   cursor
for   select   EvalDepart  
from   @tablename   where   EvalLevelmax=@max1   and   EvalLevelmin=@min1
open   fj11
fetch   next   from   fj11   into   @EvalDepart
while   @@fetch_status=0
begin
set   @str=@str+ ', '+@EvalDepart
--set   @count=@count+1
--set   @str=@str+cast(@count   as   varchar)
fetch   next   from   fj11   into   @EvalDepart
end

close   fj11
deallocate   fj11
if   charindex( ', ',@str)> 0
begin
set   @str=substring(@str,2,len(@str)-1)
end
return   @str
end

@tablename是表名称或临时表名称(在存储过程里调用)

------解决方案--------------------
这种想法目前只能被认为是异想天开

------解决方案--------------------
函数不支持动态语句,表名作为函数的参数是没法实现的

------解决方案--------------------
表名是動態的話,必須使用動態SQL語句,而在函數中沒辦法使用動態SQL語句。

樓主,你的需求改用存儲過程實現吧。
------解决方案--------------------
只能改成存储过程,这样试试:
CREATE PROC get_unitCode_str (
@max1 int,@min1 int,@tablename varchar(30) = ' ',
@str varchar(5000) OUTPU /*增加此输出参数,作为存储过程结果*/
)
as
declare @EvalDepart varchar(100),@count int
set @count=0;
declare fj11 cursor for select EvalDepart
from @tablename where EvalLevelmax=@max1 and EvalLevelmin=@min1
open fj11
fetch next from fj11 into @EvalDepart
while @@fetch_status=0
begin
set @str=@str+ ', '+@EvalDepart
--set @count=@count+1
--set @str=@str+cast(@count as varchar)
fetch next from fj11 into @EvalDepart
end
close fj11
deallocate fj11
set @str=CASE WHEN charindex( ', ',@str)> 0 THEN substring(@str,2,len(@str)-1) ELSE @str END
return
GO

----执行存储过程
declare @max1 int,@min1 int,@tablename varchar(30),@str varchar(5000)
set @max1 = ...
EXEC get_unitCode_str @max1,@min1,@tablename,@str OUTPUT
----查看存储过程结果
SELECT @str

------解决方案--------------------
--創建存儲過程
Create ProceDure SP_get_unitCode_str(@max1 int,@min1 int,@tablename varchar(30), @str varchar(8000) output)
As
Begin
Declare @S Nvarchar(4000)
Select @str = ' '
Select @S = 'Select @str = @str + ' ', ' ' + EvalDepart From ' + @tablename + ' where EvalLevelmax= ' + Cast(@max1 As Varchar) + ' and EvalLevelmin= ' + Cast(@min1 As Varchar)
EXEC sp_executesql @S, N '@str Varchar(8000) Output ',@str Output
Select @str = Stuff(@str, 1, 1, ' ')
End
GO
--調用
Declare @str varchar(8000)
EXEC SP_get_unitCode_str 1, 10, 'TableName ', @str output
------解决方案--------------------
--創建測試環境
Create Table TEST(
EvalLevelmax Int,
EvalLevelmin Int,
EvalDepart Varchar(10))
Insert TEST Select 10, 1, 'A '
Union All Select 10, 1, 'B '
Union All Select 10, 1, 'C '