> Hi,
> I am not aware of a way to do a nested INSERT EXEC in SQL Server and
> tSQLt does not (yet) offer a true resultset compare.
> However, in your case you could rewrite the procedure to not use the
> INSERT EXEC pattern, as the attached sql document shows.
> dbo.DoExecInsert_old uses your pattern -> the test will error
> dbo.DoExecInsert_new provides the same functionality without INSERT EXEC ->
> the test works
> The test it self is just a quick demo and it itself is breaking a lot of
> testing best practices, so do not use it as a template for other tests.
> As always, when using dynamic SQL properly filter any input values to
> prevent SQL Injection Attacks.
> Sebastian
> --------------------------------------------------------------------------- ------------------------------
> Try out SQL Test, the new SSMS Plugin for tSQLt:http://sql-test.com
> --------------------------------------------------------------------------- ------------------------------
> On Fri, Jan 27, 2012 at 11:01, TetonSig <teton...@gmail.com> wrote:
> > We're trying to implement tSQLt in our existing system. One of our
> > current road blocks is a set of procedures for reports which have
> > dynamic SQL that we execute results into a series of temp tables and
> > then join them for the final resultset.
> > Greatly simplified:
> > create proc RPT_Ads
> > as
> > set @dynSQL1='select ' + @collist + @fromClause + @whereclause
> > insert #worktable1 exec @dynSQL1
> > set @dynSQL2='select ' + @collist + @fromClause + @whereclause
> > insert #worktable2 exec @dynSQL2
> > select * from #worktable1 inner join #worktable2
> > GO
> > The problem then becomes inside the unit test trying to do
> > insert #results exec RPT_Ads
> > An INSERT EXEC statement cannot be nested.
> > I'm not opposed to trying to refactor our stored procedures, just
> > wondering if there's any other way to work around this?
> InsertExecTests.sql
> 3KViewDownload
