June 4, 2014 at 9:04 am
Hi
I'm using Sql server 2012
On my stored procedure I declare table variable but I have an error "must declare @temptable in my cte" How I can use a table variable in a select and join ?
Here a little example that I want to do.
Declare @TempTable Table
(
Field1 int
Field2 int,
field3 int
...
)
with Ct1
(
select @TempTable .field1 * 100, @TempTable.field2* 200, @TempTable.field3
from @TempTable
inner join Table1 on table1.field3= @TempTable.field3
)
insert into @TempTable
select table3.field1,table3.field2,table3.field3
from table3
insert into @TempTable
select Ct1.field1,Ct1.field2,Ct1.field3
from Ct1
select *
from @TempTable
thanks !
June 4, 2014 at 9:13 am
With an alias on table variable I'm able to use it. I just don't know why sql server need an alias to work.
June 4, 2014 at 9:48 am
dquirion78 (6/4/2014)
With an alias on table variable I'm able to use it. I just don't know why sql server need an alias to work.
You really should just use an alias in all your queries. It makes them a lot more readable. IIRC in the future you will not be allowed to reference table names in the select portion of queries and will instead be forced to use an alias.
Glad you were able to figure out a solution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2014 at 10:05 am
Sean Lange (6/4/2014)
You really should just use an alias in all your queries. It makes them a lot more readable.
Agreed.
Sean Lange (6/4/2014)
IIRC in the future you will not be allowed to reference table names in the select portion of queries and will instead be forced to use an alias.
Really? Where did you hear this? It'll make things a more readable, but what impact is this going to have on that stupid view designer? How is it going to load up the SQL to make it more than it needs to be? 😛
June 4, 2014 at 10:16 am
Ed Wagner (6/4/2014)
Really? Where did you hear this? It'll make things a more readable, but what impact is this going to have on that stupid view designer? How is it going to load up the SQL to make it more than it needs to be? 😛
I have been trying to find it but not having any luck. It may be wishful thinking but I sort of remember hearing that is on the list of possible things to deprecate at some point. Maybe it was only going to prevent 3 part naming convention without an alias...
It would be awesome if 2 part naming was required. Would save countless hours of lost analysis trying to figure out which table a certain column comes from. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2014 at 10:18 am
Ahh it is preventing more than 2 part naming in the column list.
"A query used a 3-part or 4-part name in the column list. Change the query to use the standard-compliant 2-part names."
http://msdn.microsoft.com/en-us/library/bb510662.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2014 at 10:43 am
dquirion78 (6/4/2014)
HiI'm using Sql server 2012
On my stored procedure I declare table variable but I have an error "must declare @temptable in my cte" How I can use a table variable in a select and join ?
Here a little example that I want to do.
Declare @TempTable Table
(
Field1 int
Field2 int,
field3 int
...
)
with Ct1
(
select @TempTable .field1 * 100, @TempTable.field2* 200, @TempTable.field3
from @TempTable
inner join Table1 on table1.field3= @TempTable.field3
)
insert into @TempTable
select table3.field1,table3.field2,table3.field3
from table3
insert into @TempTable
select Ct1.field1,Ct1.field2,Ct1.field3
from Ct1
select *
from @TempTable
thanks !
I had a question about the two insert statements. Since the cte is associated with the first would it still be available for the second?
June 4, 2014 at 11:01 am
djj (6/4/2014)
dquirion78 (6/4/2014)
HiI'm using Sql server 2012
On my stored procedure I declare table variable but I have an error "must declare @temptable in my cte" How I can use a table variable in a select and join ?
Here a little example that I want to do.
Declare @TempTable Table
(
Field1 int
Field2 int,
field3 int
...
)
with Ct1
(
select @TempTable .field1 * 100, @TempTable.field2* 200, @TempTable.field3
from @TempTable
inner join Table1 on table1.field3= @TempTable.field3
)
insert into @TempTable
select table3.field1,table3.field2,table3.field3
from table3
insert into @TempTable
select Ct1.field1,Ct1.field2,Ct1.field3
from Ct1
select *
from @TempTable
thanks !
I had a question about the two insert statements. Since the cte is associated with the first would it still be available for the second?
Excellent question. No a cte is only available for the statement following the definition. The code posted is obviously a snippet of a larger piece of code.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2014 at 11:06 am
Thanks Sean, I had thought I was correct, however, since I am always learning new things I thought I missed something.
June 4, 2014 at 12:21 pm
Sean Lange (6/4/2014)
Ed Wagner (6/4/2014)
Really? Where did you hear this? It'll make things a more readable, but what impact is this going to have on that stupid view designer? How is it going to load up the SQL to make it more than it needs to be? 😛I have been trying to find it but not having any luck. It may be wishful thinking but I sort of remember hearing that is on the list of possible things to deprecate at some point. Maybe it was only going to prevent 3 part naming convention without an alias...
It would be awesome if 2 part naming was required. Would save countless hours of lost analysis trying to figure out which table a certain column comes from. 😉
That would also eliminate the lookup of the user's default schema, check for a table, then the dbo schema and a check for a table. It would be a pain for some people to get used to, but I think it would be a simple transition for people who don't already do it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply