我写一个函数实现将一列里的数据以;分隔的形式连起来,并用在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 @sql=@sql+'SET @NAME='''''+CHAR(13)
SET @sql=@sql+'SELECT @NAME=@NAME+'';''+F_NAME FROM PT_AJRYBASE INNER JOIN '+@SARYTableName+' ON PT_AJRYBASE.F_ID='+@SARYTableName+'.F_RYID WHERE '+@SARYTableName+'.F_AJBS='''+@ajbs+''''+CHAR(13)
SET @sql=@sql+'SET @NAME=STUFF(@NAME,1,1,'''')'
EXECUTE sp_executesql @sql,N'@NAME VARCHAR(8000) output',@NAME output
RETURN @NAME
END
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @sql VARCHAR(8000)
DECLARE @NAME VARCHAR(8000)
SET @sql='DECLARE @NAME VARCHAR(8000)'+CHAR(13)
SET @sql=@sql+'SET @NAME='''''+CHAR(13)
SET @sql=@sql+'SELECT @NAME=@NAME+'';''+F_NAME FROM PT_AJRYBASE INNER JOIN '+@SARYTableName+' ON PT_AJRYBASE.F_ID='+@SARYTableName+'.F_RYID WHERE '+@SARYTableName+'.F_AJBS='''+@ajbs+''''+CHAR(13)
SET @sql=@sql+'SET @NAME=STUFF(@NAME,1,1,'''')'
EXECUTE sp_executesql @sql,N'@NAME VARCHAR(8000) output',@NAME output
RETURN @NAME
END
AS
BEGIN
DECLARE @sql VARCHAR(8000)
SET @sql='DECLARE @NAME VARCHAR(8000)'+CHAR(13)
SET @sql=@sql+'SET @NAME='''''+CHAR(13)
SET @sql=@sql+'SELECT @NAME=@NAME+'';''+F_NAME FROM PT_AJRYBASE INNER JOIN '+@SARYTableName+' ON PT_AJRYBASE.F_ID='+@SARYTableName+'.F_RYID WHERE '+@SARYTableName+'.F_AJBS='''+@ajbs+''''+CHAR(13)
SET @sql=@sql+'SET @NAME=STUFF(@NAME,1,1,'''')'
EXECUTE sp_executesql @sql,N'@NAME VARCHAR(8000) output',@NAME output
END
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 @sql=@sql+'SET @NAME='''''+CHAR(13)
SET @sql=@sql+'SELECT @NAME=@NAME+'';''+F_NAME FROM PT_AJRYBASE INNER JOIN '+@SARYTableName+' ON PT_AJRYBASE.F_ID='+@SARYTableName+'.F_RYID WHERE '+@SARYTableName+'.F_AJBS='''+@ajbs+''''+CHAR(13)
SET @sql=@sql+'SET @NAME=STUFF(@NAME,1,1,'''')'
EXECUTE sp_executesql @sql,N'@NAME VARCHAR(8000) output',@NAME output
END
GO--調用
Declare @NAME VARCHAR(8000)
EXEC uf_GetSARY 'A', 'ABA', @NAME Output
Select @NAME