Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2008
    Location
    India
    Posts
    96

    Unanswered: IDENTITY problem

    Hi Guys,

    When i executed the below query,

    select 'N' = IDENTITY(5),name
    into #sp
    from sysobjects
    where type = 'P'

    I go the below error message.

    Server Message: Number 156, Severity 15
    Server 'DEV', Line 1:
    Incorrect syntax near the keyword 'IDENTITY'.

    what is the problem in my query ?

    Inputs are welcome !

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    What on earth are you trying to do??? What were you hoping to do with the identity(5) bit of your SQL? If you just want the names of all the stored procedures then just do the following:
    Code:
    select 'N' = name 
    into #sp
    from sysobjects
    where type = 'P'
    Most people rename fields to make them more readable but calling the procedure name N certainly goes against this trend.

    Mike

  3. #3
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    No.

    My requirement is, i want to create sequential number along with the procedure name.

    say for example,

    1 p1
    2 p2
    3 p3
    4 p4
    .
    .
    .
    520 p_close

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    You should create the temptable first, with the identitycolumn. Then,you insert the rows you select from sysobjects:
    Code:
    create procedure proc_name
    as
    begin
    create table #sp (
    sp_id numeric(5) identity,
    name varchar(20)
    )
    go
    insert into #sp (name)
    select name from sysobjects
    end
    Something like this
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I agree with Martijnv's method but I'm not sure about the stored proc - won't the temporary table disappear once the proc finishes? We also seem to have missed the restriction to just stored procs. Wouldn't it be better just to leave it at :
    Code:
    create table #sp (
       sp_id numeric(5) identity,
       name varchar(20)
    )
    go
    
    insert into #sp (name)
    select name from sysobjects
    where type='P'
    
    select * from #sp
    go

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    remove the quotes around N
    Code:
    select N = IDENTITY(5),name 
    into #sp
    from sysobjects
    where type = 'P'

  7. #7
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by mike_bike_kite
    I agree with Martijnv's method but I'm not sure about the stored proc - won't the temporary table disappear once the proc finishes? We also seem to have missed the restriction to just stored procs. Wouldn't it be better just to leave it at :
    Code:
    create table #sp (
       sp_id numeric(5) identity,
       name varchar(20)
    )
    go
    
    insert into #sp (name)
    select name from sysobjects
    where type='P'
    
    select * from #sp
    go
    I can imagine the temptable vanishing when the proc ends, but that doesn't have to be a problem, depending on what you want to do with the contents.
    I'm not crazy, I'm an aeroplane!

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    It was pdreyer's solution that really threw me - it looked like it would just raise a syntax error but worked fine. Does it mean that the identity(5) is a function rather than a declaration? I tried different variations on this to try and understand what it was doing:
    • remove the into #sp and it fails
    • remove N= it fails
    • add quotes to the N with "N"= and it fails
    • add quotes to other var ie "fred"=name and it's fine
    Can someone explain this for me?

    Mike

  9. #9
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    IDENTITY(5) mean the length of the number should be within 5. Am i correct ?

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Yes you are correct but the initial question is a little deeper than that The integer(5) would normally be a declaration of type but you couldn't change the SQL to use other types such as int:
    Code:
    select N = int,name 
    into #sp
    from sysobjects
    where type = 'P'
    As this would generate an error rather than make N to be an integer with a null value. You could alter the SQL to use a function such as rand and it would work fine:
    Code:
    select N = rand(),name 
    into #sp
    from sysobjects
    where type = 'P'
    Hence my first question - Does it mean that the identity(5) is a function rather than a declaration.

  11. #11
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    identity(5) tell ASE how to define column N
    Code:
    select N=identity(9),name into #t1 from sysobjects where type='P'
    use tempdb 
    go
    exec sp_help #t1
    drop table #t1
    
    
    Name                           Owner                          Object_type                      
    ----                           -----                          -----------                      
    #t1__________00002840012654325 dbo                            user table                       
    
    Data_located_on_segment        When_created                   
    -----------------------        ------------                   
    default                        Jun 27 2008  2:19PM            
    
    Column_name     Type            Length      Prec        Scale       Nulls       Default_name    Rule_name       Access_Rule_name               Identity    
    -----------     ----            ----------- ----------- ----------- ----------- ------------    ---------       ----------------               ----------- 
    N               numeric                   5           9           0           0 NULL            NULL            NULL                                     1 
    name            sysname                  30        NULL        NULL           0 NULL            NULL            NULL                                     0 
    
    exp_row_size reservepagegap fillfactor  max_rows_per_page identity_gap 
    ------------ -------------- ----------- ----------------- ------------ 
               0              0           0                 0            0 
    
    concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg 
    ------------------------- --------------------- ------------------- 
                           15                     0                   0

Posting Permissions

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