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

/* No 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