Capture DBCC CHECKIDENT and use in SP

[SQLBI]

Hi,

Is there any way of capturing the return value of DBCC CHECKIDENT and using that value as a parameter in an SP?

I've tried SET @MyVar = (DBCC CHECKIDENT) and i can't use @MyVar = (SELECT MAX(id) FROM MyTable) + 1 as the next identity value may not be sequential.

If it helps, my table has a SELF JOIN between MailingID (IDENTITY) and ParentID, but i want to set the value of ParentID at runtime to equal that of MailingID if no value is specified by the user.

I thought about using a trigger to take the value of MailingID from the Inserted table, but obviously the self join wont allow a NULL value for ParentID prior to the trigger assigning the value. However do i need the self join as the trigger should maintain referential integrity shouldn't it?

Anyone got any thoughts on how i can go about it?

Cheers,
Leigh

Sure, if it has a microchip in it, it must be IT... Now what seems to be the problem with your toaster...?

 

[NoCoolHandle]

Does this work for you?

create Table #ident (c1 varchar(300))
go
exec master.dbo.xp_cmdshell 'osql -E -S. -dnorthwind -q"dbcc checkident (orders)" -oc:\ident.txt'
insert into #ident(c1)
exec master.dbo.xp_cmdshell 'type c:\ident.txt'
declare @row varchar(299)
select @row = min(c1)  from #ident where c1 like '%''%'
select substring(@row,charindex('''',@row)+1,charindex('''',@row,charindex('''',@row)+1)-1 - charindex('''',@row))
go
Drop Table #ident

 

[NoCoolHandle]

Actually this will probably work better...
it builds a proc in master that can be executed from any db.. takes 2 parameters , tablename and database name.

e.g. sp_getident 'orders','northwind'
returns a single resultset of 1077 (approx result depending on if you have added rows..)


Kind of fun.. Might be better called how to mix SQL and DOS commands
(note xp_cmdshell requires either sysadmin role membership or you need to add permissions to guest and the cmdproxy adccount to run it)

use master
go
Create Proc sp_GetIdent @tablename varchar(300),@dbname varchar(300) =''
as
set nocount on

create Table #ident (c1 varchar(300))
declare @cmd varchar(3000) 
set @cmd =  'osql -E -S. -d' + @dbname + ' -q"dbcc checkident (' + @tablename + ')" -oc:\ident.txt'
exec master.dbo.xp_cmdshell @cmd , no_output
insert into #ident(c1)
exec master.dbo.xp_cmdshell 'type c:\ident.txt'
declare @row varchar(299)
select @row = min(c1)  from #ident where c1 like '%''%'
select substring(@row,charindex('''',@row)+1,charindex('''',@row,charindex('''',@row)+1)-1 - charindex('''',@row))
Drop Table #ident

go
exec sp_getident 'orders','northwind'

 

[gmmastros]

My apologies if this does not answer your question.

I would advise against using DBCC CHECKIDENT within a stored procedure. According to Books On Line...

DBCC CHECKIDENT permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database role, and are not transferable.

It sounds to me like you are trying to insert data in to 2 different tables. The first table has an identity column that you need to insert in to another table as a column. If this is correct, you may be able to use Scope_Identity() to accomplish this.

Ex.

Declare @IdValue Integer

Insert Into Table1(Field1, Field2, Field3)
Values (@Param1, @Param2, @Param3)

Set @IdValue = Scope_Identity()

Insert Into Table2(IdValue, FieldA, FieldB)
Values (@IdValue, @ParamA, @ParamB)

SCOPE_IDENTITY
Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom