Using Dynamic SQL

From the question definition, it looks like you get a stop sign before the query is executed. This is because there is a flaw in the logic, but you don't get to see it yet due to the parsing error. For now, you have a syntax error somewhere in the line reading select * from sometable where.........

Since I cannot see the whole statement, I cannot tell what the error is, but it is there, please check your syntax (highlight the whole statement except the line reading execute sp_executesql @SQL and click Parse to reveal it).

Once you go past this point, you will then get to see the actual problem: if you declare your local table variable in your original script, it is not visible to sp_executesql. Here is the sample script revealing this behaviour (I use AdventureWorks SQl Server 2005 database):

 declare @SQL nvarchar(4000);
 
 declare @temptable table 
 (
     ContactID int, 
     FirstName nvarchar(50),
     LastName nvarchar(50) 
 );
 
 set @SQL = 'select * from @temptable;';
 
 insert into @temptable
 select ContactID, FirstName, LastName
     from Person.Contact
     where LastName like 'A%';
 
 execute sp_executesql @SQL;

The above fails with expected Msg 1087, Level 15, State 2, Line 1. Must declare the table variable "@temptable" error.

Here is the script which works:

 declare @SQL nvarchar(4000);
 
 set @SQL = '
 declare @temptable table 
 (
     ContactID int, 
     FirstName nvarchar(50),
     LastName nvarchar(50) 
 );
 
 insert into @temptable
 select ContactID, FirstName, LastName
     from Person.Contact
     where LastName like ''A%'';
 
 select * from @temptable;';
 
 execute sp_executesql @SQL;

Oleg

Just to add to previous answers. You can pass a TABLE variable as a parameter into sp_executesql in SQL Server 2008 and later, thanks to the support for Table Valued Parameters.

Quick example:

 -- First define a custom TABLE type
 CREATE TYPE IntegerTableType AS TABLE (ID INTEGER)
 GO
 
 -- Fill a var of that type with some test data
 DECLARE @MyTable IntegerTableType
 INSERT @MyTable VALUES (1),(2),(3)
 
 -- Now this is how you pass that var into dynamic statement
 EXECUTE sp_executesql N'SELECT * FROM @MyTable', 
     N'@MyTable IntegerTableType READONLY', 
     @MyTable

You can't build scripts like this as the source for SSRS. You should take a look at the Query Designer window and some of the methods exposed there. If you put the whole thing within double quotes you may be able to do it as you've defined it.