自定义函数返回拼接SQL字符串可以顺利打印并执行但无法EXEC执行，调试也不行

Question

如题，整个函数命令如下，主要目的是根据用户输入的列名拼凑动态SQL语句：
--/**
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--**/
-- =============================================
-- Author: <Author:Eagle>
-- Create date: <2012-07-30>
-- Description: <for dynamic sql statement of dynamic columns select of table payresult by birt design>
-- =============================================
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = 'test_function')
DROP FUNCTION test_function
GO
CREATE FUNCTION test_function(@list varchar(max), @EEID varchar(max), @paytime varchar(max))
RETURNS varchar(max)
--RETURNS TEXT (2147483647)
AS
BEGIN
DECLARE @ResultVar varchar(v) 
--DECLARE @ResultVar TEXT (2147483647)
DECLARE @mytemp table( 
ss varchar(v) not null ,myid int not null)
DECLARE @str varchar(max) 
DECLARE @str1 varchar(max)
DECLARE @payend varchar(max)
DECLARE @curr int 
DECLARE @prev int 
--/**
SET @str=@list
SET @str1=@EEID
SET @payend=@paytime
--**/
/**调试数据
SET @str='1,2,501,123,56,888,999,666,324,100,601,2,501,123,56,888,999,666,324,100,601,2,50,308'
SET @str1='0,1001' 
SET @payend='2012-06-30 00:00:00.000'
**/
SET @curr=1 
SET @prev=1 
WHILE @prev < LEN(@str) 
BEGIN 
  SET @curr=CHARINDEX(',',@str,@prev) 
    IF @curr>@prev 
    INSERT @mytemp SELECT SUBSTRING(@str,@prev,@curr-@prev), 0
    ELSE 
    BEGIN 
    INSERT @mytemp SELECT SUBSTRING(@str,@prev,LEN(@str)-@prev+1) ,0
    BREAK 
    END
