A. A. CREATE PROCEDURE sp_ms_NextDeviceNumber AS

/*
RETURNS THE NEXT AVAILABLE SEQUENTIAL DEVICE NUMBER
Created by Mike Schellenberger
*/

/* Get a list of used device numbers > 0 and put in a temporary table */
SELECT
dev_num=CONVERT(tinyint, SUBSTRING(CONVERT(binary(4), d.low),v.low, 1))
INTO #TmpDevHoldTable
FROM master..sysdevices d, master.dbo.spt_values v
WHERE v.type='E'
AND v.number=3
AND convert(tinyint, substring(convert(binary(4), d.low),v.low, 1)) > 0
ORDER BY dev_num

/* Now lets find the first available device from the temporary table */

SET ROWCOUNT 1 /* This gets only the first one available */

SELECT Next_Available_Device_#=t1.dev_num + 1
FROM #TmpDevHoldTable t1
WHERE NOT EXISTS /* When current dev number + 1 does not exist */
(SELECT t2.dev_num /* we are here and have our 1 row */
FROM #TmpDevHoldTable t2
WHERE t1.dev_num + 1=t2.dev_num)

SET ROWCOUNT 0
DROP TABLE #TmpDevHoldTable
GO