What's wrong with this T-SQL :
DECLARE @temp TABLE(ID INT IDENTITY,[Value] VARCHAR(100))
SET @temp = dbo.[fnCSVToTable](',2,3')
views:
64answers:
2
+4
A:
I don't think you can assign to the table variable like that (unless it is a new thing in SQL 2008).
At least for SQL2005 you would need to do the following.
DECLARE @temp TABLE(ID INT IDENTITY,[Value] VARCHAR(100))
INSERT INTO @temp
SElECT [value]
FROM dbo.[fnCSVToTable](',2,3')
Martin Smith
2010-07-12 09:13:40
+3
A:
From the docs for SET (SQL 2008; SQL 2005) (my emphasis):
@ local_variableIs the name of a variable of any type except cursor, text, ntext, image, or table.
To populate a table variable, use
INSERT @table_variable
SELECT columns
FROM dbo.fnTableValuedFunction
AakashM
2010-07-12 09:18:41
Docs say the following: There is no table included in the except clause.@local_variableIs the name of a variable of any type except cursor, text, ntext, or image. Variable names must begin with one at sign (@). Variable names must conform to the rules for identifiers. For more information, see Using Identifiers.
Puneet Dudeja
2010-07-12 09:26:35
@Puneet Dudeja added links to the relevant MSDN pages *which I am directly quoting*. The only such page I can find which *doesn't* mention `table` is the SQL *2000* page ( http://msdn.microsoft.com/en-us/library/aa259193%28SQL.80%29.aspx ), but SQL 2000 didn't *have* table variables, so naturally they are not mentioned.
AakashM
2010-07-12 09:40:10
Ok, its my mistake, but Sql 2000 have table variables and I am using this t-sql on sql 2000.
Puneet Dudeja
2010-07-12 09:55:13
@Puneet Dudeja my mistake too (neglected to check which version they were introduced in); and also it would appear to be a mistake in the SQL 2000 docs (omitting to mention that `SET` doesn't work with table variables) !
AakashM
2010-07-12 10:23:57