Below scalar function called from stored procedure.but The stored procedure running very slow. I dont know what mistake i have done below function.Please help me to find what mistake done. Thanks in advance.
CREATE FUNCTION [dbo].[GInterval](@App_ID int, @Setting_ID int,@Entity_ID
int, @Item_ID int)
RETURNS int
AS
BEGIN
DECLARE @SIntVal int
DECLARE @Error int
/* This code snipit is taken directly from GInterval because
stored procedures can not be called from within functions*/
DECLARE @SP_Nme varchar(40)
DECLARE @H_Base int
DECLARE @S_Ent_ID int
DECLARE @S_Itm_ID int
DECLARE @AppEntSet_Act int
DECLARE @AppEntSVal_Act int
DECLARE @Appl_Item_ID int
DECLARE @Usr_Ent_ID int
DECLARE @Cl_Ent_ID int
DECLARE @Ofc_Ent_ID int
DECLARE @PrsCenter_Ent_ID int
DECLARE @Appl_Ent_ID int
DECLARE @UpstreamHit tinyint
DECLARE @SrcEnt_ID INT
DECLARE @Appl_ID_loc int
DECLARE @Set_ID_loc int
DECLARE @Ent_ID_loc int
DECLARE @Itm_ID_loc int
SELECT @Appl_ID_loc=@App_ID, @Set_ID_loc=@Setting_ID,
@Ent_ID_loc=@Entity_ID, @Itm_ID_loc=@Item_ID
SET @SP_Nme = Object_Name(@@PROCID)
SET @Usr_Ent_ID = 1
SET @Cl_Ent_ID = 2
SET @Ofc_Ent_ID = 3
SET @PrsCenter_Ent_ID = 4
SET @Appl_Ent_ID = 5
SET @AppEntSet_Act = 297
SET @AppEntSVal_Act = 299
SET @UpstreamHit = 0
SET @SIntVal=NULL
SET @SrcEnt_ID=NULL
SELECT @SIntVal = AESV.intVal,
@SrcEnt_ID = @Ent_ID_loc
FROM GEICO.dbo.ApplicationEntitySettingVal AESV
WHERE AESV.Application_ID = @Appl_ID_loc
AND AESV.Setting_ID = @Set_ID_loc
AND AESV.Entity_ID = @Ent_ID_loc
AND AESV.Status_ID = @AppEntSVal_Act
AND AESV.Item_ID = @Itm_ID_loc
IF @@error <> 0
BEGIN
RETURN -999999999
END
IF ((@SIntVal IS NULL) AND (@SrcEnt_ID IS NULL))
BEGIN
SELECT @H_Base = HierarchyRank
FROM GEICO.dbo.ApplicationEntitySetting AES
WHERE AES.Application_ID = @Appl_ID_loc
AND AES.Setting_ID = @Set_ID_loc
AND AES.Entity_ID = @Ent_ID_loc
IF @H_Base IS NULL
SET @H_Base = 0
DECLARE @upstream_temp TABLE (Entity_ID INT, Item_ID INT)
INSERT INTO @upstream_temp(Entity_ID,Item_ID)
SELECT Entity_ID,Item_ID FROM GEICO.dbo.fn_Setting_GetUpstreamIDs(@Ent_ID_loc, @Itm_ID_loc)
DECLARE SettingChk cursor LOCAL FAST_FORWARD READ_ONLY
FOR
SELECT Entity_ID
FROM GEICO.dbo.ApplicationEntitySetting AES
WHERE AES.Application_ID = @Appl_ID_loc
AND AES.Setting_ID = @Set_ID_loc
AND AES.Status_ID = @AppEntSVal_Act
AND AES.HierarchyRank > @H_Base
ORDER BY AES.HierarchyRank
OPEN SettingChk
WHILE (10=10)
BEGIN
FETCH NEXT
FROM SettingChk
INTO @Search_Entity_ID
IF (@@fetch_status <> 0)
BEGIN
CLOSE SettingChk
DEALLOCATE SettingChk
BREAK
END
SELECT @S_Itm_ID = Item_ID FROM @upstream_temp
WHERE Entity_ID = @S_Ent_ID
--Need to get application ID since it was not returned by the function of getupstreamids.
if @S_Ent_ID = @Appl_Ent_ID
SELECT @S_Itm_ID = @Appl_ID_loc
IF @S_Itm_ID IS NOT NULL
BEGIN
IF EXISTS
(
SELECT TOP 1 1
FROM GEICO.dbo.ApplicationEntitySettingVal AESV (nolock)
WHERE AESV.Application_ID = @Appl_ID_loc
AND AESV.Setting_ID = @Set_ID_loc
AND AESV.Entity_ID = @S_Ent_ID
AND AESV.Status_ID = @AppEntSVal_Act
AND AESV.Item_ID = @S_Itm_ID
)
BEGIN
SELECT @SIntVal = AESV.intVal,
@SrcEnt_ID = @S_Ent_ID
FROM GEICO.dbo.ApplicationEntitySettingVal AESV
WHERE AESV.Application_ID = @Appl_ID_loc
AND AESV.Setting_ID = @Set_ID_loc
AND AESV.Entity_ID = @S_Ent_ID
AND AESV.Status_ID = @AppEntSVal_Act
AND AESV.Item_ID = @S_Itm_ID
IF @@error <> 0
BEGIN
CLOSE SettingChk
DEALLOCATE SettingChk
RETURN -999999999
END
--Exit the loop
SET @UpstreamHit = 1
BREAK
END
END -- Make sure Search_Item_ID is not null
END -- Loop
IF @UpstreamHit = 0
BEGIN
RETURN -999999999
END
CLOSE SettingChk
DEALLOCATE SettingChk
END
SELECT
@Error = @@Error
IF @Error <> 0
BEGIN
SET @SIntVal = -999999999
END
RETURN @SIntVal
END
I used SQL server.Please Anyone to find mistake of above function and resolve my problem. Thanks in Advance.
