Help with Stored Procedure - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Help with Stored Procedure

Author

Topic

codemarx
Starting Member

2 Posts

Posted - 2008-11-21 : 20:21:26

Need help cleaning this up.

ALTER PROCEDURE [dbo].[proc_assign_lead]
@lmbid int
AS
SET NOCOUNT ON;

DECLARE @ZipCode int
DECLARE @MailZipCode int
DECLARE @Zip int
DECLARE @SourceID int
DECLARE @StateID int
DECLARE @CountyID int
DECLARE @ServiceCnt int
DECLARE @clientid int
DECLARE @userid int
DECLARE @startdate datetime
DECLARE @Client int
DECLARE @User int
DECLARE @Quota int
DECLARE @qCnt int
DECLARE @aClientID int
DECLARE @aUserID int
DECLARE @LeadID Nvarchar(255)

Set @startdate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

Create Table #AssignLeads (
lmbid int, UID int, OrgID int, qCnt int)

Set @SourceID = (SELECT sourceid from leads where lmbid = @lmbid)
Set @ZipCode = (SELECT zipcode from leads where lmbid = @lmbid)
Set @MailZipCode = (SELECT MailZipCode from leads where lmbid = @lmbid)
Set @LeadID = (SELECT LeadID from leads where lmbid = @lmbid)

If (@ZipCode IS NOT NULL)
Set @Zip = @ZipCode
Else
Set @Zip = @MailZipCode

Set @CountyID = (SELECT distinct countyid from tbl_zips where zipcode = @Zip)
Set @StateID = (SELECT distinct dbo.tbl_states.stateid FROM dbo.tbl_zips INNER JOIN dbo.tbl_states ON dbo.tbl_zips.stateid = dbo.tbl_states.stateid WHERE (dbo.tbl_states.active = 1) AND (dbo.tbl_zips.zipcode = @Zip))

If (@CountyID IS NULL)
BEGIN
DECLARE @PropertyCounty NVARCHAR(50)
DECLARE @PropertyState NVARCHAR(2)
Set @PropertyCounty = (SELECT PropertyCounty from leads where lmbid = @lmbid)
Set @PropertyState = (SELECT PropertyState from leads where lmbid = @lmbid)
Set @StateID = (SELECT stateid from tbl_states where stateabbv = @PropertyState)
Set @CountyID = (SELECT countyid from tbl_counties where county_name = @PropertyCounty and stateid = @StateID)
END

--Count users that service county else go to state check
If (Select count(*) from tbl_matrix_service_areas where countyid = @CountyID) > 0
BEGIN --Begin(1) insert users into temp table that service county and have not filled quota
insert into #AssignLeads
(OrgID,UID,qCnt,lmbid)
SELECT TOP(7) msa.clientid, msa.userid, mq.quota -
(SELECT COUNT(*) AS Expr1
FROM dbo.leads
WHERE (assigned > @startdate) AND (sourceid = @SourceID) AND (userid = msa.userid)) AS qCnt, @lmbid as lmbid
FROM dbo.tbl_matrix_service_areas AS msa INNER JOIN
dbo.tbl_matrix_quota AS mq ON msa.userid = mq.userid
WHERE (msa.countyid = @CountyID) AND (mq.sourceid = @SourceID) AND (mq.quota -
(SELECT COUNT(*) AS Expr1
FROM dbo.leads AS leads_1
WHERE (assigned > @startdate) AND (sourceid = @SourceID) AND (userid = msa.userid)) > 0)
ORDER BY qCnt Desc, NEWID()

