Hola a todos, espero que se encuentren muy bien, mi consulta es la siguiente: Tengo un Stored Procedure que me han pedido optimizar. Este es:
Código SQL:
Ver originalUSE [TeamTemplate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Entity_SelectContact_pa]
@WorkorderID VARCHAR(10)
, @WorkorderMod VARCHAR(5) = '00'
, @FilterBy VARCHAR(15) = 'Team'
, @SearchBy VARCHAR(50) = NULL
, @band VARCHAR(3) = NULL
AS
SET NOCOUNT ON
IF @FilterBy = 'Team'
SELECT @FilterBy = NULL
IF @SearchBy = '' OR @SearchBy = 'undefined'
SELECT @SearchBy = NULL
IF @band = ''
SELECT @band = NULL
IF @band IS NULL OR @band <> 'All'
BEGIN
IF ISNUMERIC(@FilterBy) = 1
BEGIN
CREATE TABLE #VendorId
(
VendorId INT
)
INSERT INTO #VendorId
SELECT q.VendorID
FROM Quotes q
INNER JOIN CostCode cc ON q.DefaultCC = cc.CostCodeID
WHERE q.WorkorderID = @WorkorderID AND q.QuoteStatus IN ('Draft','Committed','Closed') AND q.DefaultCC = @FilterBy
INSERT INTO #VendorId
SELECT b.VendorID
FROM buyout b
INNER JOIN CostCode cc ON b.OrderCostCode = cc.CostCodeID
WHERE WorkorderID = @WorkorderID AND b.STATUS IN ('Draft','Committed','Executed','Closed')AND b.OrderCostCode = @FilterBy
SELECT DISTINCT u.UserId,(e.ShortName +' - '+ u.FirstName +' '+ u.LastName) AS UserName,
'('+ isnull(u.EmailAddress,'') + ')' AS EmailAddress, u.STATUS
FROM Users u
INNER JOIN Entity e ON u.EntityID = e.EntityID
INNER JOIN Vendor v ON e.EntityID = v.EntityID
WHERE u.EntityID IN (
SELECT et.EntityID
FROM EntityType et
WHERE et.EntityID = e.EntityID AND et.EntityTypeName = 'Vendor' AND et.STATUS = 'on'
) AND
v.Vendor# IN ( SELECT DISTINCT VendorID FROM #VendorId )
ORDER BY e.ShortName +' - '+ u.FirstName +' '+ u.LastName
DROP TABLE #VendorId
END
ELSE
BEGIN
IF @FilterBy IS NULL OR @FilterBy <> 'All'
BEGIN
SELECT DISTINCT u.UserID,(e.ShortName +' - '+ u.FirstName +' '+ u.LastName) AS UserName ,
'('+ isnull(u.EmailAddress,'') + ')' AS EmailAddress, u.STATUS
FROM Users u
INNER JOIN WorkorderTeam wt ON wt.UserID = u.UserID
INNER JOIN Entity e ON u.EntityID = e.EntityID
WHERE wt.WorkorderID = @WorkorderID AND
wt.WorkorderMod = @WorkorderMod AND
wt.STATUS = 'show' AND
(@FilterBy IS NULL OR wt.TeamMemberType = @FilterBy)
ORDER BY e.ShortName +' - '+ u.FirstName +' '+ u.LastName
END
IF @FilterBy = 'All'
BEGIN
SELECT u.UserId, (e.ShortName +' - '+ u.FirstName +' '+ u.LastName) AS UserName,
'('+ isnull(u.EmailAddress,'') + ')' AS EmailAddress, u.STATUS
FROM Users u
INNER JOIN Entity e ON e.EntityID = u.EntityID
WHERE u.EntityID IN (
SELECT et.EntityID
FROM EntityType et
WHERE et.EntityID = e.EntityID AND
et.EntityTypeName IN ('Customer','Occupant','ThirdParty','Vendor','SP') AND
et.STATUS = 'on'
) AND
(@SearchBy IS NULL OR e.ShortName +' - '+ u.FirstName +' '+ u.LastName LIKE '%' + @SearchBy + '%')
ORDER BY u.FirstName, u.LastName
END
END
END
IF @band = 'All'
BEGIN
SELECT u.UserId, (e.ShortName +' - '+ u.FirstName +' '+ u.LastName) AS UserName,'('+ isnull(u.EmailAddress,'') + ')' AS EmailAddress,u.STATUS
FROM Users u
INNER JOIN Entity e ON e.EntityID = u.EntityID
WHERE u.STATUS = 'on' AND
u.EntityID IN (SELECT et.EntityID FROM EntityType et WHERE et.EntityID = e.EntityID AND et.STATUS = 'on') AND
(@SearchBy IS NULL OR e.ShortName +' - '+ u.FirstName +' '+ u.LastName LIKE '%' + @SearchBy + '%')
ORDER BY u.FirstName, u.LastName
END
[/CODE]
Podrían decirme como puedo hacerlo más óptimo. Gracias.