When I assign an alias name for a table in a select statement, how can I use it after the select statement is executed. I want to accomplish somewhat Like this:-
Declare @name as varchar(20)
Select TOP 1 * from Table1 t1 -- Making sure it returns only one record
Set @name = t1.[Name]
When I do this i get the following error :-
The column prefix 't1' does not match with a table name or alias name used in the query.
Here I would like to get the result set as well as access the values in the result set. By doing this I dont have to execute the Select statement two times for better performance. Is there any way to accomplish this? or in SQL Server are we restricted to such a behavior.
Thank you very much for the reply.
Actually i changed it as follows to get it working:
Set @name = t1.[Name] FROM #t1
Set @name = (select t1.[Name] FROM #t1)
But here instead of accessing the main table two times i'm accessing the main table once and temp table twice. Temp table is accessed once for assigning the value and one more time to select the record, because I need to the result set too. So my question is which one performs better? Accessing main table twice or accessing main table once and temp table twice.