[karthi_syb]

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 !

[mike_bike_kite]

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:

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

[karthi_syb]

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

[Martijnvs]

You should create the temptable first, with the identitycolumn. Then,you insert the rows you select from sysobjects:

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

[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 :

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

[pdreyer]

remove the quotes around N

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

[Martijnvs]

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 :

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.

[mike_bike_kite]

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

[karthi_syb]

IDENTITY(5) mean the length of the number should be within 5. Am i correct ?

[mike_bike_kite]

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:

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:

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.

[pdreyer]

identity(5) tell ASE how to define column N

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