SSRS Text Query: Variable names must be unique within a query batch or stored procedure

I am developing an SSRS 2008 report, but instead of using stored procedures, I want to use all Text queries. This report was working with stored procedures, but when I changed this report to use same logic but via text queries, I got the foll开发者_如何学Cowing error:

An error occurred during local report processing

    Query execution failed for dataset 'BRSR_Totals'

        The variable name '@END_yEAR' has already been declared. Variable names must be unique within a query batch or stored procedure.

Operation cancelled by user.

The problem is that some of my datasets (text queries) re-use the same parameters and END_YEAR is one of these parameters. How do I make this report run correctly?

One area that you might want to check is case sensitivity. SSRS is case-sensitive when considering parameter names but T-SQL does not have that case sensitivity. Take another look at your code and make sure that all parameters are using the same case.

I just resolved a similar issue using a text query to populate a dataset. It worked in SQL Server Management Studio and it worked in the Query Designer within BIDS, but failed at runtime.

The issue turned out to be BIDS helpfully adding parameters to the Dataset that this query was referencing. Switching to the Parameters tab of the Dataset Properties showed that BIDS had duplicated the parameters I had already added earlier. Deleting the duplicates resolved my problem.

_{To respond to the suggestion that the logic be off-loaded into a stored procedure: in this case, the report is a custom report for a single customer. The query will only ever be used in this report and makes a few assumptions about the customer's configuration that should not be available globally}

I also just fixed this same issue in one of my queries. I was using a text query and had datetime variables/parameters. SSRS added a second set into the parameters for the dataset properties. I deleted them and my query ran fine after that and my graph populated.

I ran into a similar issue on a report where I had declare a substantial number of parameters at the beginning that I didn't want the end user to see. The issue I had was I was using a comma at the beginning of the line, so I had:

DECLARE @Parameter VARCHAR(4) = 'text'
, @Parameter VARCHAR(4) = 'text2'

It worked just fine in SSMS, but when I ran it in Report Builder 3.0 it threw the error shown in this thread. I changed it to remove the comma and to restate DECLARE at the beginning of each line and it worked perfectly.

Check that you didn't declare it twice, once in the CREATE PROC statement you're creating and another in the actual code...I've seen this problem while testing changes to SP code.