INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL 2000 Table Variables

How do I use the new table variables in SQL Server 2000? by jacktek
Posted: 14 Sep 01

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.






Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close