Finding unused identities 1

[-cush-]

I'm working on a procedure that will take the min and max values from an identity column, and return a cursor with all the values that are missing between the min and the max. Here is what I have so far:

CREATE PROCEDURE dbo.nonsequential
@table1 varchar(25),
@idfield as varchar(25)
as
DECLARE @min int
DECLARE @max int
DECLARE @Records Cursor
DECLARE @s varchar(255)
set @s='create procedure #temp_a as declare @r int select @r=min('+@idfield+') from '+@table1+' return@r'
EXEC (@s)
EXEC @min=exec(#temp_a)
set @s='create procedure #temp_b as declare @r int select @r=max('+@idfield+') from '+@table1+' return@r'
EXEC (@s)
EXEC @max=exec(#temp_b)
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'mytemp' and type = 'T')
DROP TABLE mytemp
CREATE TABLE dbo.mytemp([id] INT PRIMARY KEY)
WHILE(@min <= @max)
BEGIN
INSERT INTO mytemp VALUES (@min)
@min = @min + 1
END
SET @Records = Cursor FOR
SELECT [id]
FROM mytemp
WHERE [id] not in (select @idfield from @table1)
OPEN @Records
GO

I get the following errors:
Error 156: Incorrect syntax near the keyword 'exec'
Line 13: Incorrect syntax near '#temp_a'
Incorrect syntax near the keyword 'exec'
Line 16: Incorrect syntax near '#temp_b'
Line 25: Incorrect syntax near '@min'
Must declare variable '@table1'

I've seen several examples where people execute string variables, so I don't know what the problem is. Also, @table1 is defined as a parameter, so I don't know what the problem there is either.

 

[amrita418]

I would suggest to avoid cursors whenever possible.

Here is a snippet to get you started:

create table #Test (Name varchar(10), Id int)

insert into #Test values ('a', 1)
insert into #Test values ('b', 4)
insert into #Test values ('c', 5)
insert into #Test values ('d', 8)
insert into #Test values ('e', 18)

declare 
	@min int,
	@max int

	select 	@min = min(id), 
		@max = max(id) 
        from 	#Test

while @min < @max
begin
	set @min = @min + 1
	if not exists (select 'x' from #Test where id = @min)
		print convert(varchar, @min)
end

Regards,
AA

 

[SQLDenis]

Why not creating a number table and doing a left join against the table

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[-cush-]

Thanks amrita418,

This is ok for now, but I really wanted to create a stored procedure where you could pass the table and the identity column instead of it being hard coded:

DECLARE @min int
DECLARE @max int

set @min=(select min(BoxID) from RBoxes)
set @max=(select max(BoxID) from RBoxes)

WHILE(@min <= @max)
BEGIN
    set @min = @min + 1
    if not exists (select BoxID from RBoxes where BoxID = @min)
        print convert(varchar, @min)
END

GO

 

[SQLSister]

Let's start with a few things. First, if you are going to use a temp table do not create it as a table in your database, use a real temp table they start with # for local ones and ## for global ones.

Second as noted above a cursor is to be avoided. Just becasue it is more work to write a procedure for each table doesn't mean that it won't be much more efficient when it comes to runtime. In the database world, efficiency is a higher priority than code reuseability in general.

Third, why do you want that information? It would be a really bad idea to try to reuse those identity numbers.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[-cush-]

It is more for research, as the data has been imported from another system. I have some tables where a column isn't an identity yet, but will be. In these cases, we may reuse these unused ones because they actually represent something in another system. When the column gets turned into an identity, the seed will be higher than the max value, and everything will work smoothly after that.