Imagine a situation where a record is going to be inserted first time in master table and then subsequent child tables. And IDs for the master records are not known. I need to pass master record string values from textboxes (Sorry forgot to mention it before). Once these are stored then I'll store child records as per the master Ids.
In the meanwhile there are number of titles shown on front end are already po***ted in the back end master tables. Now, I am passing these titles and selected values from drop downs against these titles as character strings to stored procedure using Table Value Parameters. Once, received in stored procedure I join TableValueParameter table to master table on character values to find out their IDs and then use then use these master IDs to store record in child tables.
You can imagine the kind of situation it is now. For the 1st situation where master ids are not known I have to pass character strings to back end stored procedure to create master record. Can you please suggest is this the valid approach or is there any other option available to do it and I am not aware of it.
And for 2nd situation where master records are stored and Ids can be extracted to store in data in child tables. The approach I am using is capturing all character string values from the front end (And instead of quering database tables to find Ids of these strings I am) storing them in a sort of denormalized TableValueParameter type table and passing it to stored procedure. Where I am joining received string values to master tables to find out their IDs
From front end I am passing
insert into @TVP (ClientName, PortionName, SubPortionName,PortionVisibility,SubPortionVisibility,
Values ('ExpertExchange','Databases','SQL Server',1,1,1,0,1)
Now after receiving these values in stored procedure to find out Ids;
ClientName with ClientName in ClientTable and get its id and I do the same for to get all other master ids.
Once I have Ids for
ClientName,PortionName,SubPortionName I use them to store in GroupPermission tables.
Hope I have explained the my question in detail. Now what I want to know is my approach valid?
I really appreciate the time and effort you put in to read my question. Waiting for your valuable comments and suggestions.