How to create dynamic sql based on optional values from temp

ahmed elbarbary

6y717

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

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

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]
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)
insert into [MasterData]
([SourceGeneralTypeID],[StatusGeneralTypeID],[DailyLogId])
values
(2,30,20),
(2,30,30),
(2,30,10)

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ramachandran M6y ago
Hi,,
what meaning 1 Means

condition matched so i set value as 1 and finally condition matched rows will display .

does it work ?
0
ahmed elbarbary6y ago
thank you for reply
when i make like that
(CASE WHEN ((tem.DailyLogId IS NULL AND tem.StatusGeneralTypeID IS NULL ) AND (tem.SourceGeneralTypeID = M.SourceGeneralTypeID )) THEN 1
what meaning 1
are that meaning execute statment or what
what mean 1
0
Ramachandran M6y ago
Hi Ahmed,

Can you try below query

    select M.*
     from #TempMaster tem
    INNER join [MasterData] M on
    (CASE WHEN ((tem.DailyLogId IS NULL AND tem.StatusGeneralTypeID IS NULL ) AND (tem.SourceGeneralTypeID = M.SourceGeneralTypeID )) THEN 1
    WHEN ( (tem.DailyLogId IS NULL AND tem.SourceGeneralTypeID IS NULL) AND (M.StatusGeneralTypeID = tem.StatusGeneralTypeID)) THEN 1
    WHEN ((tem.SourceGeneralTypeID IS NULL AND tem.StatusGeneralTypeID IS NULL) AND ( M.DailyLogId = tem.DailyLogId)) THEN 1 END ) =1
0
ahmed elbarbary6y ago
thank you for reply
can you tell my more details about your solution
i cannot understand statment you write above
0
Gowtham Ramar6y ago
1. use EXEC command in sql
2. DECLARE @sqlCommand varchar(1000) DECLARE @condition1 varchar(75) DECLARE @condition2 varchar(75)
3. SET @condition1
4. = 'your join condition'
5. SET @condition2 = '''your where condition'''
6. SET @sqlCommand = 'select M.MasterDataID,M.TrackingNumber,M.StatusDate
7. from #TempMaster tmp INNER join [MasterData] M on'+ @condition1
8. +' where '+ @condition2
10. EXEC (@sqlCommand)
0