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
