T Sql dynamic SQL question

Question

Hi
I have a requirement that for all of my tables I need to check if
1:- Any of the primary key column is NULL
2:- If any of the primary key column has invalid data . All columns of my table have no PKs defined and all columns have varchar data types as all tables are loaded from flat files

I do maintain a separate table which has info about table metadata eg

Table Name PKcol name Datatype
Student. Student ID int
Student. Nationality varchar
Student. DOB. Date
College. Collegeid. Int

So basically want to check if pk columns have right data type eg if for student studentid is int and dob is date etc
I know with TRY_Convert I can do that

However for all tables it will be better I have a dynamic sql which goes to my metadata table , identify column names and expected data types and then check data in actual tables and identify records which have null pks or invalid data types in pk fields and update “faulty row flag “ column of that particular row and populate another column with the reason Eg invalid date or null primary key column etc

Is this doable in dynamic sql . Sorry I can’t post ddl as my pc has some issues and raising this request from cell phone

Thanks in advance

Answer 1

Hi @John K ,

I think what you want can be achieved through dynamic sql. Since you can't provide DDL and expected output, I did some tests based on my understanding, I hope it will be useful to you.In addition, the PKcol='null' field is more convenient to update with ordinary SQL：

    create table test  
    ( PKcol char(25),  
    Datatype char(25),  
    faultyrowflag char(25))  
    insert into test values('StudentID','int',''),   
    ('Nationality', 'varchar',''),  
    ('DOB','Date',''),  
    ('Collegeid','Int',''),('null','Int','')  
  
 update test  
 set faultyrowflag ='null primary key'  
 from test  
 where PKcol='null'  
  
create procedure b_stu  
 (@pkcol varchar(25),@datatype varchar(25))  
 as  
if @datatype<>(select Datatype from test where pkcol=@pkcol)  
 update test   
 set faultyrowflag ='invalid date'  
 from test  
 where pkcol=@pkcol  
   
exec b_stu @pkcol=StudentID, @datatype=date  
  
select * from test  
  
drop table test   
drop procedure b_stu  

Best Regards
Echo

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @John K ,
Is there any update？
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!

Echo

Answer 3

I vote with Dan: this is certainly doable.

Furthermore, this seems to me like a sound usage of dynamic SQL. I often see people ask for solutions that leads to dynamic SQL, but where they have gone astray and they should solve their problem in a different way. But if you have something of a generic loading engine, something like this can make sense.

But given that you need to put in the data types and that, it sounds like it will take some time go get it working.