Ausgabe
Ich habe eine Frage unten
SELECT @RoleUser = MR.Code FROM MasterRole MR INNER JOIN MasterUsersRole MUR ON MR.Id = MUR.RoleId
INNER JOIN MasterUsers MU ON Mu.UserCode = MUR.UserCode
WHERE MU.UserCode = @UserLoginID
select 1 Num
, MyHistory.ID
, MyHistory.RequestNumber
, MyHistory.FlowID
, MyHistory.FlowProcessStatusID
from
(
select *
from Requests R
inner join
(
--DECLARE @UserLoginID nvarchar(200) = 'dum.testing.3'
select distinct
RequestID
from dbo.RequestTrackingHistory RTH
where IIF(@UserLoginID = 'admin', @UserLoginID, RTH.CreatedBy) = @UserLoginID
OR ( CreatedBy IN
SELECT Mu.UserCode from MasterUsers MU
INNER JOIN MasterUsersRole MUR ON MU.UserCode = MUR.UserCode
INNER JOIN MasterRole MR ON MUR.RoleId = MR.Id
WHERE MR.Code = @RoleUser
)
)
) RT on R.ID = RT.RequestID
) as MyHistory
inner join MasterFlow F on MyHistory.FlowID = F.ID
inner join
(
select FP.ID
, FP.Name
, FP.AssignType
, FP.AssignTo
, FP.IsStart
, case FP.AssignType
when 'GROUP' then
G.Name
end as 'AssignToName'
from MasterFlowProcess FP
left join dbo.MasterRole G on FP.AssignTo = G.ID and FP.AssignType = 'GROUP'
) FP on MyHistory.FlowProcessID = FP.ID
inner join MasterFlowProcessStatus FPS on MyHistory.FlowProcessStatusID = FPS.ID
left join MasterFlowProcessStatusNext FPSN on FPS.ID = FPSN.ProcessStatusFlowID
left join MasterFlowProcess FPN on FPSN.NextProcessFlowID = FPN.ID
left JOIN MasterRole MR ON MR.Id = FPN.AssignTo
left join MasterUsersRole MUR on MR.Id = MUR.RoleId
left join MasterUsers MURO on MUR.UserCode = MURO.UserCode
inner join MasterUsers UC on MyHistory.CreatedBy = UC.UserCode
left join MasterUsers UU on MyHistory.UpdatedBy = UU.UserCode
LEFT JOIN RequestMT RMT ON MyHistory.ID = RMT.RequestID
LEFT JOIN RequestGT RGT ON MyHistory.ID = RGT.RequestID
left join (SELECT sum(QtyCU) countQty , RequestId from dbo.RequestGTDetail where IsActive = 1 group by RequestId) RGTD on RGTD.RequestId = RGT.RequestId
left join (SELECT sum(QtyPCS) countQty , RequestId from dbo.RequestMTDetail where IsActive = 1 group by RequestId) RMTD on RMTD.RequestId = RMT.RequestId
left join (SELECT COUNT(IIF(returnable = 0, returnable, null)) countReturnable , RequestId from dbo.RequestMTDetail group by RequestId) RMTR on RMTR.RequestId = RMT.RequestId
left JOIN dbo.MasterDistributor md ON md.Code = RGT.CustId or md.Code = RMT.CustId
left JOIN dbo.MasterUsersDistributor MUD ON MUD.UserCode = MURO.UserCode AND md.Code = MUD.DistributorCode
LEFT JOIN dbo.MasterReason MRMT ON RMT.ReasonId = MRMT.Id
LEFT JOIN dbo.MasterReason MRGT ON RGT.ReasonId = MRGT.Id
LEFT JOIN dbo.MasterDistributorGroup MDG ON MDG.Id = MD.GroupId
OUTER APPLY dbo.FnGetHistoryApproveDate(MyHistory.Id) AS x
where REPLACE(FPS.Name, '@Requestor', uc.Name) <> 'DRAFT'
AND MUD.DistributorCode IN (SELECT DistributorCode FROM dbo.MasterUsersDistributor WHERE UserCode = @UserLoginID)
Ich möchte etwas Logik in die Where-Klausel einfügen
diese Linie
==> AND MUD.DistributorCode IN (SELECT DistributorCode FROM dbo.MasterUsersDistributor WHERE UserCode = @UserLoginID)
es hängt von der @RoleUser-Variablen ab, wenn @RoleUser IN (‘A’,’B’) dann die Where-Klausel oben ausgeführt wird, aber wenn @RoleUser Not IN (‘A’,’B’) die Where-Klausel nicht ausgeführt wird
Ich versuche diese Where-Klausel
AND IIF(@RoleUser IN ('A','B'), MUD.DistributorCode, @RoleUser) IN (SELECT DistributorCode FROM dbo.MasterUsersDistributor WHERE UserCode = IIF(@RoleUser IN ('A','B'), @UserLoginID, NULL))
es hat nicht funktioniert, nur ausgeführt, wenn @RoleUser IS (‘A’, ‘B’) ist, außer dass es 0 Datensätze zurückgibt
Jede Hilfe oder Beratung wird sehr geschätzt
Danke
Lösung
Die sauberste Art, wie ich diese Art von verrückten Regeln implementiert habe, ist a
holderTable
und a countVariablegegen die Holder-Tabelle.
Ich gebe ein generisches Beispiel. Dies ist ein “Ansatz” und eine “Philosophie”, keine spezifische Antwort … mit komplexen WHERE-Klauseln.
DECLARE @customerCountryCount int
DECLARE @customerCountry TABLE ( CountryName varchar(15) )
if ( "the moon is blue on tuesday" ) /* << whatever rules you have */
BEGIN
INSERT INTO @customerCountry SELECT "Honduras" UNION ALL SELECT "Malaysia"
END
if ( "favorite color = green" ) /* << whatever rules you have */
BEGIN
INSERT INTO @customerCountry SELECT "Greenland" UNION ALL SELECT "Peru"
END
SELECT @customerCountryCount = COUNT(*) FROM @customerCountry
Select * from dbo.Customers c
WHERE
(@customerCountryCount = 0)
OR
( exists (select null from @customerCountry innerVariableTable where UPPER(innerVariableTable.CountryName) = UPPER(c.Country) ))
)
This way, instead of putting all the “twisted logic” in an overly complex WHERE statement….. you have “separation of concerns”…
Your inserts into @customerCountry are separated from your use of @customerCountry.
And you have the @customerCountryCount “trick” to distinguish when nothing was used.
You can add a @customerCountryNotExists table as well, and code it to where not exists.
As a side note, you may want to try using a #temp table (instead of a @variabletable (@customerCountry above)… and performance test these 2 options.
There is no “single answer”. You have to “try it out”.
And many many variables go into #temp table performance (from a sql-server SETUP, not “how you code a stored procedure”. That is way outside the scope of this question.
Hier ist ein SOF-Link zur “sicheren” Verwendung von #temp-Tabellen.
Beantwortet von – GranadaCoder
Antwort geprüft von – Cary Denson (FixError Admin)
