How to pass an inner query with in a select query in sql 2005 Without using sacalar valued fns ?

See more:

SQL-Server-2005

VS2005

C#

, +

How to pass an inner query with in a select query in sql 2005? I am able to get the same by passing saclar valued fns of getting last od balance,od utilized amount of an employee from another table.
I am posting the two scalar valued fns and the query with passed scalar valued fns. I want to avoid scalar valued fns and get the desired result.
Scalar vALUED fN uSED FOR GETTING LAST OD BALANCE OF AN EMPLOYEE
USE [C3WPS]
GO
/****** Object: UserDefinedFunction [dbo].[fnGetODBalance] Script Date: 03/07/2014 10:53:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/************************************************************
Module : Salary Advance
Author : Alexander
Date : 06/March/2014
Name : [fnGetODBalance]
Description : For getting ODBalance for an Employee with Inactive Status(IsDeleted=1)

Parameters : @EmpId
@CorporateId
@OldCardNo
Tables :
Associated PL/SQLs :
Warnings :
See Also :
select dbo.fnGetODBalance('556570','IBM','6828190104681031')
************************************************************/
ALTER FUNCTION [dbo].[fnGetODBalance](@EmpId varchar(20),@CorporateId varchar(5),@OldCardNo varchar(16))
RETURNS VARCHAR(1000)

AS
BEGIN

DECLARE @ODBalance VARCHAR(40)
set @ODBalance=''

SET @ODBalance= (select top 1 SASR.AvailableBalance from SalaryAdvanceSnapShotSpecificationReport
SASR inner join Employee E on SASR.CardNo=E.CardNo
where CorporateId=@CorporateId and EmpId=@EmpId AND IsDeleted=1
order by SASR.CreatedDate desc)
RETURN @ODBalance


END

Scalar vALUED fN uSED FOR GETTING LAST OD uTILIZED aMOUNT OF AN EMPLOYEE
USE [C3WPS]
GO
/****** Object: UserDefinedFunction [dbo].[fnGetODUtilizedAmount] Script Date: 03/07/2014 10:53:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/************************************************************
Module : Salary Advance
Author : Alexander
Date : 06/March/2014
Name : [fnGetODUtilizedAmount]
Description : For getting ODUtilizedAmount for an Employee with Inactive Status(IsDeleted=1)

Parameters : @EmpId
@CorporateId
@OldCardNo
Tables :
Associated PL/SQLs :
Warnings :
See Also :
select dbo.fnGetODUtilizedAmount('556570','IBM','6828190104681031')
************************************************************/
ALTER FUNCTION [dbo].[fnGetODUtilizedAmount](@EmpId varchar(20),@CorporateId varchar(5),@OldCardNo varchar(16))
RETURNS varchar(1000)

AS
BEGIN

DECLARE @ODUtilizedAmount VARCHAR(40)
set @ODUtilizedAmount=''

SET @ODUtilizedAmount= (select top 1 SASR.OdUtilizationAmount from SalaryAdvanceSnapShotSpecificationReport
SASR inner join Employee E on SASR.CardNo=E.CardNo
where CorporateId=@CorporateId and EmpId=@EmpId AND IsDeleted=1
order by SASR.CreatedDate desc)
RETURN @ODUtilizedAmount


END

