Need Help on Creating a Function.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need Help on Creating a Function.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-01-28 : 13:52:38

[code]Given the tables below, i need to create a function which pass in LoginId and return all the loans base upon the roletype. Please see the business rules below. SQL2008

Any suggestions or have better way to do these would greatly appreciate. I have several search SP need to incorporate this new function into

Thank you in advance.

Joe Celko - Don't need your suggestions or recommendations in case you look into this thread.

-------------------------------------------------------------------------------------------------

DROP TABLE SecurityRole;
go
CREATE TABLE dbo.SecurityRole
(
[RoleCode] [varchar](10) NOT NULL,
[RoleTitle] [varchar](30) NULL
)
GO

INSERT INTO [dbo].[SecurityRole]([RoleCode], RoleTitle)
SELECT N'CM', N'Collateral Manager' UNION ALL
SELECT N'CS', N'Collateral Specialist' UNION ALL
SELECT N'IT', N'IT Role'
go

SELECT *
FROM SecurityRole;
GO

DROP TABLE SecurityUserRole;
go
CREATE TABLE [dbo].[SecurityUserRole]
(
[UserRoleId] [int] IDENTITY(1,1) NOT NULL,
[LoginId] [varchar](20) NOT NULL,
[RoleCode] [varchar](10) NULL
)
GO

ALTER TABLE SecurityUserRole
ADD CONSTRAINT XPKSecurityUserRole PRIMARY KEY (LoginId, UserRoleId)
go

INSERT INTO [dbo].[SecurityUserRole]([LoginId], [RoleCode])
SELECT N'Jdoe', N'IT' UNION ALL
SELECT N'SLisa', N'CS' UNION ALL

SELECT N'TestUser1', N'CS' UNION ALL
SELECT N'Lnguyen', N'CM'
go

SELECT *
FROM SecurityUserRole;
GO

/*

UserRoleId LoginId RoleCode
----------- -------------------- ----------
1 Jdoe IT
4 Lnguyen CM
2 SLisa CS
3 TestUser1 CS

*/

DROP TABLE [dbo].[OL]
GO
CREATE TABLE [dbo].[OL]
(
[SpecialistId] [varchar](15) NULL,
[LoanNum] [varchar](10) NOT NULL
)
GO

INSERT INTO [dbo].[OL]([SpecialistId], [LoanNum])
SELECT N'Jdoe', N'10000' UNION ALL
SELECT N'LNguyen', N'10001' UNION ALL
SELECT N'SLisa', N'10002' UNION ALL
SELECT N'TestUser1', N'10003' UNION ALL
SELECT N'TestUser1', N'10004' UNION ALL
SELECT N'TestUser1', N'10005' UNION ALL
SELECT N'SLisa', N'10006' UNION ALL
SELECT N'SLisa', N'10007' UNION ALL
SELECT N'Jdoe', N'10008' UNION ALL
SELECT N'Lnguyen', N'10009'
go

SELECT *
FROM OL;
GO

/*

SpecialistId LoanNum
--------------- ----------
Jdoe 10000
LNguyen 10001
SLisa 10002
TestUser1 10003
TestUser1 10004
TestUser1 10005
SLisa 10006
SLisa 10007
Jdoe 10008
Lnguyen 10009

*/

-- Business rules: If the LoginId is in the CM or IT role than he/she can see all the Loans
2. If the LoginId is in the CS (Collaterial Specialist) than he can ONLY see his Loans.

-- I need to create a function to accept LoginId and return all his/her loans base up on Security Role.

-- Testing...

DECLARE @IsFilter BIT = 0
DECLARE @LoginId VARCHAR(50) = 'LNguyen' --'Cruz'

SELECT @IsFilter = 1
FROM SecurityUserRoles
WHERE RoleCode IN ('CM'. 'IT')
AND LoginId = @LoginId

SELECT @IsFilter

--INSERT @tblLoans (loannum)
SELECT SpecialistId,LoanNum
FROM OrderLoans
WHERE (SpecialistId = @LoginId)
OR ( @IsFilter = 1 )
go

-------------------------------------------------------------------------------
--Function.

CREATE FUNCTION [dbo].[Testfn_tblUserLoans]
(
@LoginId varchar(50)
)
RETURNS @Loans TABLE (loannum varchar(10) PRIMARY KEY NOT NULL)
AS
BEGIN
DECLARE @IsFilter BIT = 0

SELECT @IsFilter = 1
FROM SecurityUserRoles
WHERE RoleCode IN ('CM', 'IT')
AND LoginId = @LoginId

INSERT @tblLoans (loannum)
SELECT LoanNum
FROM OL
WHERE ( (SpecialistId = @LoginId)
OR ( @IsFilter = 1 ) )
RETURN
END
go

----------------------------------------------------------------------------------------------

-- Need to incorporate the newly create function into the sp below.

ALTER PROCedure [dbo].[spLoanQ_LoanList]
(
@PoolNum VARCHAR(6) = NULL,
@CustomerCode CHAR(3) = NULL,
@OrderId INT = NULL,
@LoginID VARCHAR(50),
@userName VARCHAR(255) = ''
)
AS
/***********************************************************************************************
**
** Modifications:
** ----------------------------------
** Date: Author: Reasons:
** ------------+-----------------------------------------------------------------------
**
**
***********************************************************************************************/
SET nocount ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @IsFilter BIT = 0

SELECT @IsFilter = 1
FROM SecurityUserRoles
WHERE (RoleCode IN ('IT', 'CM') )
AND (LoginId = @LoginId)

IF @PoolNum = ''
SET @PoolNum = NULL

IF @CustomerCode = ''
SET @CustomerCode = NULL

SELECT p.CustomerCode,
p.PoolNum,
o.OrderId,
l.SellerLoanId,
luas.[Description] AS 'Status',
luas.ActionRequired,
l.LoanNum,
l.BorrowerLastName,
l.BorrowerFirstName,
l.PropertyAddress,
l.PropertyCity,
l.PropertyCounty,
l.PropertyState,
l.PropertyZipCode,
ol.SpecialistId,
lo.UserName as 'SpecialistName'
FROM Pools AS p
JOIN Orders AS o
ON p.PoolNum = o.PoolNum
JOIN OrderLoans AS ol
ON ol.OrderId = o.OrderId
JOIN Loans AS l
ON l.LoanNum = ol.LoanNum
JOIN LUActionStatus AS luas
ON luas.ActionStatusCode = ol.ActionStatusCode
JOIN Logins lo on ol.SpecialistId = lo.LoginID
WHERE ( ol.CurrentOrder = 1
AND ( o.PoolNum = @PoolNum OR @PoolNum IS NULL)
AND ( p.CustomerCode = @CustomerCode OR @CustomerCode IS NULL)
AND ( ol.OrderId = @OrderId OR @OrderId IS NULL) )

AND ( (ol.SpecialistId = @LoginId)
OR ( @IsFilter = 1 ) );
GO[/code]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-28 : 14:27:24

Why do you need this as a function? Can't you simply join the OL table to the main query in the stored procedure?

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-01-28 : 14:44:53

[code]I need to use the Function because I have to put into another 20 SPs and want to be dynamic SP. I would love to make life easier if just only 1 and 2 SPs. Thanks.[/code]

quote:
Originally posted by robvolk

Why do you need this as a function? Can't you simply join the OL table to the main query in the stored procedure?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources