inconsistent results from stored procudure

[Judith Farber Abraham]

I have a stored procedure (see below), in which I would like to check if the create an identity column and make it a primary key succeeded. I check @@error after the exec statement. This used to pick up an error if the table already had an identity column. It has stopped doing that. Why? And, if this is not the way to capture the error after the exec statement, how do I do it?


CREATE PROCEDURE rasp_test3
/*
Written by Judith Farber Abraham
this procedure loops thru sysobjects looking for user tables.
If a user table, does it have a primary key?
If not, add an identity column to table and make it a primary key
*/
--would like to have sp in main db but use from all three
@fixDB nvarchar(50) --the db to which to add PKs

AS
Declare @TableName varchar(50)
Declare @TableID int
Declare @Msg varchar (50)
Declare @ColumnName varchar(50)
Declare @IndexName varchar(50)
Declare @MyCursor nvarchar(500)
declare @MyCursorC nvarchar(500)
declare @CName sysname
--Set @Msg = "********* Finished adding Ident fields *************"
/* */
/*
do for all user tables ( xtype = u )
*/
set @Mycursor = N'Declare SysCursor cursor for select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"'
execute sp_executesql @mycursor
open syscursor
Fetch next from SysCursor into @TableName, @TableID
/* -1 = no record; -2 = row deleted; 0 = got a row */
While (@@Fetch_status <> -1)
Begin
If (@@Fetch_status <> -2)
Begin /* have a user row (table) */
/* */
set @ColumnName = @TableName + &#39;ID&#39;
set @IndexName = &#39;PK_&#39; + @columnName