SET @prev=@curr+1 
END 
UPDATE @mytemp SET myid =(CASE WHEN SS%50 = 0 THEN CEILING(ss/50) ELSE CEILING(SS/50)+1 END)
UPDATE @mytemp SET SS=(CASE WHEN SS%50 = 0 THEN 50 ELSE SS%50 END)
--拼凑SQL语句
DECLARE @n int
DECLARE @ss varchar(max)
DECLARE @sql1 varchar(max)
DECLARE @sql2 varchar(max)
DECLARE @sql3 varchar(max)
DECLARE @x varchar(max)
DECLARE @x1 varchar(max)
DECLARE @y varchar(max)
DECLARE @y1 varchar(max)
DECLARE @y2 varchar(max)
SET @y=''
SET @y1=''
SET @n = 1
SET @sql2=''
WHILE (@n<=(SELECT MAX(myid) FROM @mytemp))
  BEGIN
      SET @y2=''
  DECLARE test CURSOR FOR
  SELECT ss FROM (SELECT DISTINCT * FROM @mytemp)Z WHERE myid = @n ORDER BY ss
  OPEN test
  FETCH NEXT FROM test INTO @ss
    WHILE @@FETCH_STATUS=0
      BEGIN
  --SET @x=@ss
  --SET @x='HRPayResult_'+CONVERT(varchar(200),@n)+'.hrFigure'+@x+','
  SET @x1=@ss
  SET @x1='hrFigure'+@x1+','
  SET @y2=@y2+@x1
  SET @y1=@y1+'ISNULL ('+'PayResult_'+CONVERT(varchar(200),@n)+'.list'+@ss+',0)'+' AS NEWCOLUMNS_'+@ss+'_'+CONVERT(varchar(200),@n)+','
  FETCH NEXT FROM test INTO @ss
  END
  SET @x=SUBSTRING(@y1,1,LEN(@y1)-1)
  CLOSE test
  DEALLOCATE test
  IF(@n in (SELECT DISTINCT(myid) FROM @mytemp))
  BEGIN
  SET @sql1='(select EEID, '+SUBSTRING(@y2,1,LEN(@y2)-1)+' from PayResult '+'where EEID IN '+'('+@str1+')'+' AND PayEnd ='+''''+@payend+''''+' AND Action <= '+'''E'''+' AND PgIndex = '+convert(varchar(200),@n)+')PayResult_'+convert(varchar(200),@n)
  SET @sql2=@sql2+'LEFT JOIN '+@sql1+' ON HRPayResult_'+convert(varchar(200),@n)+'.hrEEID '+' = HRPayResult_0'+'.hrEEID '+' '
  END
SET @n=@n+1
 END
SET @sql3='SELECT DISTINCT * FROM ('+'select PayResult_0.EEID, A.EEName, PayResult_0.PayEnd, PayResult_0.BatchID, PayResult_0.Action, PayResult_0.AuditNo, PayResult_0.FinalApp, PayResult_0.PeriodID, '+@x+' from (SELECT  * FROM PAYRESULT WHERE EEID IN '+'('+@str1+')'+' AND PayEnd ='+''''+@payend+''''+' AND ACTION <= '+'''E'''+')PayResult_0 '+@sql2+'LEFT JOIN EEInfo A ON PayResult_0.EEID = A.EEID'+')A'
SET @ResultVar = @sql3
--print @ResultVar
RETURN @ResultVar
END如上，可以顺利打印全部语句，将其考下可以正确执行，但是对生成的函数进行执行就无法得出全部语句，打印是只能出2000个字符。EXEC(),EXEC SP_EXECUTESQL都无法执行（字符串被截断）。
问题就是：原因何在？BY THE WAY,sp_executesql执行的时候我是做了调整了，Nvarchar(max)当然是改了的。为什么得不到完整的语句呢？请高手指教！谢谢！
第一次发帖，不知道如何散分，或者我就没有分，请各位高手海涵！

Answer 1

函数里面不能用exec 语句的
 改成存储吧。

Answer 2

2楼嫂子，我没有在函数中EXEC，只是调试的，我的目的是直接EXEC得到函数的输出字符串。1楼兄台，谢谢您的解答，但是我是直接在SQL中调试，也是只能取得2000个字符的，我的返回变量是VARCHAR(MAX)按理不会有问题啊！请指教啊！谢谢2位解答。

Answer 3

--print @ResultVar
RETURN @ResultVar
只是返回一个字符串？贴出打印出来的字符串是什么呢？sp_executesql(test_fun(...));不行？报什么错？

Answer 4

谢谢嫂子！我需要在程序中这样去调用：
DECLARE @Z VARCHAR(MAX)
SET @Z = dbo.test_function('1,2,501,123,56,888,999,666,324,100,601,2,501,123,56,888,999,666,324,100,601,2,50,308','0,1001','2012-06-30 00:00:00.000')
print @z--得到的字符串不全
print len(@z)--长度只有2000，在函数中调试可以知道有3000+
select @z--结果一样
exec (@z)--所以无法执行
或者嫂子有别的办法咩？请指教！谢谢！

Answer 5

谢谢兄台回答！是的，我只需要返回一个字符串，长度不一定，根据用户的输入。报错是明显的取到的字符串不全，被截断了，因为函数返回的是一个SQL，那我调试的输入参数来说，调试时得到的语句是：SELECT DISTINCT * FROM (select PayResult_0.EEID, A.EEName, PayResult_0.PayEnd, PayResult_0.BatchID, PayResult_0.Action, PayResult_0.AuditNo, PayResult_0.FinalApprv, PayResult_0.PeriodID, ISNULL (PayResult_1.list1,0) AS NEWCOLUMNS_1_1,ISNULL (PayResult_1.list2,0) AS NEWCOLUMNS_2_1,ISNULL (PayResult_1.list50,0) AS NEWCOLUMNS_50_1,ISNULL (PayResult_2.list50,0) AS NEWCOLUMNS_50_2,ISNULL (PayResult_2.list6,0) AS NEWCOLUMNS_6_2,ISNULL (PayResult_3.list23,0) AS NEWCOLUMNS_23_3,ISNULL (PayResult_7.list24,0) AS NEWCOLUMNS_24_7,ISNULL (PayResult_7.list8,0) AS NEWCOLUMNS_8_7,ISNULL (PayResult_11.list1,0) AS NEWCOLUMNS_1_11,ISNULL (PayResult_13.list1,0) AS NEWCOLUMNS_1_13,ISNULL (PayResult_14.list16,0) AS NEWCOLUMNS_16_14,ISNULL (PayResult_18.list38,0) AS NEWCOLUMNS_38_18,ISNULL (PayResult_20.list49,0) AS NEWCOLUMNS_49_20 from (SELECT  * FROM PAYRESULT WHERE EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND ACTION <= 'E')PayResult_0 LEFT JOIN (select EEID, hrFigure1,hrFigure2,hrFigure50 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 1)PayResult_1 ON PayResult_1.EEID  = PayResult_0.EEID  LEFT JOIN (select EEID, hrFigure50,hrFigure6 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 2)PayResult_2 ON PayResult_2.EEID  = PayResult_0.EEID  LEFT JOIN (select EEID, hrFigure23 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 3)PayResult_3 ON PayResult_3.EEID  = PayResult_0.EEID  LEFT JOIN (select EEID, hrFigure24,hrFigure8 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 7)PayResult_7 ON PayResult_7.EEID  = PayResult_0.EEID  LEFT JOIN (select EEID, hrFigure1 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 11)PayResult_11 ON PayResult_11.EEID  = PayResult_0.EEID  LEFT JOIN (select EEID, hrFigure1 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 13)PayResult_13 ON PayResult_13.EEID  = PayResult_0.EEID  LEFT JOIN (select EEID, hrFigure16 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 14)PayResult_14 ON PayResult_14.EEID  = PayResult_0.EEID  LEFT JOIN (select EEID, hrFigure38 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 18)PayResult_18 ON PayResult_18.EEID  = PayResult_0.EEID  LEFT JOIN (select EEID, hrFigure49 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 20)PayResult_20 ON PayResult_20.EEID  = PayResult_0.EEID  LEFT JOIN EEInfo A ON PayResult_0.EEID = A.EEID)A
是可以正确执行的，但是输出就会被截断，无法正确EXEC 或者exec sp_executesql。未知如何应对。谢谢兄台，请指教。盼望解答。

Answer 6

按照兄台指点，仍然不行，报错如：消息 102，级别 15，状态 1，第 1 行
'test_function' 附近有语法错误。谢谢解答，请多关照，盼望解答！

Answer 7

我测试了一下 varchar(max) 可以接收至少 8000 个的IF OBJECT_ID('f_test',N'FN') IS NOT NULL 
DROP FUNCTION f_test
GO 
CREATE FUNCTION f_test()
RETURNS  varchar(max) 
AS
BEGIN 
DECLARE @s varchar(max) 
SET @s=REPLICATE('a',30000)
RETURN @s
END 
DECLARE @a varchar(max) 
SET @a=dbo.f_test()
SELECT @a,LEN(@a)
上面这个测试最大值是8000，你在你sql上试试
你的sql 版本是什么改成 varchar(8000)试试

Answer 8

哦，调用函数好像要加dbo的：
sp_executesql( dbo.test_fun(...) );
呢？

Answer 9

函数里面DECLARE @ResultVar varchar(v) --把varchar(v) 改成varchar(8000)，
declare @a varchar(8000)
select @a = dbo.test_function('1,2,501,123,56,888,999,666,324,100,601,2,501,123,56,888,999,666,324,100,601,2,50,308','0,1001','2012-06-30 00:00:00.000')
print @a
print len(@a)
结果
SELECT DISTINCT * FROM (select PayResult_0.EEID, A.EEName, PayResult_0.PayEnd, PayResult_0.BatchID, PayResult_0.Action, PayResult_0.AuditNo, PayResult_0.FinalApp, PayResult_0.PeriodID, ISNULL (PayResult_1.list1,0) AS NEWCOLUMNS_1_1,ISNULL (PayResult_1.list2,0) AS NEWCOLUMNS_2_1,ISNULL (PayResult_1.list50,0) AS NEWCOLUMNS_50_1,ISNULL (PayResult_2.list50,0) AS NEWCOLUMNS_50_2,ISNULL (PayResult_2.list6,0) AS NEWCOLUMNS_6_2,ISNULL (PayResult_3.list23,0) AS NEWCOLUMNS_23_3,ISNULL (PayResult_7.list24,0) AS NEWCOLUMNS_24_7,ISNULL (PayResult_7.list8,0) AS NEWCOLUMNS_8_7,ISNULL (PayResult_11.list1,0) AS NEWCOLUMNS_1_11,ISNULL (PayResult_13.list1,0) AS NEWCOLUMNS_1_13,ISNULL (PayResult_14.list16,0) AS NEWCOLUMNS_16_14,ISNULL (PayResult_18.list38,0) AS NEWCOLUMNS_38_18,ISNULL (PayResult_20.list49,0) AS NEWCOLUMNS_49_20 from (SELECT * FROM PAYRESULT WHERE EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND ACTION <= 'E')PayResult_0 LEFT JOIN (select EEID, hrFigure1,hrFigure2,hrFigure50 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 1)PayResult_1 ON HRPayResult_1.hrEEID  = HRPayResult_0.hrEEID  LEFT JOIN (select EEID, hrFigure50,hrFigure6 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 2)PayResult_2 ON HRPayResult_2.hrEEID  = HRPayResult_0.hrEEID  LEFT JOIN (select EEID, hrFigure23 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 3)PayResult_3 ON HRPayResult_3.hrEEID  = HRPayResult_0.hrEEID  LEFT JOIN (select EEID, hrFigure24,hrFigure8 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 7)PayResult_7 ON HRPayResult_7.hrEEID  = HRPayResult_0.hrEEID  LEFT JOIN (select EEID, hrFigure1 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 11)PayResult_11 ON HRPayResult_11.hrEEID  = HRPayResult_0.hrEEID  LEFT JOIN (select EEID, hrFigure1 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 13)PayResult_13 ON HRPayResult_13.hrEEID  = HRPayResult_0.hrEEID  LEFT JOIN (select EEID, hrFigure16 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 14)PayResult_14 ON HRPayResult_14.hrEEID  = HRPayResult_0.hrEEID  LEFT JOIN (select EEID, hrFigure38 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 18)PayResult_18 ON HRPayResult_18.hrEEID  = HRPayResult_0.hrEEID  LEFT JOIN (select EEID, hrFigure49 from PayResult where EEID IN (0,1001) AND PayEnd ='2012-06-30 00:00:00.000' AND Action <= 'E' AND PgIndex = 20)PayResult_20 ON HRPayResult_20.hrEEID  = HRPayResult_0.hrEEID  LEFT JOIN EEInfo A ON PayResult_0.EEID = A.EEID)A
2897
没有问题啊

Answer 10

谢谢您的回答！多谢！我最终是将函数改为表值函数，将最后的字符串作为TEXT存储在表变量中，这样就没有问题了，估计还是字符串的问题吧。感谢了！