Stored Procedure - Table Name as variable

Question

I am tasked with writing a stored procedure that will validate the input data against a few tables before inserting into a main table named CHANGES.

Here is what I am dealing with table wise:

1. There is a lookup table. This table basically gives the user the rules of data validation before it can be inserted into the MAIN table. The lookup table looks like this:

   ID  TABLECODE   COLUMNAME   ACCEPTEDDATATYPE    
   1   luDEPT      DEPTCODE    INT     
   2   luEMP       GENDERCODE  INT             
   3   luDEPT      BLDGcode    INT             
   

   So if a user is inserting an ID of 1, we know they are trying to make a correction to the DeptCode column and they must meet the requirements that only an Integer will be accepted before inserting into the CHANGES table (this is the main table that will hold the new values).

2. CHANGES table - Data is inserted into this table with the new value per column. Data will only be inserted into this table if it passes validation against the lookup table and explained in part 3. Structure of CHANGES table

   ID  pkid    NEWVALUE    
   1   67        01
   1   84        09
   2   56        03
   

3. This is the part I would like some help/input with to even see if it's doable. The column from the LOOKUP table name TABLECODE is the name of an actual table that exists in the database with codes and description for each column. So for example, all the DEPTCODE codes will be found in a lookup table named: luDEPT

Here is how the luDEPT that looks like this:

CODE  DEPARTMENTNAME
01    BIOLOGY
02    CHEMISTRY
03    INFORMATION TECHNOLOGY

So another validation step I have to take is, make sure that the NEW VALUE being inserted into CHANGES table is a valid code found in the lookup table related to the COLUMNNAME.

This is what I have so far, which works

CREATE PROCEDURE [dbo].[NewValueData]
(

@ID int,
@pkid VARCHAR(40),
@value VARCHAR(50)


)
AS
Begin
declare @result bit = 0;
declare @result1 bit = 0;
declare @result2 bit = 0;
declare @result3 bit = 0;
declare @result4 bit = 0;
    
DECLARE  @tablename varchar(50);

DECLARE @columndatatype varchar(30);
set @columndatatype=(select accepteddatatype from lookup where ID=@ID)

**set @tablename=(select TABLE_NAME from INFORMATION_SCHEMA.TABLES A, lookup b 
                where a.TABLE_NAME= b.lutablecode
                and TABLE_SCHEMA = 'Test'  and ID=@ID)**

   --CHECK IF ID, pkid and VALUE are PROVIDED
if (@pkid IS NULL OR @pkid = '') or (@ID IS NULL OR @ID = '') or (@value IS NULL OR @value = 
'')
begin
    set @result = 1
    PRINT 'PKID,ID or Value is missing'
end 


--CHECK IF ID EXISTS IN LOOKUP TABLE
if @ID not in (select ID from lookup 
    where @ID=ID)
begin
    set @result1=1
    PRINT 'ID is not in lookup table'
end

--IF datatype is an integer, only accept a numeric value
if @columndatatype = 'INT'
begin
    set @result3 = IIF(ISNUMERIC(@value)=1,0,1) 
    PRINT 'column type is INT '
end 
  
   **--ATTEMPT of trying to use @tablename 
    --CHECK IF VALUE IS AN ACCEPTED VALUE IN THE LOOKUP TABLE FOR THAT COLUMN
if @value not in (select code from @tablename where @value=code)
begin
    set @result4=1
    PRINT 'Not a valid code')
end**



if (@result = 0 and @result1 = 0 and @result2 = 0 and @result3 = 0 and @result4 = 0)
begin
    BEGIN TRANSACTION;
        begin try
    INSERT INTO [CHANGES] (ID, pkid,newvalue) VALUES (@ID, @pkid, @value)
PRINT 'New Record Inserted'
COMMIT TRANSACTION
end TRY
    begin catch
ROLLBACK TRANSACTION 
  PRINT 'id is not acceptable'
END
end
GO

The text in bold is my attempt at trying to derive the tablename dynamically but it doesn't work. Does anyone have suggestion on how to go about this issue? Any help will be welcomed.

Answer 1

Try something like:

DECLARE @tablename sysname = 'luDEPT'
DECLARE @columnname sysname = 'DEPTCODE'
DECLARE @value INT = 123
DECLARE @valid BIT

DECLARE @sql NVARCHAR(MAX) = '
    SET @Valid = CASE
        WHEN EXISTS(SELECT * FROM ' + QUOTENAME(@tablename) + ' WHERE ' + QUOTENAME(@columnname) + ' = @Value)
        THEN 1
        ELSE 0
        END'
EXECUTE sp_executesql
    @sql,
    N'@value INT, @valid BIT OUTPUT',
    @value = @value, @valid = @valid OUTPUT

SELECT Valid = @Valid

The data type could potentially also be parameterized if types other than INT are needed.