--only add ident and PK if no primary key in table
If not exists (Select * from Sysobjects where Parent_obj = @TableID and xtype = &#39;PK&#39

--add an identity column to user table and make it a Primary key

EXEC (&#39;ALTER TABLE &#39; + @tablename + &#39; ADD &#39; + @columnName + &#39; INT IDENTITY CONSTRAINT &#39; + @IndexName + &#39; PRIMARY KEY &#39; )
--
Begin
--if error, assume already ident column, so find column name & make PK
print @@error
if @@error <> 0 print &#34;jerror occured&#34;
--set @MycursorC = N&#39;Declare SysCursorC cursor for SELECT c.name
--FROM syscolumns c, sysobjects o
--WHERE ((c.id = o.id) AND (c.status = 128)) AND (o.name = &#39; + @tablename + &#39&#39;
--execute sp_executesql @mycursorC
--Open SyscursorC
--Fetch next from SysCursorC into @CName
--print @cname
--close syscursorc
--deallocate syscursorc
--Exec (&#39;ALTER TABLE &#39; + @tablename + &#39; ADD &#39; + @columnName + &#39; INT IDENTITY CONSTRAINT &#39; + @IndexName + &#39; PRIMARY KEY &#39; )
--select @cname=c.name
--print c.name
End

End
Fetch next from SysCursor into @TableName, @TableID
End
--Print @Msg
Close SysCursor
Deallocate SysCursor
Return

Thanks for any help,
Judith

I only glanced through this, but check your error condition.

It only prints a message if there was an error and the logic beneath it will execute everytime regardless.

Is that what you intended to do?

------------
Judith Farber Abraham at 1/11/00 9:08:25 AM

I have a stored procedure (see below), in which I would like to check if the create an identity column and make it a primary key succeeded. I check @@error after the exec statement. This used to pick up an error if the table already had an identity column. It has stopped doing that. Why? And, if this is not the way to capture the error after the exec statement, how do I do it?


CREATE PROCEDURE rasp_test3
/*
Written by Judith Farber Abraham
this procedure loops thru sysobjects looking for user tables.
If a user table, does it have a primary key?
If not, add an identity column to table and make it a primary key
*/
--would like to have sp in main db but use from all three
@fixDB nvarchar(50) --the db to which to add PKs

AS
Declare @TableName varchar(50)
Declare @TableID int
Declare @Msg varchar (50)
Declare @ColumnName varchar(50)
Declare @IndexName varchar(50)
Declare @MyCursor nvarchar(500)
declare @MyCursorC nvarchar(500)
declare @CName sysname
--Set @Msg = &#34;********* Finished adding Ident fields *************&#34;
/* */
/*
do for all user tables ( xtype = u )
*/
set @Mycursor = N&#39;Declare SysCursor cursor for select Name, ID from &#39; + @fixdb +&#39;.dbo.sysobjects where xtype = &#34;u&#34;&#39;
execute sp_executesql @mycursor
open syscursor
Fetch next from SysCursor into @TableName, @TableID
/* -1 = no record; -2 = row deleted; 0 = got a row */
While (@@Fetch_status <> -1)
Begin
If (@@Fetch_status <> -2)
Begin /* have a user row (table) */
/* */
set @ColumnName = @TableName + &#39;ID&#39;
set @IndexName = &#39;PK_&#39; + @columnName

--only add ident and PK if no primary key in table
If not exists (Select * from Sysobjects where Parent_obj = @TableID and xtype = &#39;PK&#39

--add an identity column to user table and make it a Primary key

EXEC (&#39;ALTER TABLE &#39; + @tablename + &#39; ADD &#39; + @columnName + &#39; INT IDENTITY CONSTRAINT &#39; + @IndexName + &#39; PRIMARY KEY &#39; )
--
Begin
--if error, assume already ident column, so find column name & make PK
print @@error
if @@error <> 0 print &#34;jerror occured&#34;
--set @MycursorC = N&#39;Declare SysCursorC cursor for SELECT c.name
--FROM syscolumns c, sysobjects o
--WHERE ((c.id = o.id) AND (c.status = 128)) AND (o.name = &#39; + @tablename + &#39&#39;
--execute sp_executesql @mycursorC
--Open SyscursorC
--Fetch next from SysCursorC into @CName
--print @cname
--close syscursorc
--deallocate syscursorc
--Exec (&#39;ALTER TABLE &#39; + @tablename + &#39; ADD &#39; + @columnName + &#39; INT IDENTITY CONSTRAINT &#39; + @IndexName + &#39; PRIMARY KEY &#39; )
--select @cname=c.name
--print c.name
End

End
Fetch next from SysCursor into @TableName, @TableID
End
--Print @Msg
Close SysCursor
Deallocate SysCursor
Return

Thanks for any help,
Judith

[Judith Farber Abraham]

yes, this is just a test program. My problem is that even when there is an error (the table already has an ident column), the &#39;error&#39; message does not print out. That is, @@error = 0. How do I find out if the &#39;add an identity column and make it a primary key&#39;, failed?

Thanks,
Judith


------------
at 1/11/00 12:58:43 PM


I only glanced through this, but check your error condition.

It only prints a message if there was an error and the logic beneath it will execute everytime regardless.

Is that what you intended to do?

------------
Judith Farber Abraham at 1/11/00 9:08:25 AM

I have a stored procedure (see below), in which I would like to check if the create an identity column and make it a primary key succeeded. I check @@error after the exec statement. This used to pick up an error if the table already had an identity column. It has stopped doing that. Why? And, if this is not the way to capture the error after the exec statement, how do I do it?


CREATE PROCEDURE rasp_test3
/*
Written by Judith Farber Abraham
this procedure loops thru sysobjects looking for user tables.
If a user table, does it have a primary key?
If not, add an identity column to table and make it a primary key
*/
--would like to have sp in main db but use from all three
@fixDB nvarchar(50) --the db to which to add PKs

AS
Declare @TableName varchar(50)
Declare @TableID int
Declare @Msg varchar (50)
Declare @ColumnName varchar(50)
Declare @IndexName varchar(50)
Declare @MyCursor nvarchar(500)
declare @MyCursorC nvarchar(500)
declare @CName sysname
--Set @Msg = &#34;********* Finished adding Ident fields *************&#34;
/* */
/*
do for all user tables ( xtype = u )
*/
set @Mycursor = N&#39;Declare SysCursor cursor for select Name, ID from &#39; + @fixdb +&#39;.dbo.sysobjects where xtype = &#34;u&#34;&#39;
execute sp_executesql @mycursor
open syscursor
Fetch next from SysCursor into @TableName, @TableID
/* -1 = no record; -2 = row deleted; 0 = got a row */
While (@@Fetch_status <> -1)
Begin
If (@@Fetch_status <> -2)
Begin /* have a user row (table) */
/* */
set @ColumnName = @TableName + &#39;ID&#39;
set @IndexName = &#39;PK_&#39; + @columnName

--only add ident and PK if no primary key in table
If not exists (Select * from Sysobjects where Parent_obj = @TableID and xtype = &#39;PK&#39

--add an identity column to user table and make it a Primary key

EXEC (&#39;ALTER TABLE &#39; + @tablename + &#39; ADD &#39; + @columnName + &#39; INT IDENTITY CONSTRAINT &#39; + @IndexName + &#39; PRIMARY KEY &#39; )
--
Begin
--if error, assume already ident column, so find column name & make PK
print @@error
if @@error <> 0 print &#34;jerror occured&#34;
--set @MycursorC = N&#39;Declare SysCursorC cursor for SELECT c.name
--FROM syscolumns c, sysobjects o
--WHERE ((c.id = o.id) AND (c.status = 128)) AND (o.name = &#39; + @tablename + &#39&#39;
--execute sp_executesql @mycursorC
--Open SyscursorC
--Fetch next from SysCursorC into @CName
--print @cname
--close syscursorc
--deallocate syscursorc
--Exec (&#39;ALTER TABLE &#39; + @tablename + &#39; ADD &#39; + @columnName + &#39; INT IDENTITY CONSTRAINT &#39; + @IndexName + &#39; PRIMARY KEY &#39; )
--select @cname=c.name
--print c.name
End

End
Fetch next from SysCursor into @TableName, @TableID
End
--Print @Msg
Close SysCursor
Deallocate SysCursor
Return

Thanks for any help,
Judith

[Andre M. van der Laan]

Judith,

Try to cath the error after the execute like following example:

DECLARE @error int
......
exec(&#34;....&#34
select @error = @@error
print @error
if @error <> 0
....

What you did is printing the error so @@error is reset to 0, beause printing has succeded! You have to capture the error before something else change the variable.

Succes,

Andre

------------
Judith Farber Abraham at 1/11/00 9:08:25 AM

I have a stored procedure (see below), in which I would like to check if the create an identity column and make it a primary key succeeded. I check @@error after the exec statement. This used to pick up an error if the table already had an identity column. It has stopped doing that. Why? And, if this is not the way to capture the error after the exec statement, how do I do it?


CREATE PROCEDURE rasp_test3
/*
Written by Judith Farber Abraham
this procedure loops thru sysobjects looking for user tables.
If a user table, does it have a primary key?
If not, add an identity column to table and make it a primary key
*/
--would like to have sp in main db but use from all three
@fixDB nvarchar(50) --the db to which to add PKs

AS
Declare @TableName varchar(50)
Declare @TableID int
Declare @Msg varchar (50)
Declare @ColumnName varchar(50)
Declare @IndexName varchar(50)
Declare @MyCursor nvarchar(500)
declare @MyCursorC nvarchar(500)
declare @CName sysname
--Set @Msg = &#34;********* Finished adding Ident fields *************&#34;
/* */
/*
do for all user tables ( xtype = u )
*/
set @Mycursor = N&#39;Declare SysCursor cursor for select Name, ID from &#39; + @fixdb +&#39;.dbo.sysobjects where xtype = &#34;u&#34;&#39;
execute sp_executesql @mycursor
open syscursor
Fetch next from SysCursor into @TableName, @TableID
/* -1 = no record; -2 = row deleted; 0 = got a row */
While (@@Fetch_status <> -1)
Begin
If (@@Fetch_status <> -2)
Begin /* have a user row (table) */
/* */
set @ColumnName = @TableName + &#39;ID&#39;
set @IndexName = &#39;PK_&#39; + @columnName

--only add ident and PK if no primary key in table
If not exists (Select * from Sysobjects where Parent_obj = @TableID and xtype = &#39;PK&#39

--add an identity column to user table and make it a Primary key

EXEC (&#39;ALTER TABLE &#39; + @tablename + &#39; ADD &#39; + @columnName + &#39; INT IDENTITY CONSTRAINT &#39; + @IndexName + &#39; PRIMARY KEY &#39; )
--
Begin
--if error, assume already ident column, so find column name & make PK
print @@error
if @@error <> 0 print &#34;jerror occured&#34;
--set @MycursorC = N&#39;Declare SysCursorC cursor for SELECT c.name
--FROM syscolumns c, sysobjects o
--WHERE ((c.id = o.id) AND (c.status = 128)) AND (o.name = &#39; + @tablename + &#39&#39;
--execute sp_executesql @mycursorC
--Open SyscursorC
--Fetch next from SysCursorC into @CName
--print @cname
--close syscursorc
--deallocate syscursorc
--Exec (&#39;ALTER TABLE &#39; + @tablename + &#39; ADD &#39; + @columnName + &#39; INT IDENTITY CONSTRAINT &#39; + @IndexName + &#39; PRIMARY KEY &#39; )
--select @cname=c.name
--print c.name
End

End
Fetch next from SysCursor into @TableName, @TableID
End
--Print @Msg
Close SysCursor
Deallocate SysCursor
Return

Thanks for any help,
Judith