SQL 2005- Cursor Loop

Aug 6, 2007

Please advice when i used loop in the below code its only returning last value from the sub table however i wanted to fetch all values from the sub-table----
Please advice..

ALTER Procedure [dbo].[SyncEmpContGrid] @EmpID As Varchar(20)

As

Declare @CountryEOF bit,

@i int,

@CtR Varchar (1000)



begin

Declare @C_list Cursor

Declare ContCr Cursor Local for

(Select custCountryofExperience from Employees_CountryExperience where Employee=@EmpID)

Set @C_List = contCr



Open @C_List

Fetch next from @C_List into @CtR

While (@@Fetch_Status = 0 )

Begin

set @ctR = @ctR + ','

Fetch next from @C_List into @CtR



update EmployeeCustomTabFields

Set custTxt_Ct = @CtR Where (EmployeeCustomTabFields.Employee = @EmpID);

end

Close @C_List

Deallocate @C_List

Deallocate ContCr

end

View 4 Replies


ADVERTISEMENT

Loop Though Table Using RowID, Not Cursor (was Loop)

Feb 22, 2006

I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks

View 6 Replies View Related

C# Loop Or Cursor

Apr 24, 2008

Hi,
In a enterprise server which should be used?
using SQL Cursor or loop in C# code?
 
Thanks in advance
 MAthew

View 1 Replies View Related

Sql Cursor Or App Loop?

Mar 6, 2008

I am writing a function that changes quote items prices to that of a given exchange rate. Now, there are a few business rules to conisder that I have to work around but basically I will be taking items in a table for a given quote, iterating through changing the price based on the exchange rate requested. Now, I get into writing this and I think I need a cursor. Its the only way I can get the specific pricing information based on pricelists (my constraints) for every item. Now I have never written a cursor before so thus far I have been enjoying toying and learning this. Now the industry says this is SQL of last resort correct? Suddenly I start to think why not just write one procedure that changes the item price appropraitely but use a recordset at the application level and use a loop there for every item which would mean I avoid the need for a cursor!

But I have never written a cursor before, so would I benefit from carrying on trying to work one out (I have the time) and getting the experience of doing so or do I just use a loop in the app and do as im told?

"Impossible is Nothing"

View 1 Replies View Related

Loop / Cursor Help

Feb 20, 2006

Having a brain cramp here and don't know where to start. I have 2 tables:vehicles and vehicle_useage. What I would like to do is this:For each distinct vehicle in the vehicle table, I want to make entriesfor each day of the month taken from a given date. This routine will bescheduled to fire off once a month and populate the vehicle_useage tablewith vehicle use_dates for each day of the current month and for each VINfrom the vehicle table.vehicle table:VIN emp_id------------ ------VIN123456789 620123VIN987654321 620123vehicle_useage table:use_date VIN miles----------- ------------ -----02/01/2006 VIN123456789 002/02/2006 VIN123456789 002/03/2006 VIN123456789 002/04/2006 VIN123456789 0etc....02/01/2006 VIN987654321 002/02/2006 VIN987654321 002/03/2006 VIN987654321 002/04/2006 VIN987654321 0etc...Much appreciated for any help you can give...

View 9 Replies View Related

Cursor Loop

Jul 12, 2006

Hello,I've created a stored procedure that loops through a cursor, with thefollowing example code:DECLARE curPeriod CURSOR LOCAL for SELECT * FROM tblPeriodsDECLARE @intYear smallintDECLARE @intPeriod smallintDECLARE @strTekst varchar(50)OPEN curPeriodWHILE @@FETCH_STATUS=0BEGINFETCH NEXT FROM curPeriod INTO @intYear, @intPeriodSET @strTekst = CONVERT(varchar, @intPeriod)PRINT @strTekstENDCLOSE curPeriodDEALLOCATE curPeriodThe problem is that this loop only executes one time, when I call thestored procedure a second or third time, nothing happens. It seems thatthe Cursor stays at the last record or that @@Fetch_status isn't 0. ButI Deallocate the cursor. I have to restart the SQL Server before thestored procedure can be used again.Does anyone know why the loop can execute only 1 time?Greetings,Chris*** Sent via Developersdex http://www.developersdex.com ***

View 2 Replies View Related

Loop Without Cursor

Feb 6, 2008

Hi,

I'm trying to figure out if there is a way to do the following without putting my variable values in a table and then stepping through a cursor.
Let's say the variable combinations are:

Name1, 1
Name2, 2
Name3, 3

I need to run the following code for each pair of values. The actual insert statement is more complicated and the example values are different too


DECLARE @Name varchar(25), @Code int

SET @Name = 'Name1'
SET @Code = 1


<INSERT INTO Table SELECT * FROM OtherTable WHERE Name = @Name AND Code = @Code>



Thanks

View 3 Replies View Related

Cursor While Loop Problem.. Please Help

Jun 4, 2004

*** edited by: master4eva ***
Please enclose your code in < code ></ code> tags (without the spaces). This will make your code easier to read online; therefore, encouraging a response to be faster. It is to your own benefit for your question to be answered in future.

I have already done the editing to include the < code ></ code> tags for this post.
*********

It will process the first REID but the second and so on REID won't... any idea where is the problem in my cursor

ALTER PROCEDURE TrigRetReqRecIDP2
@REID int

AS


Declare @RRID int
Declare @APID int
Declare @intREID varchar(20)
Declare @intIMID varchar(20)
Declare @RetValint
Declare crReqRec cursor for
select RRID from RequestRecords where REID = @REID and RRSTatus = 'PE' and APID is not null
open crReqRec
fetch next from crReqRec
into
@RRID
set @APID = (select APID from RequestRecords where REID = @REID and RRID = @RRID)

set @intIMID = (select IMID from Applications_ImplementationGroup where APID = @APID)
insert into ImplementationTasks
(
IMID,
ITStatus,
ITStatusDate
)
VALUES
(
@intIMID,
'2',
GetDate()
)
SET @RetVal = SCOPE_IDENTITY()
while @@fetch_status = 0

