SQL Server Performance

how to replace function to sp or view

Discussion in 'SQL Server 2005 General Developer Questions' started by isa, Jun 25, 2010.

  1. isa New Member

    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 :


    SELECT dbo.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

    FROM dbo.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

    WHERE dbo.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.
  2. Adriaan New Member

    The function you show seems to do a straightforward lookup, just with a number of tables involved.
    Most obvious solution is that you create a view with the same query statement. Additional benefit is that you will be able to use the same view in other procedures too.
    The view will return the columns for (1) the items that your function parameter is filtering on, and (2) the lookup results that you need.
    I would leave the special criteria (like for SystemRoleID) out of the view, unless you will will use those same criteria all the time. In that case, you may want to make this an indexed view.
    You can then use the view in your query statements, as if it was a table - using JOINs.

Share This Page