Error While Executing sp_executesql

Question

exec sp_executesql N'SELECT (STUFF (( SELECT '',''+ NAME from Project WHERE ProjectTypeID=1 and OutputHierarchyID IN (SELECT DISTINCT HierarchyID from HierarchyNode'+ @TextVal +''''
+ N')FOR XML PATH('''')), 1, 1, ''''))AS INDUSTRIES',  
N'@Industries NVARCHAR(100) output', @Industries output;  

I am getting error

Incorrect syntax near '+'.

Answer 1

You have extra single quotes. Try this

exec sp_executesql N'SELECT (STUFF (( SELECT '',''+ NAME from Project WHERE ProjectTypeID=1 and OutputHierarchyID IN (SELECT DISTINCT HierarchyID from HierarchyNode'+ @TextVal +''
+ N')FOR XML PATH('''')), 1, 1, ''''))AS INDUSTRIES',  
N'@Industries NVARCHAR(100) output', @Industries output;  

Also it is good pratice to assign dynamic query into a variable and use that varialbe for execution

Answer 2

You can't have an expression in the parameter to any procedure, including sp_executesql (this means you can't do things there like concatenate strings). Try the following, which also allows you to debug the statement in a more simple way (I'm not convinced your query is right, because I don't know why you want to add ' after @TextVal, which I assume is some table suffix):

DECLARE @sql NVARCHAR(MAX), @Industries NVARCHAR(100);

SET @sql = N'SELECT @Industries = STUFF((SELECT '','' + NAME 
    from Project WHERE ProjectTypeID = 1 
    and OutputHierarchyID IN 
    (
      SELECT DISTINCT HierarchyID from HierarchyNode' + @TextVal + '
    ) FOR XML PATH('''')), 1, 1, '''');';

PRINT @sql;

--EXEC sp_executesql @sql, N'@Industries NVARCHAR(100) output', @Industries output;  

Though I think this version will be slightly more efficient:

DECLARE @sql NVARCHAR(MAX), @Industries NVARCHAR(100);

SET @sql = N'SELECT @Industries = STUFF((SELECT '','' + NAME 
    from Project AS p WHERE ProjectTypeID = 1 
    AND EXISTS 
    (
      SELECT 1 from HierarchyNode' + @TextVal + ' 
      WHERE HierarchyID = p.OutputHierarchyID
    ) FOR XML PATH('''')), 1, 1, '''');';

PRINT @sql;

--EXEC sp_executesql @sql, N'@Industries NVARCHAR(100) output', @Industries output;  

You can inspect the statement and be sure it's correct instead of just blindly throwing it at SQL Server and trying to figure out what the error messages might mean. If you copy and paste the output into the top pane, the error message will point to a line number that you can actually see and will make it easier to troubleshoot your syntax. When you believe it's producing correct output, comment the PRINT and uncomment the EXEC.

If you think the +''''+ after @TextVal is necessary, please tell us the value of @TextVal and the name of the table you expect that query to run against.