Solved

Fixing Table alias inside a dynamic SQL query

Posted on 2016-10-09
2
32 Views
Last Modified: 2016-10-09
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
 ID
,(SELECT ServiceTypeID FROM [dbo].[tb_List_ServiceType] WHERE ServiceTypeName = R.ServiceTypeName) As ServiceTypeID
FROM' +@AsIsTable + ' AS R';

EXECUTE(@ChangingAsIsColumnsToTheEquivelantValuesSQL)

Open in new window



Thanks a lot in advance.
Harreni
0
Comment
Question by:Harreni
2 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41835670
"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
          R.ID
        , (
                SELECT
                      MAX(N.ServiceTypeID)
                FROM [dbo].[tb_List_ServiceType] AS N
                WHERE N.ServiceTypeName = R.ServiceTypeName
          )
          AS ServiceTypeID
    FROM ' + @AsIsTable +' AS R'

Open in new window

0
 

Author Closing Comment

by:Harreni
ID: 41835737
Thanks a lot PortletPaul for your help and explanation.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now