表名为参数的函数该怎么写，以下代码该怎么改？
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 '
Union All Select 10, 2, 'D '
Union All Select 10, 2, 'E '
GO
--創建存儲過程
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 10, 1, 'TEST ', @str output
Select @str
--刪除測試環境
Drop Table TEST
Drop ProceDure SP_get_unitCode_str
--結果
/*
A,B,C
*/
[解决办法]
只能用存储过程创建了