Update RequestRecords
set ITID = @RETVal, RRStatus = 'IA'
where REID = @REID and RRID = @RRID

FETCH NEXT FROM crReqRec
into
@RRID

close crReqRec
deallocate crReqRec

View 2 Replies View Related

Loop Still Fails In Cursor

Oct 24, 2000

I have been working on a loop that needs to run inside of a cursor statement.
It has to check for a difference in days and create transaction records
for the difference. The problem is that if I include this while statement
it will only process 1 record with the cursor and stop. If I remove the
while it will work for all the records the cursor should be reading but
doesn't give the multiple transactions I need if there is a day difference.
Is there a limitation to using a while inside of a cursor. Below is the
code. ANy hep is appreciated.

cursor stuff
declare dbcursor cursor for
select uniq_id,account_id,created_by,encounter_id,
start_date,date_stopped,sig_codes, ndc_id,modified_by
from patient_medication where convert(datetime,start_date) = '10/20/2000'
and date_stopped is not null and date_stopped <> start_date order by uniq_id
open dbcursor
fetch next from dbcursor into @uniqid,@account_id,@createid,@entcid,
@sdate, @edate ,@sig_code, @ndcid, @modid
while (@@FETCH_STATUS =0)
begin
--freq stuff
select @freq = SIG.sig_frequency
FROM SIG where SIG.SIG_KEY = @sig_code
--check for evey other day
set @freq = 1
set @nodays = datediff(day, @sdate - 1, @edate)
select @nodays
while @cnter < @nodays
begin
insert into PATIENT_MEDICATION_DISPERSAL_
(uniq_id,account_id, occurance_id, encounter_id, ndc_id, ddate, frequency, sig_code,disp_create_id, disp_mod_id)
values (@uniqid,@account_id,@fcnter, @entcid, @ndcid, @sdate, @freq, @sig_code,@createid, @modid )
set @fcnter = 1
set @sdate = @sdate + 1
Set @cnter = @cnter + 1
end
fetch next from dbcursor into @uniqid,@account_id,@createid,@entcid,
@sdate, @edate ,@sig_code, @ndcid, @modid
END
close dbcursor
deallocate dbcursor

View 3 Replies View Related

Loop Inside Of A Cursor

Oct 23, 2000

I have a loop(while) statement I need to run inside a cursor statement. The loop creates records based on a frequency. The cursor and the loop work but the problem is that the cursor only reads the first record, runs the loop, but then ends. I am pasting the code below. Any help appreciated

declare dbcursor cursor for select uniq_id,account_id,created_by,encounter_id, start_date,date_stopped,sig_codes, ndc_id,modified_by from patient_medication where convert(datetime,start_date) = '10/20/2000' and date_stopped is not null
open dbcursor fetch next from dbcursor into @uniqid,@account_id,@createid,@entcid, @sdate, @edate ,@sig_code, @ndcid, @modid
while (@@FETCH_STATUS <> -1)
begin
select @freq = SIG.sig_frequency FROM SIG where SIG.SIG_KEY = @sig_code
set @hfreq = @freq if @freq = 9 set @freq = 1 set @nodays = datediff(day, @sdate - 1, @edate)
while @cnter < @nodays
begin
while @fcnter < @freq + 1 begin insert into PATIENT_MEDICATION_DISPERSAL_ (uniq_id,account_id, occurance_id, encounter_id, ndc_id, ddate, frequency, sig_code,disp_create_id, disp_mod_id) values (@uniqid,@account_id,@fcnter, @entcid, @ndcid, @sdate, @freq, @sig_code,@createid, @modid )
set @fcnter = @fcnter + 1
set @erdate = @sdate

END
if @hfreq = 9
begin set @fcnter = 1
set @sdate = @sdate + 2
Set @cnter = @cnter + 2
end
else
begin
set @fcnter = 1
set @sdate = @sdate + 1
Set @cnter = @cnter + 1
end
end
end
close dbcursor
deallocate dbcursor

View 2 Replies View Related

Cursor Versus While Loop

Oct 29, 2007

I have always been told that Cursors create a lot of overhead and consume a lot of system resources. Is it faster to store the data in a temp table and loop through it by using Select Top 1 and Delete statements or by using a static, Forward-Only Cursor? Both ways store the data in TempDB, but doesn't the While Loop statement generate more IO's than the Cursor? In theory, I am thinking that the Cursor is better. Any info will be appreciated.

Thanks!!

View 2 Replies View Related

Cursor, Loop Or Case?

May 11, 2006

Just started here, so here's what I got. I would ask the developer, but he's already gone (now I see why;-))

We are trying to pull leads from a table that have not been sold (numbsold), is available, not expired, has no agent

Here's an example of a leadtimeframeid - 1= 1month , 2=1-3months 3= 4-5months

Here's an example of LeadtypeID 1= sell 2=buy 3=buy/sell

He seems to be going through the table and looking for a variation of each..

Such as for leadtimeframe 1, pull leadstypes 1 and 2 and union each on the 3.

The result set should be as follows -

lead_id,parent_lead_id,lead_type_id, buy_zip_1, buy_zip_2, buy_zip_3, zip

Hope that explains it!!

I'ved included the SP and the table schema.. any help would be greatly appreciated.








ALTER PROCEDURE dbo.usp_GetRNLeadsCapOptimization
@LookBackDays INT,
@LeadTimeFrame INT,
@PropertyValue INT,
@WorkWithRealtor TINYINT,
@LeadTypeID TINYINT,
@strZIP VARCHAR(5)

AS
DECLARE @PriceRange MONEY,
@DateIn DATETIME

BEGIN

SET @DateIn = CAST(CONVERT(VARCHAR(10), DATEADD(d, -@LookBackDays, CURRENT_TIMESTAMP), 101) AS DATETIME) + CAST('12:00:00AM' AS DATETIME)
SET @PriceRange = 100000.0000