SP used(ODGET_EMPLOYEE_DETAILS
USE [C3WPS]
GO
/****** Object: StoredProcedure [dbo].[ODGET_EMPLOYEE_DETAILS] Scr)ipt Date: 03/07/2014 10:54:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object: StoredProcedure [dbo].[ODGET_EMPLOYEE_DETAILS]
Script Date: 10/03/2013 11:01:10 ******/

/************************************************************
Module : C3Admin
Author :Alexander
Date : Feb 24,2014
Name : ODGET_EMPLOYEE_DETAILS
Description : To get employee,Salary Advance Master,SalaryAdvanceSnapShotSpecificationReport details
Parameters : @p_error_code OUTPUT Parameter
@p_error_msg OUTPUT Parameter
Tables : Employee,SalaryAdvanceMaster
Associated PL/SQLs :
Warnings :
See Also :
exec [ODGET_EMPLOYEE_DETAILS] 'IBM','556570','',''
************************************************************/
ALTER PROCEDURE [dbo].[ODGET_EMPLOYEE_DETAILS]
(
--@C3EmpRegId NUMERIC(18,0),
@CorporateId VARCHAR(10),
@EmpId VARCHAR(20),
@p_error_code VARCHAR(40) OUTPUT,
@p_error_msg VARCHAR(1000) OUTPUT
)
AS
SET NOCOUNT ON

DECLARE @error VARCHAR(40)


BEGIN

--To check whether Employee Exists under selected Corporate
IF NOT EXISTS(SELECT * FROM EMPLOYEE WHERE EmpId=@EmpId and CorporateId=@CorporateId)
BEGIN
PRINT 4
SET @p_error_code='3';
SET @p_error_msg='Invalid Employee Id:'+ @EmpId
return(0)
END
--To check whether Employee has OD Facility under selected Corporate
ELSE IF EXISTS(SELECT * FROM EMPLOYEE WHERE EmpId=@EmpId and CorporateId=@CorporateId and SalaryAdvance=0)

BEGIN
print 3
SET @p_error_code='2';
SET @p_error_msg='Salary Advance not Enabled for EmployeeId:'+ @EmpId
return(0)
END
--To check whether Employee has OD Transfer already done with same card no
else IF EXISTS(SELECT EmpId FROM ODFUNDTRANSFER WHERE EmpId=@EmpId and CorporateId=@CorporateId and CurrentRecordStatus in('K','V'))

BEGIN
print 5
SET @p_error_code='12';
SET @p_error_msg='OD Fund Transfer Already Done for EmployeeId:'+ @EmpId
return(0)
END
--To check OD Transfer suited for Employee under a Corporate
ELSE IF NOT EXISTS(SELECT E.EmpId FROM EMPLOYEE E inner join SalaryAdvanceMaster SA on
SA.C3EmpRegId=E.C3EmpRegId WHERE E.EmpId=@EmpId and E.CorporateId=@CorporateId and SalaryAdvance=1 and IsDeleted in(1,0))
BEGIN
print 2
SET @p_error_code='4';
SET @p_error_msg='OD Fund Transfer Not Possible for EmployeeId:'+ @EmpId
return(0)
END
--To get Employee OD Details if od Facility Enabled under a Corporate with both Active(IsDeleted=0) and Inactive(IsDeleted=1))
--Fetch Employee OD Details if od Facility Enabled under a Corporate with InActive(IsDeleted=1)
else IF EXISTS(SELECT E.EmpId FROM EMPLOYEE E inner join SalaryAdvanceMaster SA on
SA.C3EmpRegId=E.C3EmpRegId

WHERE E.EmpId=@EmpId and E.CorporateId=@CorporateId and SalaryAdvance=1 and IsDeleted in(1,0))
BEGIN
PRINT 1
select first.MaskedOldCardNo,first.OldCardNo,first.OldAccountNo,first.ODUtilizedAmount,first.ODLimit,first.ODBalance

from
(SELECT top 1 [dbo].[fnMaskCardNo](E.CardNo)as MaskedOldCardNo,E.CardNo as OldCardNo,
E.AccountNo as OldAccountNo,dbo.fnGetODUtilizedAmount(E.EmpId,E.CorporateId,E.CardNo) as ODUtilizedAmount,SA.SA_Limit as ODLimit,dbo.fnGetODBalance(E.EmpId,E.CorporateId,E.CardNo) as ODBalance FROM EMPLOYEE E inner join
SalaryAdvanceMaster SA on
SA.C3EmpRegId=E.C3EmpRegId
inner join dbo.SalaryAdvanceSnapShotSpecificationReport SASR
ON SASR.CardNo=E.CardNo
WHERE E.EmpId=@EmpId and E.CorporateId=@CorporateId and E.SalaryAdvance=1 and E.IsDeleted=1 ORDER by E.DeletedDate desc)first

--Fetch Employee OD Details if od Facility Enabled under a Corporate with Active Status(IsDeleted=0)
select last.C3EmpRegId,last.CitizenId,last.EmpId,last.EmployeeName,last.MaskedNewCardNo,last.NewCardNo,last.NewAccountNo from
(SELECT top 1 E.C3EmpRegId,E.CitizenId,E.EmpId,isnull(E.FirstName,'')+' '+isnull(E.MiddleName,'')+' '+isnull(E.LastName,'') as EmployeeName,[dbo].[fnMaskCardNo](E.CardNo)as MaskedNewCardNo,E.CardNo as NewCardNo,E.AccountNo as

NewAccountNo FROM EMPLOYEE E
WHERE E.EmpId=@EmpId and E.CorporateId=@CorporateId and E.SalaryAdvance=1 and E.IsDeleted=0 ORDER by E.C3EmpRegId desc)
last
END

END



SET @error= @@error
IF @error <> 0
GOTO ERROR_HANDLER
SET @p_error_msg = ''
SET @p_error_code='0'
return(0)

ERROR_HANDLER:
SET @p_error_msg = 'Procedure Name : ODGET_EMPLOYEE_DETAILS. System Error.' + RTRIM(convert(char(6),@error))
SET @p_error_code = '-1'
Return(1)