How do I pass an existing table to a procedure? How do I use this... table-valued parameters thingys? look at my code below please

Question

I have many tables that need ID scramblers, so:

CREATE PROCEDURE SP_generateUniqueID ( -- pass table here somehow -- )
AS  
BEGIN
    DECLARE @ID         varchar(100)    -- NEW ID.
    DECLARE @isIDInUse  tinyint         -- BOOLEAN YES/NO.
    SET     @isIDInUse=1
    WHILE(@isIDInUse=1)                 -- KEEP GENERATING TILL YOU FIND ONE:
        BEGIN
            SET @ID= dbo.generateID('aA1a1') -- GENERATES ID. doesn't matter how.
            IF (@ID NOT IN (@passedTable))   -- DOES @ID EXIST ALREADY?
        /*(SEARCHES THE PASSED TABLE! Which its size will be 1XN)*/
                SET @isIDInUse=0             -- NO, YOU CAN USE.
        END
    RETURN @ID
END

I can't make the passing of the existing table go smoothly... I want to be able to insert any table that uses IDs. Any suggestion?

Answer 1

I would advise you REALLY look hard into better solutions for this issue. You will be hitting your table/index with every iteration of the new ID that you generate. What is wrong with an auto-incrementing integer value:

create table IDs (ID int identity(1,1))

(also, SQL Server has bit data types for boolean values. No need for your tinyint)

---

That aside, the only way I think you can do this your way is with dynamic SQL. Using the script below you should be able to see how you can pass in your schema.table to the stored procedure and within the procedure define your ID to be inserted in to the checking loop:

create table a(ID nvarchar(100)) insert into a values('1'),('2'),('3'),('4'),('5')
create table b(ID nvarchar(100)) insert into b values('6'),('7'),('8'),('9'),('10')


declare @Table nvarchar(100) = 'dbo.a'

declare @ID nvarchar(100) = '6'
declare @IDinUse bit = 0

declare @sql nvarchar(max) = 'if exists(select ID from ' + @Table + ' where ID = @ID) select @IDinUse = 1 else select @IDinUse = 0'
exec sp_executesql @sql, N'@ID nvarchar(100), @IDinUse bit output', @ID = @ID, @IDinUse = @IDinUse output
select @IDinUse as IDinUse

go

declare @Table nvarchar(100) = 'dbo.b'

declare @ID nvarchar(100) = '6'
declare @IDinUse bit = 0

declare @sql nvarchar(max) = 'if exists(select ID from ' + @Table + ' where ID = @ID) select @IDinUse = 1 else select @IDinUse = 0'
exec sp_executesql @sql, N'@ID nvarchar(100), @IDinUse bit output', @ID = @ID, @IDinUse = @IDinUse output
select @IDinUse as IDinUse