I'm trying to figure out how to have a locally declared parameter that stays with the Datasets query and does not try to get added to the parameters list on the dataset or the report. I have some advanced queries that store some temp data in local parameters in the query text. All the queries I'm trying to bring over have been running on SSRS and work fine.
Example:
DECLARE @dr table(acct varchar(max));
INSERT INTO @dr values('acct1'),('acct2');
SELECT * FROM @dr;
Every time I try to use one of these types of queries, the report designer tries to add the parameter for @dr to the dataset and the report and then I receive the error that @dr has already been declared. I have tried to delete the automatically added parameter to no avail. If I remove the declare statement I then receive errors regarding conversion between string and object even if I try to declare the type for the parameter inside the report designer.
Is there any way to have the report designer ignore query declared parameters? This is Report Server v4.1.0.25.
Thanks,
Brian
