SSRS添加共享数据集报错：存储过程新增代码引发'as'关键字语法错误

Hey Sean, let's break down what's causing this syntax error and how to fix it. The issue almost certainly comes down to missing spaces in your dynamic SQL concatenation—a super common pitfall when building SQL strings on the fly.

The Root Cause #

Looking at your code, when you concatenate @TimeDefinition with N' as RESULT_NAME', if @TimeDefinition doesn't end with a trailing space, the two strings will run together. For example, if @TimeDefinition is something like N'DATEPART(yy, Created_Date)', your concatenated SQL will end up as:

SELECT DATEPART(yy, Created_Date)as RESULT_NAME

SQL Server sees DATEPART(yy, Created_Date)as as a single invalid token, hence the "Incorrect syntax near 'as'" error.

Another tiny thing to check: your INSERT INTO line is missing a space between the temp table name and the column list. While SQL Server sometimes tolerates this, it's better to add it to avoid unexpected parsing issues.

The Fix #

Update your concatenation to explicitly add spaces where needed, and make sure all string parts are properly separated. Here's the adjusted code:

SET @sqlstring = Concat(
    N'INSERT INTO #TEXT_SEARCH_RESULT ', -- Added space before column list
    N'(Result_Name,Count_of_tickets,HT,Created_Group)', 
    N' SELECT ',
    @TimeDefinition, 
    N' as RESULT_NAME', -- Ensures a space exists between @TimeDefinition and "as"
    N' ,sum(COUNT_OF_TICKETS) as COUNT_OF_TICKETS', 
    N' ,sum(HT) as HT', 
    N' ,''Group'' as CREATED_GROUP' -- Make sure this string is fully closed
);

If you want to be extra safe, you can also append a space to @TimeDefinition before concatenation (e.g., @TimeDefinition + N' '), but adding the space in the subsequent string part is cleaner.

Pro Tip for Debugging Dynamic SQL #

When you hit syntax errors with dynamic SQL, always print the final generated string before executing it. Add this line right after setting @sqlstring:

PRINT @sqlstring;

Then copy the output from your messages tab and run it directly in SSMS—this will show you exactly where the syntax breaks, making troubleshooting way faster.

One More Check #

Double-check that your temp table #TEXT_SEARCH_RESULT is created before this dynamic SQL runs, and that its column names/data types match exactly what you're inserting. Dynamic SQL runs in its own execution context, so if the temp table isn't already defined, this will throw another error.

内容的提问来源于stack exchange，提问作者SeanC