--Count users that have been inserted into temp table if no users sent to check state quota
If (Select count(*) from #AssignLeads) > 0
BEGIN --Begin(2) Assign lead to user
Select top(1) a.UID as userid, a.OrgID as clientid
--update leads
--set userid = a.UID, clientid = a.OrgID, assigned = getdate()
FROM #AssignLeads as a inner join leads l on l.lmbid = a.lmbid
where l.lmbid = @lmbid
order by NEWID()
END --End(2) Assign lead to user
--All users that service area have full quota's sending to that check
Else
BEGIN --Begin(3) State check otherwise send to Over ride
If (Select count(*) from tbl_matrix_service_areas where stateid = @StateID) > 0
BEGIN --Begin(4) Insert into temp table for users that service state
insert into #AssignLeads
(OrgID,UID,qCnt,lmbid)
SELECT TOP (7) msa.clientid, msa.userid, mq.quota -
(SELECT COUNT(*) AS Expr1
FROM dbo.leads
WHERE (assigned > @startdate) AND (sourceid = @SourceID) AND (userid = msa.userid)) AS qCnt, @lmbid as lmbid
FROM dbo.tbl_matrix_service_areas AS msa INNER JOIN
dbo.tbl_matrix_quota AS mq ON msa.userid = mq.userid
WHERE (msa.stateid = @StateID) AND (mq.sourceid = @SourceID) AND (mq.quota -
(SELECT COUNT(*) AS Expr1
FROM dbo.leads AS leads_1
WHERE (assigned > @startdate) AND (sourceid = @SourceID) AND (userid = msa.userid)) > 0)
ORDER BY qCnt Desc, NEWID()

If (Select count(*) from #AssignLeads) > 0
BEGIN --Begin(5)
Select top(1) a.UID as userid, a.OrgID as clientid
--update leads
--set userid = a.UID, clientid = a.OrgID, assigned = getdate()
FROM #AssignLeads as a inner join leads l on l.lmbid = a.lmbid
where l.lmbid = @lmbid
order by NEWID()
END --End(5)
Else
BEGIN --Begin(6)
--update leads
--set userid = 2, clientid = 1, assigned = getdate()
Select 2 as userid, 1 as clientid
--where lmbid = @lmbid
END --End(6)
END --End(4)
Else
BEGIN --Begin(7)
--update leads
--set userid = 2, clientid = 1, assigned = getdate()
Select 2 as userid, 1 as clientid
--where lmbid = @lmbid
END --End(7)
END --End(3) State check
END --End(1) insert users into temp table that service county and have not filled quota
Else
BEGIN
--Count users that service state else send to Over ride
If (Select count(*) from tbl_matrix_service_areas where stateid = @StateID) > 0
BEGIN
insert into #AssignLeads
(OrgID,UID,qCnt,lmbid)
SELECT TOP (7) msa.clientid, msa.userid, mq.quota -
(SELECT COUNT(*) AS Expr1
FROM dbo.leads
WHERE (assigned > @startdate) AND (sourceid = @SourceID) AND (userid = msa.userid)) AS qCnt, @lmbid as lmbid
FROM dbo.tbl_matrix_service_areas AS msa INNER JOIN
dbo.tbl_matrix_quota AS mq ON msa.userid = mq.userid
WHERE (msa.stateid = @StateID) AND (mq.sourceid = @SourceID) AND (mq.quota -
(SELECT COUNT(*) AS Expr1
FROM dbo.leads AS leads_1
WHERE (assigned > @startdate) AND (sourceid = @SourceID) AND (userid = msa.userid)) > 0)
ORDER BY qCnt Desc, NEWID()

If (Select count(*) from #AssignLeads) > 0
BEGIN
Select top(1) a.UID as userid, a.OrgID as clientid
--update leads
--set userid = a.UID, clientid = a.OrgID, assigned = getdate()
FROM #AssignLeads as a inner join leads l on l.lmbid = a.lmbid
where l.lmbid = @lmbid
order by NEWID()
END
Else
BEGIN
--update leads
--set userid = 2, clientid = 1, assigned = getdate()
Select 2 as userid, 1 as clientid
--where lmbid = @lmbid
END

END
Else
BEGIN
--update leads
--set userid = 2, clientid = 1, assigned = getdate()
Select 2 as userid, 1 as clientid
--where lmbid = @lmbid
END
END
--Send to survey but check to see if lead already has been sent

If (Select NeedPurchaseREAgent from leads where lmbid = @lmbid) = 'Yes'
BEGIN
If (Select Count(*) from ab.dbo.tbl_contacts where URL = @LeadID) = 0
BEGIN
insert ab.dbo.tbl_contacts
(FormType, BaseLoan, LoanPurpose, firstname, lastname, streetaddress, city, zip, homephone, altphone, workphone, emailaddress, userIP, URL, Site, propertytype, lookingin, purprice, LoanTimeframe, IntendedPropertyUse, Employer, cofirstname, colastname)
Select '2',NewPropertyValue,'2',FirstName,LastName,MailStreetAddress,MailCity,MailZipCode,Phone1,Phone2,'', Email,IPAddress,LeadID,'AFP', '1', '',DesiredLoanAmount,LoanTimeFrame,IntendedPropertyUse, '', '', ''
from leads
where lmbid = @lmbid
End
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 00:10:39

you're storing the results of lot of subqueries to variables. Are you sure they all return only a single value? else you cant store them in a variable. you need to use table variable for storing them.

codemarx
Starting Member

2 Posts

Posted - 2008-11-22 : 16:08:57

It works correctly as it is, but I think there must be any easier way or cleaner way of obtain the results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources