Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How do I use the new table variables in SQL Server 2000?
SQL 2000 Table Variables
Table variables did not work for me until I started using an alias for the table variable when I had to qualify column names of a table variable.
Look at the UPDATE @myTable statement, where the INNER JOIN refers to tblTable.Field1, and tblTable is the alias for table variable @myTable. If you try to use the table variable name directly, as in INNER JOIN @myTable.Field1 ON ---, or in SELECT @myTable.Field1, you will get an error.
ALTER FUNCTION uf_Function()
RETURNS @passedTable TABLE ( Field1 int, Field2 int, Field3 nvarchar(50) )
AS
BEGIN
DECLARE @myTable TABLE (
Field1 int, Field2 int, Field3 nvarchar(50)
)
INSERT INTO @myTable
SELECT AcctBID, 0, Name
FROM AcctB
UPDATE @myTable
SET Field2 = CustTable.AcctAID
FROM @myTable tblTable INNER JOIN
CustTable ON tblTable.Field1 = CustTable.AcctBID
-- Plug the data into the table variable that gets passed back to the calling routine.
INSERT INTO @passedTable
SELECT Field1, Field2, Field3
FROM @myTable
RETURN
END
If you follow the rule of using an alias when qualifying column names of table variables you should have no problem writing quite complex T-SQL using table variables (well, no problems with the table variables)
Why not just use temporary tables? For one thing -- table variables are created in memory, not written to the tempdb database, and are therefore MUCH faster.
Note: Table variables are a new feature of MS SQL 2000.
Look at the UPDATE @myTable statement, where the INNER JOIN refers to tblTable.Field1, and tblTable is the alias for table variable @myTable. If you try to use the table variable name directly, as in INNER JOIN @myTable.Field1 ON ---, or in SELECT @myTable.Field1, you will get an error.
ALTER FUNCTION uf_Function()
RETURNS @passedTable TABLE ( Field1 int, Field2 int, Field3 nvarchar(50) )
AS
BEGIN
DECLARE @myTable TABLE (
Field1 int, Field2 int, Field3 nvarchar(50)
)
INSERT INTO @myTable
SELECT AcctBID, 0, Name
FROM AcctB
UPDATE @myTable
SET Field2 = CustTable.AcctAID
FROM @myTable tblTable INNER JOIN
CustTable ON tblTable.Field1 = CustTable.AcctBID
-- Plug the data into the table variable that gets passed back to the calling routine.
INSERT INTO @passedTable
SELECT Field1, Field2, Field3
FROM @myTable
RETURN
END
If you follow the rule of using an alias when qualifying column names of table variables you should have no problem writing quite complex T-SQL using table variables (well, no problems with the table variables)
Why not just use temporary tables? For one thing -- table variables are created in memory, not written to the tempdb database, and are therefore MUCH faster.
Note: Table variables are a new feature of MS SQL 2000.