Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Location
    Brazil - Rio de Janeiro
    Posts
    7

    How to work around in T-SQL an Oracle %Rowtype similar var?

    I'm working on a Oracle migration to SQL Server and now I need to know how to declare, a %rowtype like var type in SQL Server (T-SQL).

    I know SQL Server does not accept %rowtype declaration, but I still need to retrieve an entire table row into one variable at once. As e.g.

    ------------

    Tab1 is (
    f1 int,
    f2 datetime,
    f3 varchar(50));

    ---------------

    declare var1 Tab1%rowtype;

    select * from Tab1 into var1 where f1 = 10;

    insert into tab2 values (var1.f1, var1.f2, var1.f3);

    --------

    Any help is welcome,

    Regards,
    Bruno.

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    With SQL Server 2000 you can DECLARE a variable of type table:
    Using the sample PUBS database
    Code:
    DECLARE @xxx TABLE 
            (
    	au_id char (11) NOT NULL ,
    	au_lname varchar (40),
    	au_fname varchar (20)
    )
    
    INSERT @xxx
    SELECT
    	au_id ,
    	au_lname ,
    	au_fname 
    FROM	authors
    WHERE   au_id = '341-22-1782'
    
    SELECT au_lname FROM @xxx
    GO
    With pre-SQL Server 2000 and with SQL Server 2000 you can use temporary table:
    Code:
    CREATE TABLE #xxx
            (
    	au_id char (11) NOT NULL ,
    	au_lname varchar (40),
    	au_fname varchar (20)
    )
    
    INSERT #xxx
    SELECT
    	au_id ,
    	au_lname ,
    	au_fname 
    FROM	authors
    WHERE   au_id = '341-22-1782'
    
    SELECT au_lname FROM #xxx
    GO
    Otherwize you could could create seperate variables and select into them
    Code:
    DECLARE @au_id char (11),
    	@au_lname varchar (40),
    	@au_fname varchar (20)
    
    SELECT @au_id = au_id ,
    	@au_lname = au_lname ,
    	@au_fname = au_fname
    FROM	authors
    WHERE   au_id = '341-22-1782'
    
    SELECT @au_lname
    GO
    The third method works for SQL Server 7.0/2000 and 6.x. The two methods would allow you to initially select more than one record and then you could use a cursor on it. Or you can use the third method with a cursor. This may be a vague answer but from you example I don't see the benefit, but that's not for me to decide.
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •