--在函数中使用动态语句需要使用exec sp_executesql来执行
CREATE FUNCTION ParentFullPath(@ParentTableName as varchar(50),@id as int)
RETURNS varchar(500) AS
BEGIN
declare @strSQL as varchar(500)
Declare @Val varchar(500),@RetVal varchar(50)
Set @RetVal=''
WHILE @id>0
BEGIN
set @strSQL='select @id1=FParentID from @ParentTableName1 where FID=cast(@id2 as varchar(10))'
--exec(@strSQL)--这样也不允许
-- 在函数中调用动态语句要使用sp_executesql
exec sp_executesql @strSQL,N'@id1 int output,@ParentTableName1 varchar(50),@id2 int',
@id1=@id output,@ParentTableName1=@ParentTableName,@id2=@id
--Select @id=FParentID,@Val=isnull(FName,'') From @ParentTableName Where FID=@id --这样不允许
if @id is null
Break
else
Set @RetVal=@Val+case when @RetVal<>'' then '\' else '' end + @RetVal
END
Return(@RetVal)
END
根据brother2605(幽灵)的方法,我的自定义函数如下
CREATE FUNCTION ParentFullPath(@ParentTableName as varchar(50),@id as int)
RETURNS varchar(500) AS
BEGIN
Declare @strSQL nvarchar(800),@name nvarchar(500),@path varchar(50)
Set @path=''
WHILE @id>0
BEGIN
set @strSQL=N'select @id=FParentID,@name=FName from '+@ParentTableName+' where FID='+cast(@id as varchar(10))
execute sp_executesql @strSQL,N'@id int output,@name nvarchar(500) output',@id output,@name output
if @id is null
Break
else
Set @path=@name+case when @path<>'' then '\' else '' end + @path
END
Return(@path)
END
我的函数代码
CREATE FUNCTION ParentFullPath(@ParentTableName as varchar(50),@id as int)
RETURNS varchar(500) AS
BEGIN
declare @strSQL as varchar(500)
Declare @Val varchar(500),@RetVal varchar(50)
Set @RetVal=''
WHILE @id>0
BEGIN
set @strSQL='select @id=FParentID from '+@ParentTableName+' where FID='+cast(@id as varchar(10))
--exec(@strSQL)--这样也不允许
--Select @id=FParentID,@Val=isnull(FName,'') From @ParentTableName Where FID=@id --这样不允许
if @id is null
Break
else
Set @RetVal=@Val+case when @RetVal<>'' then '\' else '' end + @RetVal
END
Return(@RetVal)
END