Solved

Fixing Table alias inside a dynamic SQL query

Posted on 2016-10-09
2
27 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ssms - object execution statistics 12 37
SQL Script to find duplicates 16 20
Stored procedure 23 25
Filtered index 5 4
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.

744 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

15 Experts available now in Live!

Get 1:1 Help Now