Error in code 1

[pauledward123]

Hello

Just starting with SQL and i'm experimenting with creating a temporary table and storing variables. I'm trying th efollowing code but I get an error "Invalid object name 'Temptable'."

Here's the code:

WITH Temptable AS
(
SELECT ID_prom, Name_prom 
FROM TSSITE_Prominences_prom
)

select top 1 ID_prom, Name_prom from Temptable 

declare @b int, @c varchar(20)
select 
@b = ID_prom, @c = Name_prom
from Temptable

 

[markros]

In your sample TempTable is a common table expression. As such, it must be used in the next statement that follows it and can not be used later.

You may want to read this blog post by Brad Schulz to learn more
CTE: Coolest T-SQL Enhancement

PluralSight Learning Library

 

[pauledward123]

Thanks a lot. Other thing I found is that a statement must be terminated with a ; before a common table expression

declare @b int, @c varchar(20);

WITH Temptable AS
(
SELECT ID_prom, Name_prom
FROM TSSITE_Prominences_prom
)

select
@b = ID_prom, @c = Name_prom
from Temptable