我写一个函数实现将一列里的数据以;分隔的形式连起来,并用在Select中,但是函数是写出来了,sp_executesql不能在函数中调用,不知道还没有其它的解决办法?请大家帮帮忙,以下这是我写的函数
CREATE FUNCTION uf_GetSARY(@ajbs VARCHAR(64),@SARYTableName VARCHAR(64))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @sql VARCHAR(8000)
DECLARE @NAME VARCHAR(8000)
SET @sql= 'DECLARE @NAME VARCHAR(8000) '+CHAR(13)
SET @[email protected]+ 'SET @NAME= ' ' ' ' '+CHAR(13)
SET @[email protected]+ 'SELECT @[email protected]+ ' '; ' '+F_NAME FROM PT_AJRYBASE INNER JOIN '[email protected]+ ' ON PT_AJRYBASE.F_ID= '[email protected]+ '.F_RYID WHERE '[email protected]+ '.F_AJBS= ' ' '[email protected]+ ' ' ' '+CHAR(13)
SET @[email protected]+ 'SET @NAME=STUFF(@NAME,1,1, ' ' ' ') '
EXECUTE sp_executesql @sql,N '@NAME VARCHAR(8000) output ',@NAME output
RETURN @NAME
END
------解决方案--------------------
改成存储过程实现吧,函数里不能用动态SQL
------解决方案--------------------
使用存儲過程吧。
CREATE PROCEDURE uf_GetSARY(@ajbs VARCHAR(64),@SARYTableName VARCHAR(64), @NAME VARCHAR(8000) output)
AS
BEGIN
DECLARE @sql VARCHAR(8000)
SET @sql= 'DECLARE @NAME VARCHAR(8000) '+CHAR(13)
SET @[email protected]+ 'SET @NAME= ' ' ' ' '+CHAR(13)
SET @[email protected]+ 'SELECT @[email protected]+ ' '; ' '+F_NAME FROM PT_AJRYBASE INNER JOIN '[email protected]+ ' ON PT_AJRYBASE.F_ID= '[email protected]+ '.F_RYID WHERE '[email protected]+ '.F_AJBS= ' ' '[email protected]+ ' ' ' '+CHAR(13)
SET @[email protected]+ 'SET @NAME=STUFF(@NAME,1,1, ' ' ' ') '
EXECUTE sp_executesql @sql,N '@NAME VARCHAR(8000) output ',@NAME output
END
------解决方案--------------------
up
------解决方案--------------------
输出参数 Output