函数内如何执行动态SQL语句 问题描述: 我需要通过表单编号来找到表单信息具体存在哪个表,然后在那个表检索出一信息,我是这样做的,定义一个函数,输入变量为表单编号@FormID,声明变量@TableID,然后通过拼接SQL语句再执行的方式,来找一个叫SPValue的字段,但是想了很多方法,都不能在函数内把这个变量输出,用以下的方法,会报一个‘只有函数和某些扩展存储过程才能从函数内部执行’的异常,请大神指引 具体语句: ALTER FUNCTION [dbo].[FUN_YSCL_GetSpvalue] ( -- Add the parameters for the function here @FormID VARCHAR(200) ) RETURNS VARCHAR(20) AS BEGIN
DECLARE @TableID VARCHAR(20)
SELECT @TableID = tb.vcTableID FROM tG10Money tm LEFT JOIN tG10Tables tb ON tm.iFormType = tb.ID WHERE vcFormID = @FormID
DECLARE @strSQL NVARCHAR(2000) SET @strSQL = '' --SET @strSQL = ' INSERT INTO tSpvalue_temp(formid,spvalue)' SET @strSQL += ' SELECT ' SET @strSQL += '@spvalue=tb.SPValue' SET @strSQL += ' FROM ' + @TableID + ' tb' SET @strSQL += ' RIGHT JOIN ( SELECT DISTINCT' SET @strSQL += ' FormID ,' SET @strSQL += ' MAX(WFNodeIndex) AS maxnode' SET @strSQL += ' FROM tFS1001' SET @strSQL += ' WHERE RowType = 1' SET @strSQL += ' GROUP BY WFID ,' SET @strSQL += ' FormID' SET @strSQL += ' ) maxnode' SET @strSQL += ' ON tb.FormID = maxnode.FormID' SET @strSQL += ' AND tb.WFNodeIndex = maxnode.maxnode' SET @strSQL += ' WHERE tb.FormID= @FormID '
--函数中不能调用动态SQL,还是用存储过程吧。如果还要对函数做其他操作,换成存储过程不方便,可以考虑把其他操作一起封装在存储过程里面啊。如:
create proc [dbo].[FUN_YSCL_GetSpvalue]
@FormID VARCHAR(200)
AS
BEGIN
DECLARE @TableID VARCHAR(20)
SELECT @TableID = tb.vcTableID FROM tG10Money tm LEFT JOIN tG10Tables tb ON tm.iFormType = tb.ID WHERE vcFormID = @FormID
DECLARE @strSQL NVARCHAR(2000)
SET @strSQL = ''
SET @strSQL += ' SELECT '
SET @strSQL += '@spvalue=tb.SPValue'
SET @strSQL += ' FROM ' + @TableID + ' tb'
SET @strSQL += ' RIGHT JOIN ( SELECT DISTINCT'
SET @strSQL += ' FormID ,'
SET @strSQL += ' MAX(WFNodeIndex) AS maxnode'
SET @strSQL += ' FROM tFS1001'
SET @strSQL += ' WHERE RowType = 1'
SET @strSQL += ' GROUP BY WFID ,'
SET @strSQL += ' FormID'
SET @strSQL += ' ) maxnode'
SET @strSQL += ' ON tb.FormID = maxnode.FormID'
SET @strSQL += ' AND tb.WFNodeIndex = maxnode.maxnode'
SET @strSQL += ' WHERE tb.FormID= '''+@FormID+''''
DECLARE @spvalue VARCHAR(20)
EXEC sp_executesql @strSQL,N'@spvalue VARCHAR(20) out',@spvalue OUTPUT
select FormID,@spvalue FROM tFormMoney
END