How to create dynamic SQL based on optinal values from temp table ?

Question

0.00/5 (No votes)

See more:

I have #TempMaster temp table have 3 fields

with optional values on 3 fields SourceGeneralTypeID AND StatusGeneralTypeID AND DailyLogId
meaning
may be SourceGeneralTypeID have values and other 2 fields not have value
may be StatusGeneralTypeID have values and other 2 fields not have value
may be DailyLogId have values and other 2 fields not have value
so my problem
How to write on statement after join on ? = ? where ?

problem how to write On ? = ? where ???????????????
and what i write on where
based on details above

details data

create table #TempMaster(

SourceGeneralTypeID int,
StatusGeneralTypeID int,
DailyLogId  int
)
insert into #TempMaster

(SourceGeneralTypeID,StatusGeneralTypeID,DailyLogId)
values
(Null,10,20),
(2,Null,30),
(2,30,Null)


CREATE TABLE [MasterData](
	[MasterDataID] [int] IDENTITY(1,1) NOT NULL,
	[SourceGeneralTypeID] [int] NULL,
	[StatusGeneralTypeID] [int] NULL,
	[DailyLogId] [int] NULL,
 CONSTRAINT [PK_MasterData] PRIMARY KEY CLUSTERED 
(
	[MasterDataID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 

ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

insert into [MasterData] 
([SourceGeneralTypeID],[StatusGeneralTypeID],[DailyLogId])
values
(2,30,20),
(2,30,30),
(2,30,10)

What I have tried:

select M.MasterDataID,M.TrackingNumber,M.StatusDate
 from #TempMaster tmp
INNER join [MasterData] M on ???=?????
where ??????

Posted 23-Jan-20 11:25am

ahmed_sa

Updated 23-Jan-20 21:21pm

Add a Solution

Comments

MadMyche 23-Jan-20 22:29pm

You are going to need to reword this question; as it just does not make sense.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2020-01-23T17:08:00

I wouldn't try to write these in a single query. Join is not very dynamic in nature.

Instead, you can split this into three parts and use UNION (Transact-SQL) - SQL Server | Microsoft Docs[^] to combine the results. Consider the following

SQL

SELECT M.MasterDataID,M.TrackingNumber,M.StatusDate
FROM #TempMaster tmp
INNER JOIN [MasterData] M ON m.SourceGeneralTypeID = tmp.SourceGeneralTypeID
WHERE tmp.SourceGeneralTypeID IS NOT NULL
UNION ALL
SELECT M.MasterDataID,M.TrackingNumber,M.StatusDate
FROM #TempMaster tmp
INNER JOIN [MasterData] M ON m.StatusGeneralTypeID = tmp.StatusGeneralTypeID 
WHERE tmp.StatusGeneralTypeID IS NOT NULL
UNION ALL
SELECT M.MasterDataID,M.TrackingNumber,M.StatusDate
FROM #TempMaster tmp
INNER JOIN [MasterData] M ON m.DailyLogId = tmp.DailyLogId 
WHERE tmp.DailyLogId IS NOT NULL

In order for tihs to produce correct results, you need to adjust the conditions to match your requirements and the logic in the data in each three cases.

Note that to when you build this, you can run each select separately . This makes it easier to check that the intermediate results are correct for each SELECT statement. When running the whole statement only, it may be harder to see which part gives invalid results.

Jörgen Andersson · Answer 2 · 2020-01-23T21:22:00

Wendelius solution is good if you want the condition m.SourceGeneralTypeID = tmp.SourceGeneralTypeID OR m.StatusGeneralTypeID = tmp.StatusGeneralTypeID OR m.DailyLogId = tmp.DailyLogId where any of the tmp-fields can be null.

But if you want an AND-condition you have to use a variant of this query:

SQL

SELECT  M.MasterDataID
       ,M.TrackingNumber
       ,M.StatusDate
FROM    MasterData M
JOIN    #TempMaster tmp
    ON  (m.SourceGeneralTypeID = tmp.SourceGeneralTypeID OR tmp.SourceGeneralTypeID IS NULL)
    AND (m.StatusGeneralTypeID = tmp.StatusGeneralTypeID OR tmp.StatusGeneralTypeID IS NULL)
    AND (m.DailyLogId          = tmp.DailyLogId          OR tmp.DailyLogId          IS NULL)

How to create dynamic SQL based on optinal values from temp table ?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0