IF (@PropertyValue = 1)
SET @PriceRange = 100000.0000
ELSE IF (@PropertyValue = 2)
SET @PriceRange = 200000.0000
ELSE IF (@PropertyValue = 3)
SET @PriceRange = 300000.0000
ELSE IF (@PropertyValue = 4)
SET @PriceRange = 400000.0000
ELSE IF (@PropertyValue = 5)
SET @PriceRange = 500000.0000
ELSE IF (@PropertyValue = 6)
SET @PriceRange = 600000.0000
ELSE IF (@PropertyValue = 7)
SET @PriceRange = 700000.0000
ELSE IF (@PropertyValue = 8)
SET @PriceRange = 800000.0000
ELSE IF (@PropertyValue = 9)
SET @PriceRange = 900000.0000
ELSE IF (@PropertyValue = 10)
SET @PriceRange = 1000000.0000
ELSE IF (@PropertyValue = 11)
SET @PriceRange = 2000000.0000
ELSE IF (@PropertyValue = 12)
SET @PriceRange = 3000000.0000
ELSE IF (@PropertyValue = 13)
SET @PriceRange = 4000000.0000
ELSE IF (@PropertyValue = 14)
SET @PriceRange = 5000000.0000
ELSE IF (@PropertyValue = 15)
SET @PriceRange = 6000000.0000
ELSE IF (@PropertyValue = 16)
SET @PriceRange = 7000000.0000
ELSE IF (@PropertyValue = 17)
SET @PriceRange = 8000000.0000
ELSE IF (@PropertyValue = 18)
SET @PriceRange = 9000000.0000
ELSE IF (@PropertyValue = 19)
SET @PriceRange = 10000000.0000


IF (@WorkWithRealtor = 0)
BEGIN
IF (@LeadTimeFrame = 1)
BEGIN
IF (@LeadTypeID = 1)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 1 -- BUY
AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
AND tlq.time_frame_id = 23
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 3 --BUY/SELL
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id = 23
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
ORDER BY tlq.lead_id, tlq.lead_type_id
END

ELSE IF(@LeadTypeID = 2)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 2 --SELL
AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
AND tlq.time_frame_id = 23
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 3 --BUY/SELL
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id = 23
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP
ORDER BY tlq.lead_id, tlq.lead_type_id
END
END

ELSE IF(@LeadTimeFrame = 2)
BEGIN
IF(@LeadTypeID = 1)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 1
AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
AND tlq.time_frame_id IN (23, 24)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id IN (23, 24)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
ORDER BY tlq.lead_id, tlq.lead_type_id
END

ELSE IF(@LeadTypeID = 2)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 2
AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
AND tlq.time_frame_id IN (23, 24)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id IN (23, 24)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP
ORDER BY tlq.lead_id, tlq.lead_type_id
END
END

ELSE IF(@LeadTimeFrame = 3)
BEGIN
IF(@LeadTypeID = 1)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 1
AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
AND tlq.time_frame_id IN (23, 24, 25)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id IN (23, 24, 25)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
ORDER BY tlq.lead_id, tlq.lead_type_id
END

ELSE IF(@LeadTypeID = 2)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 2
AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
AND tlq.time_frame_id IN (23, 24, 25)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id IN (23, 24, 25)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP
ORDER BY tlq.lead_id, tlq.lead_type_id
END
END

ELSE IF(@LeadTimeFrame = 4)
BEGIN
IF (@LeadTypeID = 1)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 1
AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
AND tlq.time_frame_id IN (23, 24, 25, 107)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id IN (23, 24, 25, 107)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
ORDER BY tlq.lead_id, tlq.lead_type_id
END
ELSE IF(@LeadTypeID = 2)
BEGIN

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 2
AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
AND tlq.time_frame_id IN (23, 24, 25, 107)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id IN (23, 24, 25, 107)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP
ORDER BY tlq.lead_id, tlq.lead_type_id
END
END

ELSE IF(@LeadTimeFrame = 5)
BEGIN
IF (@LeadTypeID = 1)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 1
AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
AND tlq.time_frame_id = 96
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id = 96
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
ORDER BY tlq.lead_id, tlq.lead_type_id
END

ELSE IF(@LeadTypeID = 2)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 2
AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
AND tlq.time_frame_id = 96
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.has_agent = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id = 96
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP
ORDER BY tlq.lead_id, tlq.lead_type_id
END
END
END

ELSE IF (@WorkWithRealtor = 1)
BEGIN
IF (@LeadTimeFrame = 1)
BEGIN
IF (@LeadTypeID = 1)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 1
AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
AND tlq.time_frame_id = 23
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id = 23
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
ORDER BY tlq.lead_id, tlq.lead_type_id
END

ELSE IF(@LeadTypeID = 2)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 2
AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
AND tlq.time_frame_id = 23
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id = 23
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP
ORDER BY tlq.lead_id, tlq.lead_type_id
END
END

ELSE IF(@LeadTimeFrame = 2)
BEGIN
IF(@LeadTypeID = 1)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 1
AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
AND tlq.time_frame_id IN (23, 24)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id IN (23, 24)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
ORDER BY tlq.lead_id, tlq.lead_type_id
END

ELSE IF(@LeadTypeID = 2)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 2
AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
AND tlq.time_frame_id IN (23, 24)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id IN (23, 24)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP
ORDER BY tlq.lead_id, tlq.lead_type_id
END
END

ELSE IF(@LeadTimeFrame = 3)
BEGIN
IF(@LeadTypeID = 1)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 1
AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
AND tlq.time_frame_id IN (23, 24, 25)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id IN (23, 24, 25)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
ORDER BY tlq.lead_id, tlq.lead_type_id
END

ELSE IF(@LeadTypeID = 2)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 2
AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
AND tlq.time_frame_id IN (23, 24, 25)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id IN (23, 24, 25)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP
ORDER BY tlq.lead_id, tlq.lead_type_id
END
END

ELSE IF(@LeadTimeFrame = 4)
BEGIN
IF (@LeadTypeID = 1)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 1
AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
AND tlq.time_frame_id IN (23, 24, 25, 107)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id IN (23, 24, 25, 107)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
ORDER BY tlq.lead_id, tlq.lead_type_id
END
ELSE IF(@LeadTypeID = 2)
BEGIN

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 2
AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
AND tlq.time_frame_id IN (23, 24, 25, 107)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id IN (23, 24, 25, 107)
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP
ORDER BY tlq.lead_id, tlq.lead_type_id
END
END

ELSE IF(@LeadTimeFrame = 5)
BEGIN
IF (@LeadTypeID = 1)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 1
AND ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
AND tlq.time_frame_id = 96
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id = 96
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND (tlq.buy_zip_1 = @strZIP OR ISNULL(tlq.buy_zip_2, '') = @strZIP OR ISNULL(tlq.buy_zip_3, '') = @strZIP)
ORDER BY tlq.lead_id, tlq.lead_type_id
END

ELSE IF(@LeadTypeID = 2)
BEGIN
SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 2
AND ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
AND tlq.time_frame_id = 96
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP

UNION

SELECT tlq.lead_id, tlq.parent_lead_id, tlq.lead_type_id,
tlq.buy_zip_1, ISNULL(tlq.buy_zip_2, '') AS buy_zip_2, ISNULL(tlq.buy_zip_3, '') AS buy_zip_3, tlq.zip
FROM tbl_leads_queue tlq WITH (NOLOCK)
WHERE tlq.lead_id = tlq.parent_lead_id
AND tlq.NumSold = 0
AND tlq.is_available = 1
AND tlq.Expired = 0
AND tlq.lead_type_id = 3
AND (
ISNULL(tlq.buy_price_range_start, 0) >= @PriceRange
OR
ISNULL(tlq.sell_price_qualifying, 0) >= @PriceRange
)
AND tlq.time_frame_id = 96
AND NOT EXISTS (SELECT lead_id FROM tbl_leads_queue WITH (NOLOCK)
WHERE parent_lead_id = tlq.parent_lead_id
AND (lead_status_id = 3 OR lead_status_id = 4))
AND tlq.date_in >= @DateIn
AND tlq.zip = @strZIP
ORDER BY tlq.lead_id, tlq.lead_type_id
END
END
END
END




---Table schema


CREATE TABLE [dbo].[tbl_leads_queue] (
[lead_id] [bigint] IDENTITY (1, 1) NOT NULL ,
[parent_lead_id] [bigint] NOT NULL ,
[partner_id] [int] NOT NULL ,
[RealtorCompanyID] [bigint] NOT NULL ,
[RealtorPrimaryContactUserName] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RealtorPrimaryContactFirstName] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RealtorPrimaryContactLastName] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RealtorCompanyName] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RealtorTotalOffices] [int] NOT NULL ,
[RealtorTotalAgents] [int] NOT NULL ,
[RealtorBrandLogoURL] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RealtorMoreInfoURL] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RealtorWeekendCoverage] [bit] NOT NULL ,
[RealtorCustomerServiceRating] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DesiredMonthlyPayment] [float] NOT NULL ,
[realtor_id] [bigint] NULL ,
[lead_type_id] [int] NOT NULL ,
[lead_status_id] [int] NOT NULL ,
[buy_property_type_id] [int] NULL ,
[sell_property_type_id] [int] NULL ,
[time_frame_id] [int] NOT NULL ,
[best_time_id] [int] NOT NULL ,
[matched_on] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[city] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone_area] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone_prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone_suffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_ext] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_area2] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_prefix2] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone_suffix2] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[buy_zip_1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[buy_city_state_1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[buy_zip_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_city_state_2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_zip_3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_city_state_3] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[buy_price_range_start] [money] NOT NULL ,
[buy_price_range_end] [money] NOT NULL ,
[buy_square_footage] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[buy_bedroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[buy_bathroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[buy_detail_list] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sell_price_desired] [money] NULL ,
[sell_price_qualifying] [money] NULL ,
[sell_square_footage] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sell_bedroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sell_bathroom_count] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sell_detail_list] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[additional_info] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lead_fee] [money] NOT NULL ,
[no_charge] [int] NULL ,
[credited] [int] NOT NULL ,
[lead_problem] [int] NULL ,
[date_in] [datetime] NOT NULL ,
[date_sent] [datetime] NULL ,
[TrafficLogID] [bigint] NOT NULL ,
[notify_offers] [bit] NOT NULL ,
[credit_history] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[has_agent] [bit] NOT NULL ,
[found_home] [bit] NOT NULL ,
[cell_area] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cell_prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cell_suffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[why_selling] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[is_buysell] [bit] NOT NULL ,
[Affiliate_ID] [bigint] NULL ,
[free_mortgage_quote] [bit] NOT NULL ,
[loan_type] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[gross_monthly_income] [float] NOT NULL ,
[can_verify_income] [bit] NOT NULL ,
[desired_loan_amount] [float] NOT NULL ,
[existing_loan_balance] [float] NOT NULL ,
[first_mortgage_monthly_payment] [float] NOT NULL ,
[current_interest_rate] [float] NOT NULL ,
[RealtorNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NumSold] [int] NOT NULL ,
[is_available] [bit] NOT NULL ,
[Date_Declined] [datetime] NULL ,
[Expired] [bit] NOT NULL ,
[Original_Affiliate] [bigint] NULL ,
[lead_distance] [float] NULL ,
[lead_Problem_Comments] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdminUserID] [int] NULL ,
[dateCredited] [datetime] NULL ,
[OriginalAffiliateID] [bigint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

View 1 Replies View Related

Infinite Loop In Cursor

Jul 5, 2006

Hi

I have an infinite loop in a trigger I and I cant reslove it.

In my system the user updates a stock table from the GUI and on the update I need to check values to see if I need to add records to a StockHistory table.  For Example:  If the user changes the grade of Product X from A to B then I need to add a new line in StockHistory for product X grade A that decrements the total number of products in the warehouse.  Similary I need to increase the quantity of stock for Product X grade B.

I had the trigger working for single updates but now when stock is added to the database (from another db) it has status of 'New'.  This isn't actually 'in stock' until the user sets the status to 'Goods In'.  This process will then update the status for all records in the category.  This caused my trigger to fail as the 'inserted' table now contains many records.

Now the problem I have is the trigger is in an infinite loop. It always shows the id of the first record it finds and the @Quantity values increases as expected.  I've taken all my procesing code out of the trigger and adding some debugging stuff but it still doesnt work:

CREATE TRIGGER [StockReturns_on_change] ON [dbo].[StockReturns]
FOR UPDATE
AS

DECLARE INDIVIDUAL Cursor --- Cursor for all the rows being updated

FOR
SELECT Id FROM inserted

OPEN INDIVIDUAL

FETCH NEXT FROM INDIVIDUAL INTO @Id

select @Quantity = 1

print @@FETCH_STATUS
print @Id
print @Quantity

WHILE @@FETCH_STATUS = 0
begin 

select @Quantity = @Quantity + 1

print @@FETCH_STATUS
print @Id
print @Quantity

-- Get the next row from the inserted table
FETCH NEXT FROM INDIVIDUAL INTO @Id

End  -- While loop on the cursor

-- no close off the cursors
CLOSE INDIVIDUAL
DEALLOCATE INDIVIDUAL





Can you help me please?

Kind Regards

View 13 Replies View Related

Need Help Programming SQL To Run Cursor, Compare, And Loop

Feb 26, 2007

I need to write a program in SQL that will compare the ID field of a table and then if the ID matches will compare the dates an account was opened and when it was closed to see if a customer with multiple accounts under the same ID has overlapping accounts or if the accounts were opened and closed consecutively. Any thoughts on the best way to code this?

View 3 Replies View Related

Combining The Results Of A Cursor Loop

Aug 7, 2007

Need a little help here.

I have a set of product ids fed in as a delimited string and for each I need to extract the top 1 record from another query based on the id.

I need the results as one table.

Here is my code.
___________________________________
SET NOCOUNT ON

DECLARE @IdsString VARCHAR(255), @Id int


SELECT @IdsString = '918|808|1214|89|995|300|526|1207'

DECLARE GetData CURSOR
FOR Select s.ProductID FROM dbo.SplitProductIDs(@IdsString) as s

OPEN GetData

FETCH NEXT FROM GetData
INTO @Id

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT TOP 1 v.*
FROM dbo.GetProductRateView as v
WHERE v.[id] = @Id

FETCH NEXT FROM GetData
INTO @Id
END

CLOSE GetData
DEALLOCATE GetData
_____________________________________

Do I need to create a temp table and do an 'Insert Into(Select...' with each cusor result or is there a better way?

Any help would be much appreciated.

NB Database was not designed and the client will not tolerate any changes to structure of the tables :eek:

Regards

Shaun McGuile

View 14 Replies View Related

Cursor Causing Infinite Loop

Dec 13, 2007

Hi i have a cursor in a Stored Procedure. The problem is that it's poiting to the first row and causing an infinite loop on it.
How can i stop this and make it go to all rows. Here is my code.

Declare @CountTSCourtesy int
Declare @WaiterName nvarchar(100), @CursorRestaurantName nvarchar (100)
Declare waiter_cursor CURSOR FOR

SELECT new_waiteridname, new_restaurantname
FROM dbo.FilteredNew_CommentCard
Where new_dateofvisit between @FromDate and @ToDate and new_restaurantname = @Restaurant
Open waiter_cursor
FETCH NEXT FROM waiter_cursor
into @WaiterName,@CursorRestaurantName
While @@FETCH_STATUS=0

BEGIN
Exec WaitersCountExCourtesy @WaiterName,@CursorRestaurantName

END
Close waiter_cursor
Deallocate waiter_cursor
END


Thanks in advance...

View 1 Replies View Related

How To Loop A Cursor And Accumulate A String Value ?

Dec 12, 2007

Hi
Why can't I loop a cursor and add values to a string in Sql server ?



Code Block
ALTER FUNCTION [dbo].[fn_Get_Project_String]
()
RETURNS nvarchar(255)
AS
BEGIN
DECLARE @Prosjekt nvarchar(255), @Pro nvarchar(255)

DECLARE c1 CURSOR
FOR select nvarchar3
FROM dbo.AllUserDataCopy

OPEN c1
FETCH NEXT FROM c1 INTO @Pro
WHILE @@FETCH_STATUS = 0

BEGIN
select @Prosjekt = '<item>' + @Pro + '</item>' + ''
FETCH NEXT FROM c1 INTO @Pro
select @Prosjekt = @Prosjekt + @Pro
END

CLOSE c1
DEALLOCATE c1

RETURN @Prosjekt
END





Ivar


View 4 Replies View Related

Cursor Count Loop - Update Table

Jul 6, 2000

I am importing a text file that list invoice columns. The invoice detail table needs the line items to be listed with sequential numbers. I import the file to a temp table to do the work in and I know I need to have the cursor do loop through and count, however, I have no more hair to pull out.

The table looks something like this.

inv# SKU
1001 ABC123
1001 DEF456
1001 GHI789
1002 123DEF
1002 456GHI
1002 876HGT

I need the cursor to go through and number each line, something like this.

inv# SKU Line#
1001 ABC123 1
1001 DEF456 2
1001 GHI789 3
1002 123DEF 1
1002 456GHI 2
1002 876HGT 3

Any help is greatly appriciated.

Thanks

View 1 Replies View Related

SQL Server 2012 :: While Loop In Place Of Cursor

Feb 16, 2014

I have a table called Table1 where I have five fields like Tableid, Processigndate, Amount, remainingCollectonCount and Frequency. All total I have more than 5Lacs records.

Now I need to fill up another table Called FutuecashFlow taking the records from Table1. There will be also five Columns like FutureCashflowid, Table1id, Processigndate, Amount.

Now the condition is that if the remainingCollectonCount =6 and the frequency is 12 then there will be the 6 entries in the futurecasflow table where the prcessign datae wille be addeed by 1 month.

For example Table1
Tableid, Processigndate Amount remainingCollectonCount Frequency
1 2014-02-15 48 8 12

the future cash flow table the prcessing date column will be shown in the following way

Processigndate
2014-03-15
2014-04-15
2014-05-15
2014-06-15
2014-07-15
2014-09-15
2014-10-15

I do not to want to use cursor....

View 4 Replies View Related

T-SQL (SS2K8) :: Cursor From Variable - Procedural Loop

May 8, 2014

I am using a cursor (i know - but this is actually something that is a procedural loop).

So effectively i have a table of names of stored procedures. I now have a store proc that loops around these procs and runs each one in order.

Now i am thinking i would like to be able to set the table it loops around in a variable at the start - is it possible to do this? So effectively use a tablename in a variable to use in the sql to define a cursor?

View 6 Replies View Related

T-SQL (SS2K8) :: Replace Cursor - Convert To Recursive CTE Or While Loop

Jul 2, 2014

Need getting the below Cursor query convert to a Recursive CTE or with a while loop as I do not want to use a cursor.

Declare @Companyname Nvarchar (400)
declare @str nvarchar(MAX)
TRUNCATE TABLE STAGING.dbo.[IT_G_L Entry]
DECLARE GLEntry_cursor CURSOR FOR
SELECT REPLACE Name FROM Company where Name <> 'AAAAA'
OPEN GlEntry_cursor

[Code] ....

View 9 Replies View Related

T-SQL (SS2K8) :: Simple Cursor Runs Infinite Loop?

Dec 23, 2014

I'm trying to build a simple cursor to understand how they work. From the temp table, I would like to print out the values of the table, when I run my cursor it just keeps running the output of the first row infinitely. I just want it to print out the 7 rows in the table ...

IF OBJECT_ID('TempDB..#tTable','U') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable

[Code]....

View 2 Replies View Related

Transact SQL :: Creating Stored Procedure With Cursor Loop

Sep 18, 2015

I appear to be having an issue where the @LetterVal and @Numeric variables aren't resetting for each loop iteration, so if no results are found, it just returns the previous loops values since they aren't overwritten.  Below is the stored procedure I've created:

ALTER PROCEDURE [dbo].[ap_CalcGrade] 
-- Add the parameters for the stored procedure here
@studId int,
@secId int,
@grdTyCd char(2),
@grdCdOcc int,
@Numeric int output,

[Code] ....

And below is the "test query" I'm using: 

--  *** Test Program ***
Declare @LetterVal varchar(2), -- Letter Grade
        @Numeric   int,        -- Numeric Grade
        @Result    int         -- Procedure Status (0 = OK) 
Execute @Result = dbo.ap_CalcGrade 102, 86, 'QZ', 3, 

[Code] ....

This is resulting in an output of: 

A+ 97
A+ 97
C- 72

but it should be returning the output below due to the 2nd data set not being valid/found in the sp query:
 
A+ 97
No Find
C- 72

I'm sure this is sloppy and not the most efficient way of doing this, so whats causing the errant results, and if there is any better way I should be writing it.  Below is the assignment requirements:

Create a stored procedure using the STUDENT database called ap_CalcGrade that does the following:

1. Accepts as input STUDENT_ID, SECTION_ID, GRADE_TYPE_CODE, and GRADE_CODE_OCCURRENCE
2. Outputs the numeric grade and the letter grade back to the user
3. If the numeric grade is found, return 0, otherwise return 1
4. You must use a cursor to loop through the GRADE_CONVERSION table to find the letter grade

View 6 Replies View Related

Sql Server 2005 Cursor

Jan 29, 2008

I am not fimilar with cursors at all but need some assistance/direction on how to re-create the following.

The result set will need to give me a list of new customers with sign dates of last year and this current year plus their sales for each year. If the customer has a date signed of 2007 I need to see the "year of sales" for both 2007 and 2008, whether or not they actually had sales.

I'm needing to convert an oracle cursor to Sql Server 2005. If at all possible I would like to stay away from the cursor and create this some other way. Any help would be greatly appreciated.


Customer Date Signed Year of Sale Amount
1111 7/1/07 2007 $50,000.0
2008 0.0


Below is the oracle cursor that was used to calculate this information in the past.

DECLARE
MBLDRN BAV_BUILDER_NEW.BLDRN%TYPE;
MYR_SIGNED BAV_BUILDER_NEW.YEAR%TYPE;
MCOMPANY BAV_BUILDER_NEW.COMPANY%TYPE;
MSHORTNAME BAV_BUILDER_NEW.SHORTNAME%TYPE;
MDSIGNED BAV_BUILDER_NEW.DSIGNED%TYPE;
MDCANCELED BAV_BUILDER_NEW.DCANCELED%TYPE;
MDMTERR BAV_BUILDER_NEW.DMTERR%TYPE;
MDMNAME BAV_BUILDER_NEW.DMNAME%TYPE;
MENGR_TEAM BAV_BUILDER_NEW.ENGR_TEAM%TYPE;
MSALESREGON BAV_BUILDER_NEW.SALESREGON%TYPE;
MDIVCODE BAV_BUILDER_NEW.DIVCODE%TYPE;
MYRLOOP NUMBER;
MLSTYR NUMBER;
MSYEAR DATE;
MEYEAR DATE;
CURSOR CA IS SELECT YEAR,BLDRN,COMPANY,SHORTNAME,DSIGNED,DCANCELED,
DMTERR,DMNAME,ENGR_TEAM,SALESREGON,DIVCODE
FROM BAV_BUILDER_NEW
ORDER BY YEAR;
BEGIN
SELECT YEAR INTO MLSTYR FROM CURRENT_DATES;
OPEN CA;
LOOP
FETCH CA INTO MYR_SIGNED,MBLDRN,MCOMPANY,MSHORTNAME,MDSIGNED,MDCANCELED,
MDMTERR,MDMNAME,MENGR_TEAM,MSALESREGON,MDIVCODE;
EXIT WHEN CA%NOTFOUND;
MYRLOOP := MYR_SIGNED;
WHILE MYRLOOP <= MLSTYR LOOP
SELECT MIN(SYEAR) INTO MSYEAR FROM UDBDATES WHERE YEAR=MYRLOOP;
SELECT MAX(EYEAR) INTO MEYEAR FROM UDBDATES WHERE YEAR=MYRLOOP;
BEGIN
INSERT INTO BAV_BUILDER_NEW_DATA (YR_SIGNED,DIVCODE,SALESREGON,
BLDRN,COMPANY,SHORTNAME,DSIGNED,DCANCELED,ENGR_TEAM,
DMTERR,DMNAME,YR_SALES)
VALUES
(MYR_SIGNED,MDIVCODE,MSALESREGON,MBLDRN,MCOMPANY,MSHORTNAME,
MDSIGNED,MDCANCELED,MENGR_TEAM,MDMTERR,
MDMNAME,MYRLOOP);
END;
MYRLOOP := MYRLOOP + 1;
END LOOP;
COMMIT WORK;
END LOOP;
COMMIT WORK;
CLOSE CA;
END;
/
COMMIT WORK;

View 8 Replies View Related

Simulating A SQL Cursor Task In I.S. 2005

Nov 21, 2006

Hi EveryBody,

I have the following question :

What components of I.S. would I use, for simulating a cursor in SQL like that?

Example of simple cursor :

declare c_CURSOR cursor FOR
SELECT DISTINCT V.CODIGO FROM GB_RELACIONAL.DBO.VISITA V INNER JOIN MEDIDAS_LINEAL_PROMOCION_200604_02 M
ON M.CODIGO_VISITA=V.CODIGO AND M.TIEMPO_ENTRE_vISITAS <> V.TIEMPO_ENTRE_VISITAS


OPEN C_CURSOR
FETCH NEXT FROM C_CURSOR INTO @CODIGO
WHILE @@FETCH_STATUS=0

Do various Jobs; Doing Selects, Inserts etc.. using @codigo

FETCH NEXT FROM C_CURSOR INTO @CODIGO
end
CLOSE C_CURSOR
DEALLOCATE C_CURSOR



Then what I need is passing the @codigo in every loop, to other components in I.S., for doing inserts in other related tables.



Then my questions are:

a- What Component i would use for simulating the looping cursor like the example ?

b- How i Store and pass de @codigo to the other I.S. components for doing the selects, inserts?

c- What I.S. components and how would use, for receiving the @codigo ,and doing the corresponding Select , Inserts.. in the related tables , using that variable ( @codigo ) ?

I need an Idea of the I.S. components, configurations, and the flow for doing something like this...

Thank in advance!!




























View 4 Replies View Related

Mdac 2.8 And Sql 2005 Cursor Performace Issue

Jun 18, 2007

HelloI have a VB6 application using classic ado (MDAC 2.8) for connectingms sql 2000 server. Application uses a lot of server side cursors. NowI want to switch to ms sql 2005 server but I have noticed very seriousperformance problem. Sql profiler results of execution of followingcommands:declare @p1 intset @p1=180150131declare @p3 intset @p3=1declare @p4 intset @p4=16388declare @p5 intset @p5=22221exec sp_cursoropen @p1 output,N' Select ... from ... where .... orderby ...',@p3 output,@p4 output,@p5 outputselect @p1, @p3, @p4, @p5on sql server 2000:CPU: 234Reads:82515Writes:136Duration:296and on sql server 2005:CPU: 4703Reads:678751Writes:1Duration:4867Both databases are identical, the servers runs on the same machine(Pentium 2,8 Ghz, 2 GB RAM) with only one client connected. On forumsI've read that Microsoft doesn't recommend using server side cursorson sql 2005 but is there any way to increase performance to someacceptable level?thanks in advanceszymon strus

View 5 Replies View Related

Transact SQL :: STATIC Defines A Cursor That Makes Temporary Copy Of Data To Be Used By Cursor

Aug 12, 2015

In MSDN file I read about static cursor

STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications

It say's that modifications is not allowed in the static cursor. I have a  questions regarding that

Static Cursor
declare ll cursor global static
            for select  name, salary from ag
  open ll
             fetch from ll
 
              while @@FETCH_STATUS=0
               fetch from ll
                update ag set salary=200 where 1=1
 
   close ll
deallocate ll

In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says  modifications is not allowed in the static cursor.But I am able to update the data using static cursor.

View 3 Replies View Related

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance

Jul 20, 2005

Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

View 1 Replies View Related

Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.

Sep 20, 2007

I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.


Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.

here is the stored procedure:


Alter PROCEDURE [dbo].[sp_MSforeachsp]

@command1 nvarchar(2000)

, @replacechar nchar(1) = N'?'

, @command2 nvarchar(2000) = null

, @command3 nvarchar(2000) = null

, @whereand nvarchar(2000) = null

, @precommand nvarchar(2000) = null

, @postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

declare @retval int

if (@precommand is not null) EXECUTE(@precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @whereand)

select @retval = @@error

if (@retval = 0)

EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

if (@retval = 0 and @postcommand is not null)

EXECUTE(@postcommand)

RETURN @retval



GO


example useage:


EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

View 7 Replies View Related

Need Help Using Foreach Loop To Transfer Data From DB2/400 To SQLServer 2005

May 8, 2007

Hi, I am attempting to create SSIS packages to extract data from our AS/400 DB2 databases and populate tables for analysis and reporting in SQLServer. We have recently installed a new 2005 SQL Server to replace our existing 2000 databases. I have several DTS packages setup to connect to the 400s and copy the needed data to SQL2000 and these work great but do not transition to SSIS very well. I can get my SSIS packages to work if I create a seperate data flow task for each table but that does not seem appropriate for a tool like SSIS. I would think that what I am attempting is a very common thing to do. Maybe other enumerators could be used but I have not been able to get it to work.



I am trying to use a foreach loop, currently with a forech item enumerator, to first truncate the table on the SQL Server database using an Execute SQL task, and the to use a Data Flow task to refresh the data using an ole db source and destination. I have the truncate portion working, but when I try to setup the Ole db source using a variable it tells me I have no input columns. I have a variable from the forech loop that loops through the table names, this variable is used in a second variable which is set to EvaluateAsExpression with the expression set to the follwoing: "select * from " + @table_name



I have set delayvalidation to true and validateexternalmetadata to false. What do I do in order to get around this issue? Also, how should the old db destination be set so that it load the data into the table specified in the variable?



Thanks for any help you can provide.



Keith C

View 6 Replies View Related

CURSOR_STATUS Unreliable: SQL 2005 -- Is There A Better Way To Find Cursor Status?

May 27, 2008

What is the best way of reliably closing a cursor inside a BEGIN CATCH block? The simple problem statement is: I want to check if the cursor is open, then close it. I'm trying to use CURSOR_STATUS function and it seems to return a -3 (both when the cursor is open and not open.). Is this a bug or am I missing something?

I have removed all of my code and only provided what is necessary to repro the problem.

====================================================================================================

CREATE PROCEDURE dbo.repro_PROC

(

@condition1 varchar(10),

@condition2 varchar(10)

)

AS

BEGIN

BEGIN TRY


SET NOCOUNT ON;

SET XACT_ABORT ON;

DECLARE @dbName varchar(64), @dbid int;

DECLARE @FileID int;

DECLARE @Physical_Name varchar(512);

IF (@condition1 = 'ERROR')


RAISERROR ('ERROR: This error happens before any cursor is declared or is open', 16, 1);


DECLARE dbNameCursor CURSOR FOR SELECT Name, database_id FROM master.sys.databases ORDER BY Name;

OPEN dbNameCursor;


FETCH NEXT FROM dbNameCursor INTO @dbName, @dbid;

WHILE @@FETCH_STATUS = 0

BEGIN


DECLARE fileCursor CURSOR FOR SELECT File_ID, Physical_Name FROM master.sys.master_files

WHERE database_id = @dbid

ORDER BY File_ID;


OPEN fileCursor;

FETCH NEXT FROM fileCursor INTO @FileID, @Physical_Name;

WHILE @@FETCH_STATUS = 0

BEGIN


-- some condition which causes an exception

IF (@condition2 = 'ERROR')


RAISERROR ('ERROR: This error happens after both cursors are open', 16, 1);


FETCH NEXT FROM fileCursor INTO @FileID, @Physical_Name;

END

CLOSE fileCursor;

DEALLOCATE fileCursor;




FETCH NEXT FROM dbNameCursor INTO @dbName, @dbid;

END

CLOSE dbNameCursor;

DEALLOCATE dbNameCursor;

END TRY

BEGIN CATCH


DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

DECLARE @fileCursorStatus INT;

DECLARE @dbNameCursorStatus INT;


SELECT

@ErrorMessage = ERROR_MESSAGE(),

@ErrorSeverity = ERROR_SEVERITY(),

@ErrorState = ERROR_STATE();


SET @fileCursorStatus = CURSOR_STATUS('local', 'fileCursor');

SET @dbNameCursorStatus = CURSOR_STATUS('local', 'dbNameCursor');

PRINT 'Cursor_Status (fileCursor) = ' + convert(varchar, @fileCursorStatus);

PRINT 'Cursor_Status (dbNameCursor) = ' + convert(varchar, @dbNameCursorStatus);


IF (@fileCursorStatus >= 0)

BEGIN


PRINT 'Closing fileCursor';

CLOSE fileCursor;

DEALLOCATE fileCursor;

END


IF (@dbNameCursorStatus >= 0)

BEGIN


PRINT 'Closing dbNameCursor';

CLOSE dbNameCursor;

DEALLOCATE dbNameCursor;

END


-- info about the original error

RAISERROR (@ErrorMessage, -- Message text.

@ErrorSeverity, -- Severity.

@ErrorState -- State.

);


END CATCH;

END;

====================================================================================================

EXEC dbo.repro_PROC @condition1='ERROR', @condition2='NO-ERROR'


Output:

Cursor_Status (fileCursor) = -3

Cursor_Status (dbNameCursor) = -3

Msg 50000, Level 16, State 1, Procedure repro_PROC, Line 88

ERROR: This error happens before any cursor is declared or is open

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

EXEC dbo.repro_PROC @condition1='NO-ERROR', @condition2='ERROR'


Output:

Cursor_Status (fileCursor) = -3

Cursor_Status (dbNameCursor) = -3

Msg 50000, Level 16, State 1, Procedure repro_PROC, Line 89

ERROR: This error happens after both cursors are open

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

EXEC dbo.repro_PROC @condition1='NO-ERROR', @condition2='ERROR'


Output:

Cursor_Status (fileCursor) = -3

Cursor_Status (dbNameCursor) = -3

Msg 50000, Level 16, State 1, Procedure repro_PROC, Line 89

A cursor with the name 'dbNameCursor' already exists. <=== THIS IS A PROBLEM

=================================================================================================


If I try to close the cursor when the status = -3, then sometimes it closes successfully, some other times, it errors "Cursor NOT open" etc.

View 6 Replies View Related

SQL Server 2008 :: Difference Between FOR LOOP And FOREACH LOOP?

May 28, 2010

difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.

View 4 Replies View Related

Spin Loop Attaching Database In 2000 =&&> 2005 Upgrade

Apr 21, 2008

I just upgraded a large SQL 2000 database server to SQL 2005, and I have 2 databases that won't attach to the 2005 Server. They are both very small, about 90MB in size, and when I attach them to 2005, the process alternates between running and spinloop status. It can sit forever, and will never complete. I restored backups before the upgrade to a SQL 2000 Server, and reran DBCC CheckDB on both, and got no consistency errors, Updated Stats and indexes, then detached and moved the data files to 2005, and same thing.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved