In sql server if I use an exec statement to run some dynamic sql in a procedure, how can I retrieve the result as an output parameter

Question

I'm not sure that my question is well written; I'm sure it's been asked before, but I have failed to find it...

In a proc, I am creating and running some dynamic SQL using EXEC to create a large CSV string. The problem is that if I put the results in to a nvarchar(max) variable in the EXEC, it's not in the PROC scope, so how do I get the value to return it via an OUTPUT variable of my procedure?

I'm guessing that I am somehow going about this whole thing in the wrong way :o)

Here is my actual code (though I could produce a less convoluted example if required and offer an explanation of why it's convoluted):

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;

you will need to use sp_executesql to be able to use OUTPUT pramas with dynamic sql. — M.Ali
– M.Ali, Commented Aug 12, 2015 at 12:28

GarethD · Accepted Answer · 2015-08-12 12:42:21Z

3

Use sp_executesql instead of EXEC(), and remove the declaration from your dynamic SQL, and pass your output parameters. A simple example would be:

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

SELECT  @i;

Having reviewed your dynamic SQL you should also use input parameters , so instead of:

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

Simply use

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

Then pass @FormID to sp_executesql:

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

ADDENDUM

Using input parameters is even more important with your dates, you have:

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

This is explicitly converting your @StartDate (which is actually nvarchar for some obscure reason), to NVARCHAR(20), only to implicitly convert it to a date to compare with b.RDATE. This is just asking for conversion errors! Make your @StartDate and@EndDate parameters a DATETIME type, and again, use parameters in your dynamic 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;

You now have stronly typed parameters not vunerable to conversion errors, or malformed sql.

edited Aug 12, 2015 at 12:42

answered Aug 12, 2015 at 12:28

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Ellis Over a year ago

Thanks GarethD. Parameterisation was my next goal after overcoming this hurdle. I simply used nvarchar for my dates to cut down on the casting required in the creation of the dynamic SQL with a view to revisiting that along with the parameterisation.

Collectives™ on Stack Overflow

In sql server if I use an exec statement to run some dynamic sql in a procedure, how can I retrieve the result as an output parameter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related