在sql server中，如果我使用exec语句在过程中运行动态sql，如何将结果作为输出参数检索？

Question

我不确定我的问题是否写得很好，我肯定以前有人问过，但我没有找到.

在proc中，我使用EXEC创建和运行一些动态SQL来创建一个大型CSV字符串。问题是，如果我将结果放入到EXEC中的nvarchar(max)变量中，那么它不在PROC范围内，那么如何通过过程中的输出变量获得返回它的值？

我猜我不知怎么搞错了这件事:哦)

下面是我的实际代码(如果需要的话，我可以生成一个不那么复杂的示例，并解释为什么它是复杂的)：

CREATE PROCEDURE GETFORMDATACSV 
(
    -- Add the parameters for the function here
    @FORMID numeric(38,0), 
    @STARTDATE nvarchar(20) = '1900-01-01',
    @ENDDATE nvarchar(20)   = '2100-01-01'--,
    --@RCSV nvarchar(max)    OUTPUT
)

AS 
BEGIN 
DECLARE @CSV nvarchar(max);
DECLARE @CRLF nchar(2) = char(13) + char(10);
DECLARE @FIELDS nvarchar(4000);         -- list of fields comma delimited
DECLARE @FIELDSDL nvarchar(4000);       -- list of fields "", delimited
DECLARE @FIELDSSEL nvarchar(4000);      -- list of fields with replace " with ' if needed
DECLARE @SEP nchar(1); 
DECLARE @SELSEP nvarchar(20);
DECLARE @SQL nvarchar(2000);
DECLARE @FCSV nvarchar(max);


set @SEP = ''; 
set @SELSEP = ',''","'','; 

select  @FIELDS =  isnull(@FIELDS,'') + @SEP + isnull(FD,'''x'''), 
@FIELDSDL =   isnull(@FIELDSDL,'')  + @SEP + '"' + isnull(FD,'''x''') + '"',
@FIELDSSEL =  isnull(@FIELDSSEL,'') + @SELSEP + 'replace(' + isnull(FD,'''x''') + ',''"'','''''''')',
@SEP = ','   
from (select distinct rf.FIELD as FD from FORMRESPONSEFIELDS rf  
JOIN FORMRESPONSE r on r.ID = rf.ID  
where r.FORMID = @FORMID and r.RDATE between cast(@STARTDATE as date) and cast(@ENDDATE as date)) x; 

if len(@FIELDS) > 0  
 BEGIN 
  set @CSV = '"RESPID","RDATE",' + @FIELDSDL ;
  set @SQL = 'DECLARE @FCSV nvarchar(max); set @FCSV = ''' + @CSV + @CRLF +  '''; select @FCSV = isnull(@FCSV,'''') + concat(''"'',RESPID,''","'', RDATE' + @FIELDSSEL + ',''"'',char(13),char(10)) from (select ID as RESPID, RDATE, ' + isnull(@FIELDS,'')  
  set @SQL = @SQL + ' from (select a.ID, FIELD, VALUE, RDATE from FORMRESPONSEFIELDS a JOIN FORMRESPONSE b on a.ID = b.ID ' 
  set @SQL = @SQL + ' where b.FORMID = ' + cast(@FORMID as nvarchar(15)) + ' and b.RDATE between cast(''' + @STARTDATE  + '''as nvarchar(20)) and cast(''' + @ENDDATE + ''' as nvarchar(20))) rf '; 
  set @SQL = @SQL + ' pivot ( max(VALUE)  for FIELD in (' + isnull(@FIELDS,'') + ')) as pvt)x order by RDATE desc; select @FCSV; '; 
 END; 
ELSE 
 BEGIN 
  set @SQL = 'select ''No Data available for FORMID = ' + cast(@FORMID as nvarchar(15)) + ' between ' + @STARTDATE + ' and ' + @ENDDATE + ' '''; 
 END; 
EXEC(@SQL); 
RETURN;
END;

Answer 1

使用sp_executesql而不是EXEC()，从动态SQL中删除声明，并传递输出参数。一个简单的例子是：

DECLARE @SQL NVARCHAR(MAX) = 'SELECT @i = 1;';
DECLARE @i INT;
EXECUTE sp_executesql @SQL, N'@i INT OUTPUT', @i OUTPUT;

SELECT  @i;

在查看了动态SQL之后，您还应该使用输入参数，因此，而不是：

set @SQL = @SQL + ' where b.FORMID = ' + cast(@FORMID as nvarchar(15)) + ' ...'

简单地使用

SET @SQL = @SQL + ' WHERE b.FormID = @FormID '

然后将@FormID传递给sp_executesql：

EXECUTE sp_executesql @SQL, N'@FormID INT, @i INT OUTPUT', @FormID, @i OUTPUT;

增编

在您的日期中使用输入参数更重要，您有：

SET @SQL = @SQL + ' and b.RDATE between cast(''' + @STARTDATE  + '''as nvarchar(20)) and cast(''' + @ENDDATE + ''' as nvarchar(20))) rf ';  

这是显式地将@StartDate (由于某种原因实际上是nvarchar )转换为NVARCHAR(20)，只是隐式地将其转换为与b.RDATE进行比较的日期。这只是要求转换错误！将@StartDate和@EndDate参数设置为DATETIME类型，并再次在动态SQL中使用参数：

SET @SQL = @SQL + ' WHERE b.FormID = @FormID AND b.RDate BETWEEN @StartDate AND @EndDate ';
EXECUTE sp_executesql 
        @SQL, 
        N'@FormID NUMERIC(38,0), @StartDate DATETIME2, @EndDate DATETIME2', 
        @FormID,
        @StartDate,
        @EndDate;

现在，您有了字符串类型的参数，不会被转换错误或格式错误的sql所困扰。