Create dynamic SQL query to select all related data in DB based on entry table and ID

1

Hope all is well. I am hoping you can help me.

Problem Statement - I'm tasked to create a dynamic SQL statement which will select all related data from a given table where the Identifier is passed. For each table where the relevant data is found i would like the data to be exported onto a separate tab of within excel

If i was doing this manually done this i would perform the following queries and export the data onto each tab;

> Select * from  Mason where id = 12345
> Select * from  MasonContacts where Companyid= 12345 
> Select * from  MasonOpportunities  where Comid = 12345

However given the sheer volume of tables this isn't viable.

Step 1 : Type in my identifier (in this case my identifier is a field called "Id" in the Table "Mason") The query will always start from this table.

Table Name : Mason Field : Id = "12345"

Step 2 : Search against table "MasonContacts", search against the field "Companyid". Return all columns & records where the field "Companyid = 12345"

Table Name : MasonContacts Field : Companyid

Step 3 : Search against table "Mason Opportunities ", search against the field "Comid". Return all columns & records where the field "Company = 12345"

Table Name : MasonOpportunities Field : Comid

Looking forward to your help

2

Are you looking to search all columns or specific data type colums such as int ir varchar?

3

hi

not able to understand what your looking for !!

doing dynamic SQL is very easy !! but why dynamic SQL ????

what are you trying to accomplish !!!

ok data is huge so you want to filter !!

but how do you want the all the results of the three tables !!
all clubbed together ??

you can create a stored procedure pass parameter !!
and return the results from the stored procedure !!

4

Hi Yosiasz, I am not sure i understand your question ? Looking at the Data Type for the fields listed above, the Data Type = Character Varying (length 18)

5

HI Harishgg1,
My vision is that i pass an ID on that primary table, then based on a mapping, it will search the all the other tables where that ID is present.
Now, i dont want the data clubbed together, every time that id is found in a relevant table, i want that data to be on a separate tab within excel,.

So if an id is found in 2 tables, i want 2 tabs ( 1 for each table)
if an id is found in 10 tables, i want 10 tabs (1 for each table)

6

seperate tab in excel ..

it can be done in different ways !! .. some easy straightforward .. some very tough
your choice !!

7

Harishgg1 - I am happy with excel part i guess i am struggling with the sql query

8

struggling with the SQL .. query

the SQL Query is very easy as far I know ..

please help me understand !!! what you are wanting !!

9

@harishgg1 -
So using these 3 select statements below, how can i make the query dynamic (and this might not be the right word) so if i put the id of "1234" in step 1 ie setting the variable, the same id gets passed into Step 2, step 3.

Step 1 Select * from Mason where id = 12345
Step 2 Select * from MasonContacts where Companyid= 12345
Step 3 Select * from MasonOpportunities where Comid = 12345

10

declare @id int
set @id = 12345

Select * from Mason where id = @id
Select * from MasonContacts where Companyid= @id
Select * from MasonOpportunities where Comid = @id

11

create procedure whatever_procedure @input_param int
begin

Select * from Mason where id = @input_param
Select * from MasonContacts where Companyid= @input_param
Select * from MasonOpportunities where Comid = @input_param

end

12

image

you can leverage sys.tables and sys.columns.

use sqlteam
go

declare @tableName varchar(50), 
        @columnName varchar(50), 
		@columnValue int = 1, 
		@query varchar(max)

select @tableName = 'Mason', @columnName = 'Id'

select 'select * from ' + 
        t.name + ' where ' + 
		c.name + ' = ' + 
		cast(@columnValue as varchar(100))
 from sys.tables t
 join sys.columns c on t.object_id  = c.object_id
 where t.name = @tablename
   and c.name = @columnName

select @query = 'select * from ' + 
       t.name + ' where ' + 
	   c.name + ' = ' + 
	   cast(@columnValue as varchar(100))
 from sys.tables t
 join sys.columns c on t.object_id  = c.object_id
 where t.name = @tablename
   and c.name = @columnName

exec(@query);