June 25, 2010 at 4:28 am
Hay everyone, i need your help , i have one function and one SP both are working fine, but the problem is my host server didn't allow me to create functions so i have to convert my function to view or another SP but when i convert it , it gives me error as my main SP didn't get any parameter and function works on each row so plz help me how i do convert it or adjust my function in my main query.
Here are my function and SP.
CREATE FUNCTION [dbo].[IsMemberPaid] (@SystemUserID int)
RETURNS varchar(10)
AS
BEGIN
declare @Return varchar(10)
select @Return = dbo.MemberFees.FeesAmount
FROM dbo.SystemUsers LEFT OUTER JOIN
dbo.MemberFees ON dbo.SystemUsers.SystemUserID = dbo.MemberFees.SystemUserID LEFT OUTER JOIN
dbo.FeesCategory ON dbo.MemberFees.FeesCategoryID = dbo.FeesCategory.FeesCategoryID
WHERE dbo.SystemUsers.SystemRoleID = 2 AND dbo.SystemUsers.SystemUserID = @SystemUserID
IF(@Return IS NOT Null)
SET @Return = 'YES'
ELSE
SET @Return = 'NO'
return @Return
END
and my main SP is :
SELECTdbo.SystemUsers.SystemUserID, dbo.SystemUsers.ForeName, dbo.SystemUsers.SurName, dbo.SystemRoles.RoleName, dbo.SystemUserStatusCodes.StatusCodeName,
dbo.Country.CountryName, dbo.Parent.FatherForeName, dbo.MemberShipType.MemberShipName, dbo.SystemUsers.Gender, dbo.SystemUsers.MaritalStatus, dbo.SystemUsers.Email,
dbo.SystemUsers.Address1, dbo.SystemUsers.MobileNo, dbo.SystemUsers.HomeNo, dbo.SystemUsers.IsActive, --dbo.IsMemberPaid(dbo.SystemUsers.SystemUserID) AS FeesPaid,
[dbo].[SystemUsers].[DateOfBirth],[dbo].[SystemUsers].Address2,dbo.[SystemUsers].Address3,[dbo].[SystemUsers].County,dbo.[SystemUsers].PostCode,dbo.[SystemUsers].IsContactByEmail
FROMdbo.SystemUsers INNER JOIN
dbo.SystemRoles ON dbo.SystemUsers.SystemRoleID = dbo.SystemRoles.SystemRoleID INNER JOIN
dbo.SystemUserStatusCodes ON dbo.SystemUsers.StatusCode = dbo.SystemUserStatusCodes.StatusCode INNER JOIN
dbo.Country ON dbo.SystemUsers.CountryID = dbo.Country.CountryID INNER JOIN
dbo.MemberShipType ON dbo.SystemUsers.MemberShipTypeID = dbo.MemberShipType.MemberShipTypeID left outer join
dbo.Parent ON dbo.SystemUsers.ParentID = dbo.Parent.ParentID
WHEREdbo.SystemUsers.SystemRoleID = 2
its a very simple Sp but i didn't get without using function how i get this. As when i call another SP in this Sp as
exec secondSP SystemUsers.SystemUserID
it gives me error in the above line, same case with view as view didn't allow me to set parameter.
plz help me how i convert this function to view or sp or adjust it to my main SP as i didn't understand how i pass each row ID to this.
Plz reply me asap. Its very urgent.
Thanx in Advance.
June 25, 2010 at 5:02 am
The function you have doesn't look very good written...
Actually you would not need this function in the first place!
Anyway, here is a replacement query for your sp:
;WITH PaidFeesUsers
AS
(
SELECT DISTINCT sus.SystemUserID, 'YES' AS FeesPaid
FROM dbo.SystemUsers AS sus
JOIN dbo.MemberFees AS mf
ON sus.SystemUserID = mf.SystemUserID
JOIN dbo.FeesCategory AS fc
ON mf.FeesCategoryID = fc.FeesCategoryID
WHERE sus.SystemRoleID = 2
AND mf.FeesAmount IS NOT NULL
)
SELECT sus.SystemUserID
,sus.ForeName
,sus.SurName
,srl.RoleName
,usc.StatusCodeName
,cnt.CountryName
,pnt.FatherForeName
,mst.MemberShipName
,sus.Gender
,sus.MaritalStatus
,sus.Email
,sus.Address1
,sus.MobileNo
,sus.HomeNo
,sus.IsActive
,ISNULL(pfu.FeesPaid, 'NO') AS FeesPaid
,sus.DateOfBirth
,sus.Address2
,sus.Address3
,sus.County
,sus.PostCode
,sus.IsContactByEmail
FROM dbo.SystemUsers AS sus
INNER JOIN dbo.SystemRoles AS srl
ON sus.SystemRoleID = srl.SystemRoleID
INNER JOIN dbo.SystemUserStatusCodes AS usc
ON sus.StatusCode = usc.StatusCode
INNER JOIN dbo.Country AS cnt
ON sus.CountryID = cnt.CountryID
INNER JOIN dbo.MemberShipType AS mst
ON sus.MemberShipTypeID = mst.MemberShipTypeID
LEFT JOIN dbo.Parent AS pnt
ON sus.ParentID = pnt.ParentID
LEFT JOIN PaidFeesUsers AS pfu
ON pfu.SystemUserID = sus.SystemUserID
WHERE sus.SystemRoleID = 2
Please note: using the aliases and a bit better formatting makes query much more readable...
P.S. you don't need CTE and can have all required joins added into main query IF all tables in CTE are not 1-to-Many related to each other...
relationship
June 25, 2010 at 5:05 am
SELECT SU.SystemUserID,
SU.ForeName,
SU.SurName,
SR.RoleName,
SUSC.StatusCodeName,
C.CountryName,
P.FatherForeName,
MT.MemberShipName,
SU.Gender,
SU.MaritalStatus,
SU.Email,
SU.Address1,
SU.MobileNo,
SU.HomeNo,
SU.IsActive,
FeesPaid =
CASE
WHEN EXISTS
(
SELECT *
FROM dbo.MemberFees MF
WHERE MF.SystemUserID = SU.SystemUserID
AND SU.SystemRoleID = 2
)
THEN 'Yes'
ELSE 'No'
END,
SU.[DateOfBirth],
SU.Address2,
SU.Address3,
SU.County,
SU.PostCode,
SU.IsContactByEmail
FROM
dbo.SystemUsers SU
JOIN
dbo.SystemRoles SR
ON SU.SystemRoleID = SR.SystemRoleID
JOIN
dbo.SystemUserStatusCodes SUSC
ON SU.StatusCode = SUSC.StatusCode
JOIN
dbo.Country C
ON SU.CountryID = C.CountryID
JOIN
dbo.MemberShipType MT
ON SU.MemberShipTypeID = MT.MemberShipTypeID
LEFT
JOIN dbo.Parent P
ON SU.ParentID = P.ParentID
WHERE
SU.SystemRoleID = 2;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 25, 2010 at 5:09 am
Try to avoid writing scalar user-defined functions. Try even harder to avoid writing scalar user-defined functions that access data tables. Instead of writing a function to process one row at a time (which is almost always going to have horrible performance) try to write a set-based solution. The code I submitted shows one way to achieve that.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 25, 2010 at 5:25 am
I though that the join to FeesCategory table in a function, whould ensure something... But I have overlooked that the LEFT JOIN has been used. However, function also checks if the MemberFees.FeesAmount returned is not NULL. I think they may have the record in the table with NULL value for the amount, therefore mf.FeesAmount IS NOT NULL check should added into SELECT statement and er WHEN EXISTS.
Also, I wonder, will EXISTS look up for each returned record of main query be faster than using CTE? If so, will it be always faster?
June 25, 2010 at 6:21 am
thanx guys, its working now and thanx for your suggestion also.
June 25, 2010 at 7:03 am
Eugene Elutin (6/25/2010)
I though that the join to FeesCategory table in a function, whould ensure something... But I have overlooked that the LEFT JOIN has been used. However, function also checks if the MemberFees.FeesAmount returned is not NULL. I think they may have the record in the table with NULL value for the amount, therefore mf.FeesAmount IS NOT NULL check should added into SELECT statement and er WHEN EXISTS.
To be honest, I didn't give it too much thought. Without table definitions and sample data, we're just coding blind anyway. I tend to put more effort in when the questioner does 🙂
Also, I wonder, will EXISTS look up for each returned record of main query be faster than using CTE? If so, will it be always faster?
Probably faster, probably always, but it depends on the data and the exact plan chosen.
A semi join is typically more efficient since it can stop probing as soon as any match is found. A join must examine all records. Of course, the optimiser is free to transform EXISTS and certain JOIN constructs so both or neither might be implemented as a semi join...as I say, it does rather depend.
In this case, I would expect a (logically correct) EXISTS to out-perform the DISTINCT + JOIN combination. We were working on our solutions concurrently - so my code was not a response (or criticism) of yours - it just seemed natural to transform the join to an exists to me.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply