Fixing Table alias inside a dynamic SQL query

I had this question after viewing Fixing Temp Table inside dynamic query.

How to fix the table alias here:
DECLARE @AsIsTable nvarchar(MAX);
DECLARE @ChangingAsIsColumnsToTheEquivelantValuesSQL varchar (MAX);
SET @ChangingAsIsColumnsToTheEquivelantValuesSQL = N'SELECT
,(SELECT ServiceTypeID FROM [dbo].[tb_List_ServiceType] WHERE ServiceTypeName = R.ServiceTypeName) As ServiceTypeID
FROM' +@AsIsTable + ' AS R';


Open in new window

Thanks a lot in advance.
Who is Participating?
PortletPaulConnect With a Mentor Commented:
"Fixing" implies there is a known problem and you are not referring to temp tables in your dynamic sql. So what is the exact problem?

Also I'm not sure why you are using a "correlated subquery" in your select clause, but you could add an alias to that table, e.g.
DECLARE @AsIsTable nvarchar(MAX);
DECLARE @ChangingAsIsColumnsToTheEquivelantValuesSQL varchar (MAX);
SET @ChangingAsIsColumnsToTheEquivelantValuesSQL = N' SELECT
        , (
                FROM [dbo].[tb_List_ServiceType] AS N
                WHERE N.ServiceTypeName = R.ServiceTypeName
          AS ServiceTypeID
    FROM ' + @AsIsTable +' AS R'

Open in new window

HarreniAuthor Commented:
Thanks a lot PortletPaul for your help and explanation.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.