Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2006
    Posts
    4

    Unanswered: Can I use user defined type in temp table in stored procedure?

    Hi,

    I have a question about creating a temp table in stored procedure.

    Is it possible to use the user defined type when I create a temp table in the stored procedure? When I try the statement something like the following in the create procedure,

    CREATE TABLE #cmv (
    PortID int_t NOT NULL,
    ResultsCMV float NOT NULL
    )

    I've got an error message that says,

    Can't find type int_t

    but this int_t is defined as my user defined type as following.

    EXEC sp_addtype 'int_t','int','NOT NULL'

    Is this the spec of Sybase or am I doing something wrong?
    If anyone can advice this, that would be grateful.
    My ASE version is 12.5.0.3.

    Thanks,

  2. #2
    Join Date
    Nov 2006
    Posts
    13
    Does this procedure has in the same database as the user defined type has ?

  3. #3
    Join Date
    Nov 2006
    Posts
    4
    Yes it does. As the proof, I could do this in the same stored proc.

    declare @asofdatei int_t

    Best regards,

  4. #4
    Join Date
    Nov 2006
    Posts
    13
    No, you can't. Beacuase in compilation time it will search the user defined type which is yet not defined.So first you have to add this type and then compile this procedure.

  5. #5
    Join Date
    Nov 2006
    Posts
    4
    Asit,

    Thanks for your advice.
    Do you mean I have to define this type in my stored proc?

    The fact here is that I have already defined this user defined type separately in prior, before I run the create procedure statement. Is this what you meant? If so, even I've done it, I still couldn't use user defined type in my temp table..

    What I have done was,

    EXEC sp_addtype 'aaa','int','NOT NULL'
    go

    create procedure use_aaa as
    begin
    declare @hoge aaa
    end
    go

    This succeeded but,

    create procedure use_aaa_and_temptable as
    begin
    declare @hoge aaa
    create table #foo (
    bbb aaa NOT NULL
    )
    end
    go

    This failed and showed the error message as,
    Msg 2715, Level 16, State 1:
    Server <servername>, Procedure 'use_aaa_and_temptable', Line 4:
    Can't find type 'aaa'.

    This way might not be a correct approach?

    Thanks,

  6. #6
    Join Date
    Nov 2006
    Posts
    13
    I have run the the both of this query and it is running fine.
    First I have added a type sp_addtype 'aaa',int,'not null'
    and run it.When it is succeeded. I have created a procedure on the same database and on the same serever.
    create procedure test_type
    as
    begin
    declare @xyz aaa
    create table #t(
    x aaa)
    end
    return 0.
    And it is running fine.Please again check it with my code.

    --Asit

  7. #7
    Join Date
    Nov 2006
    Posts
    4
    nnnn...

    I have run your code as well, but it didn't succeeded in my environment. (same error)
    Guess not coming from the syntax but coming from the settings or environment or something..

    My ASE is 12.5.0.3.

    What is your ASE version ?
    Can you imagine what else is possibly missing ?

    Thanks,

  8. #8
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    1 User defined datatypes are good practice, especially private ones for every PK (Domian), so do use them.
    2 User defined datatypes are limited to the DATABASE. That means if you are in tempdb, you will not see UDDatatypes in my_db and vice versa. Thus you simply have to define the UDDatatypes in tempdb first (use tempdb/go/sp_addtype/etc). You will have to place this code in your server boot script, as tempdb gets re-created each time. The rest of your code is fine.
    3 If you create the tempdb table first and then insert (as opposed to selct into #table), you will get better performance and (b) you can use the table across more than one stored proc.
    4 For even better performance, for commonly created/dropped temp tables, create a "permanent" tempdb table and add an index. This eliminates the constant create/drop. You will have to place the create in the startup script as well.

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

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