Chad Crawford (2/27/2009)
Gaby Abed (2/27/2009)
How many rows would this return? 🙂 (yes...it's different)
use AdventureWorksgo
declare @tableA TABLE(col1 int, col2 int)
go
--stmt 1
select name from tempdb.sys.tables where name like '%tableA%'
--stmt 2
select name from tempdb.sys.columns where name like 'col1%'
I must admit - you TOTALLY had me... I was sitting here going "what??!?!", nothing is different! I even pasted the selects next to each other to make sure they were the same and hadn't changed. So why the different results?
I guess you can tell me "where to go". 😀
GOT ME!
Chad
Yeah, that second go screws you up with table variables. I usually use table variables for very short routines, but they are limited. First in persistence where that second go gets rid of it. The other is that a query similar to this will fail:
insert into @someTableVariable
exec someStoredProcecdure
This to me more than anything is the biggest limitation of a table variable. You can't capture values from a stored proc into it.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein