Cursor While Loop Problem.. Please Help

Loop Though Table Using RowID, Not Cursor (was &"Loop&")
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 Replies !   View Related

---

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

View Replies !   View Related

---

Loop / Cursor Help
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 Replies !   View Related

---

Cursor Loop
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 Replies !   View Related

---

Loop Without Cursor
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 Replies !   View Related

---

Sql Cursor Or App Loop?
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 Replies !   View Related

---

Cursor Loop Is Broken
In a stored procedure (SP1) I am looping through a cursor with recordsfrom Table1. Each record in the cursor is inserted into Table2.Insert trigger on Table2 is inserting the record into Table3 (inanother DB).In the insert trigger on Table3, a series on checks are done on theinserted record and in case of an error, an email is sent and thetrigger returns.This break the cursorloop in SP1 and the rest of the records in thecursor is not treated.How do I make sure that all records are treated?This is the flow:-- SP1 ---------------------------------DECLARE csrListe CURSOR FOR SELECT felt1 FROM Table1OPEN csrListe-- The first record is treated here....:-- Treat the restWHILE @@FETCH_STATUS = 0 BEGINFETCH NEXT FROM csrListe INTO @feltetIF @@FETCH_STATUS = 0 BEGINblah-blah-blahINSERT INTO Table2 (Ordrenr, Status, Dato, Resultat) VALUES(@Ordrenr, @Status, @Dato, @Result)ENDENDCLOSE csrListeDEALLOCATE csrListe-- Table2_ITrig ---------------------------------INSERT INTO db2.dbo.Table3 SELECT * FROM inserted-- Table3_ITrig ---------------------------------SET NOCOUNT ONDECLARE @STATUS intDECLARE @DATOTID smalldatetimeDECLARE @RESULT intSELECT @ORDRENR = (SELECT ORDRENR FROM INSERTED)SELECT @STATUS = (SELECT STATUS FROM INSERTED)SELECT @DATOTID = (SELECT DATO FROM INSERTED)SELECT @RESULT = (SELECT RESULT FROM INSERTED)SET XACT_ABORT ONIF NOT @STATUS IN (1,2,3,4,5,6,9,10) BEGINSELECT @ERR = 'ERROR - unknown status = ' + CAST(@ORDRENR as char(4))UPDATE Table3 SET RESULTAT=2 WHERE ORDRENUMMER=@ORDRENREXEC @rc = master.dbo.xp_smtp_sendmail@FROM = N...@here.dk',@TO = N...@here.dk',@priority = N'HIGH',@subject = N'Status error',@message = N'Status error',@type = N'text/plain',@server = 'smtp.here.dk'RETURNENDThe mail is send so it must be the final RETURN that is causing thetrouble.Any suggestions?

View Replies !   View Related

---

Cursor Versus While Loop
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 Replies !   View Related

---

Loop Inside Of A Cursor
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 Replies !   View Related

---

Loop Still Fails In Cursor
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 Replies !   View Related

---

Infinite Loop In Cursor
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 Replies !   View Related

---

Cursor, Loop Or Case?
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 Replies !   View Related

---

SQL 2005- Cursor Loop
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 Replies !   View Related

---

Calling A SP Inside A Cursor Loop..
I have SP, which has a cursor iterations. Need to call another SP forevery loop iteration of the cursor. The pseudo code is as follows..Create proc1 asBeginVariable declrations...declare EffectiveDate_Cursor cursor forselect field1,fld2 from tab1,tab2 where tab1.effectivedate<Getdate()---/////Assuming the above query would result in 3 recordsOpen EffectiveDate_CursorFetch next From EffectiveDate_Cursor Into @FLD1,@FLD2begin/*Calling my second stored proc with fld1 as a In parameterand Op1 and OP2 Out parameters*/Exec sp_minCheck @fld1, @OP1 output,@OP2 outputDo something based on Op1 and Op2.endWhile @@Fetch_Status = 0Fetch next From EffectiveDate_Cursor Into @FLD1,@FLD2/* Assume If loop count is 3.and If the Fetch stmt is below the begin Stmt, the loop iterations are4 else the loop iterations are 2*/begin/*Calling my second stored proc with fld1 as a In parameter and Op1and OP2 Out parameters*/Exec sp_minCheck @fld1, @OP1 output,@OP2 outputDo something based on Op1 and Op2.endThe problem I had been facing is that, the when a stored proc is calledwithin the loop, the proc is getting into infinite loops.Any Help would be appreciated.Satish

View Replies !   View Related

---

Combining The Results Of A Cursor Loop
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 Replies !   View Related

---

Need Help Programming SQL To Run Cursor, Compare, And Loop
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 Replies !   View Related

---

How To Loop A Cursor And Accumulate A String Value ?
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 Replies !   View Related

---

Cursor Causing Infinite Loop
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 Replies !   View Related

---

Cursor Count Loop - Update Table


View Replies !   View Related

---

Cursor Count Loop - Update Table
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 Replies !   View Related

---

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
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 Replies !   View Related

---

Variable Type For Fetcing The Cursor Record In Tsql Cursor
what is the equivalent of the %rowtype in oracle for the tsql

View Replies !   View Related

---

Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
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 Replies !   View Related

---

Foreach Loop Doesn't Loop
I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.

I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.

Any solution to this? anyone experienced anything similar

View Replies !   View Related

---

Fishing For A Clue. To Loop Or Not To Loop
I have a table called Tbltimes in an access database that consists of the following fields:

empnum, empname, Tin, Tout, Thrs

what I would like to do is populate a grid view the a select statement that does the following.

display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.

Tbltimes

|empnum | empname | Tin        | Tout      | Thrs | 

| 1            | john          | 2:00PM | 3:00PM |1hr   |  

| 1            | john          | 2:00PM | 3:00PM | 1hr | 

| 2            | joe            | 1:00PM | 6:00PM | 5hr |

GridView1

|  1   | John  |  2hrs |

|  2   | Joe    | 5hrs  |

im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.

View Replies !   View Related

---

Join Cursor With Table Outside Of Cursor
part 1

Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)

DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]

OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME


WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME

END

CLOSE DBCur
DEALLOCATE DBCur


Part 2

SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS


my question is,
how to join the part 1 n part 2?
is there posibility?

View Replies !   View Related

---

ForEach Loop Or For Loop??
I have source and destination table names in the database(one table) and I need to read the source and destination tables one by one...

My Lookp table is like the following...

Srn srctable desttable

1       SRC1    DEST1

2    SRC2    DEST2

3   SRC3    DEST3

Now I want one package to load from source to destination.. how do I do it.. I dont know how to use....

How do I run the pacakge for each of the rows... ..............................

View Replies !   View Related

---

Cursor Inside A Cursor
I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors




declare Q cursor for
select systudentid from satrans


declare @id int

open Q
fetch next from Q into @id
while @@fetch_status = 0
begin

declare c cursor for

Select
b.ssn,
SaTrans.SyStudentID,
satrans.date,
satrans.type,
SaTrans.SyCampusID,
Amount = Case SaTrans.Type
When 'P' Then SaTrans.Amount * -1
When 'C' Then SaTrans.Amount * -1
Else SaTrans.Amount END

From SaTrans , systudent b where satrans.systudentid = b.systudentid

and satrans.systudentid = @id




declare @arbalance money, @type varchar, @ssn varchar, @amount money, @systudentid int, @transdate datetime, @sycampusid int, @before money

set @arbalance = 0
open c
fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount

while @@fetch_status = 0
begin

set @arbalance = @arbalance + @amount
set @before = @arbalance -@amount

insert c2000_utility1..tempbalhistory1
select @systudentid systudentid, @sycampusid sycampusid, @transdate transdate, @amount amount, @type type, @arbalance Arbalance, @before BeforeBalance
where( convert (int,@amount) <= -50
or @amount * -1 > @before * .02)
and @type = 'P'




fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount
end
close c
deallocate c
fetch next from Q into @id

end
close Q
deallocate Q


select * from c2000_utility1..tempbalhistory1
truncate table c2000_utility1..tempbalhistory1

View Replies !   View Related

---

Client Side Cursor Vs Sever Side Cursor?
I having a difficult time here trying to figure out what to do here.I need a way to scroll through a recordset and display the resultswith both forward and backward movement on a web page(PHP usingADO/COM)..I know that if I use a client side cursor all the records get shovedto the client everytime that stored procedure is executed..if thisdatabase grows big wont that be an issue?..I know that I can set up a server side cursor that will only send therecord I need to the front end but..Ive been reading around and a lot of people have been saying never touse a server side cursor because of peformance issues.So i guess im weighing network performance needs with the client sidecursor vs server performance with the server side cursor..I am reallyconfused..which one should I use?-Jim

View Replies !   View Related

---

More Of This While Loop
Hello everyone...... I'm trying to do the following but am having issues:WHILE (SELECT ProductId FROM _ShoppingCart WHERE CartId = @CartId) IS NOT NULLBEGIN        execute code with item......... erase itemEND      In the while loop I want to execute code from each item in my _ShoppingCart and then erase them until there are no more items. However the above code gives me the error: "Subquery returned more than 1 value. This is not permitted........" It works fine when there is only one item. Does any one know what format to use when dealing with more that one entry?  Thank you, Alec 

View Replies !   View Related

---

While Loop
Hello everyone,I've got this While loop here which is giving me a problem:WHILE (SELECT ProductId FROM _ShoppingCart WHERE CartId = @CartId) IS NOT NULLBEGIN            DECLARE @ProdId int, @ProdSize varchar, @ProdQuan int            SELECT @ProdId = ProductId, @ProdSize = ProductSize, @ProdQuan = Quantity FROM _ShoppingCart WHERE CartId = @CartId                                   If @ProdSize = 'XL'            BEGIN                UPDATE                    _ProductBatches                SET                    XL = '0'                WHERE                    ProductId = @ProdId            END            DELETE FROM _ShoppingCart WHERE ProductId = @ProdId AND CartId = @CartIdEND The problem is that the IF statement isn't being executed. And I know for a fact that 'XL' is ProductSize in my _ShoppingCart database. Whats even stranger is that my delete statement is being executed. So @ProdId is Being set correctly, but when it gets to the IF @ProdSize = 'XL' it doesn't get executed for some reason. If @ProdId is being recognized correctly in my DELETE statement, why is my @ProdSize not being reconized correctly in my IF statement. I checked my _ShoppingCart database, and my ProductSize is definitely 'XL'. Can any one see what could be going on here.  Thank you, Alec 

View Replies !   View Related

---

For Loop
Dear All.
 Have a nice day.
I have db table, I need to update all fields of table.
Please can you write code," for loop " how can update all fields of my table by loop.
 Thanks.
Zahyea.

View Replies !   View Related

---

How Do You LOOP In T-SQL?
I used to loop through recordsets with ease in old classic .asp pages.
Please Help me understand how Professionals now loop through and update tables using JUST SQL Query Analyzer using my pseudo-code provided below.
I would love to learn how to do it to better develop my skills.
 SELECT * FROM zz_2007_Booth_Visitors
WHERE COALESCE ([Product Interest - Other Actuator],
[Product Interest - Chillers],
[Product Interest - Other Chiller],
[Product Interest - Electronic Products],
[Product Interest - Other network interfaces],
[Product Interest - Fittings],
[Product Interest - High Vacuum],
[Product Interest - Other high vacuum actuators],
[Product Interest - Pick& Place and Transfer],
[Product Interest - Teflon Products],
[Product Interest - Training],
[Product Interest - Valves& Manifolds],
[Product Interest - Actuators]) Is Not Null
Order BY [Contact Name]

IF [Product Interest - Actuators] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Act_Phuematic = 1 Where [Contact Name] = [Contact Name]

IF [Product Interest - Other Actuator] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Act_Electric = 1 Where [Contact Name] = [Contact Name]

IF [Product Interest - Other Chillers] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Chiller = 1 Where [Contact Name] = [Contact Name]
 
 

View Replies !   View Related

---

How To Add A Do Loop In Sql? Thanks A Lot!
What I am trying to do is to get balances at each month-end from Jan toDec 2004. Now I am doing it by manually changing the date for eachmonth, but I want to do all the months at one time. Is there a way toadd something like a do loop to achieve that goal? Please see my querybelow. Thanks so much!declare @month_date_b smalldatetime--B month beginning datedeclare @month_date_e smalldatetime--E month ending dateselect @month_date_b='9/1/2004'select @month_date_e='9/30/2004'select a.person_id, a.fn_accno, a.fn_bal, b.mm_openfrom fn_mm_fnbal as ajoin fn_mm_list as bon a.person_id=b.person_idand b.mm_open < @month_date_ewhere a.bal_date between @month_date_b and @month_date_egroup by a.person_id, a.fn_accno, a.fn_bal, b.mm_openorder by a.fn_accno, a.fn_bal

View Replies !   View Related

---

For Loop
HeaderLoop: for forHeader as curHeader dynamic scroll cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 do
set AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(AcctNum);
set Cntr=Cntr+1
end for;


The above is the sybase version of a 'for loop' . The query
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 results in 1000 results.
How do I change that in SQL?? Do we have a for loop ??
I checked in BOL but it is confusing with "browse" etc n some other options.

can I write like this?


for { Browse { declare curHeader dynamic cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 }
set @AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(@AcctNum);
set @Cntr=@Cntr+1
}


I duno its just my guess, can any one help me out. @Cntr and @Acctnum are declared
in the beginnning.



tks

View Replies !   View Related

---

For Next Loop
hi,
I am trying to find a way of using a loop that won't be an endless loop because I have to insert parts of a string until the string reaches the end. I am unable to make the loop get to a point where the statement is false.

Is there anyway I can find out the length of the string so that I can tell the statement to loop while the statement is true?

Help!!!!!!!!!!!!1

View Replies !   View Related

---

How To Loop Thru Db
Hi there,

I am jsut curious if there is a way to use loop on microsoft sql.

I have a table that has
intid (integer)
strtitle (varchar)
intparent (integer)

I want to select all entries that on my third column returns all the parents of the current page (parent of the parent of the parent, u get my idea).

my query should return result as follow:

| intid | strtitle | intparents |
==========================
140 | test | 90, 54, 3, 1
54 | test2 | 3, 1
3 | test3 | 1

intparents uses a ", " separator

I hope i do make sense here.
Any help will be appriciated.

Thanks in advance

View Replies !   View Related

---

Using While Loop
I am trying to create a simple loop that simply goes through the loop incrementing minLoanRecordID by one each time. I am receiving 4 Error messages when trying to run they are:
-Line 2: Incorrect syntax near the keyword 'BEGIN'.
-Line 4: 'INTEGER' is not a recognized CURSOR option.
-Line 5: 'INTEGER' is not a recognized CURSOR option.
-Line 15: Incorrect syntax near '='.

Any Help would be greatly appreciated.




Code:



CREATE PROCEDURE LOOPING
BEGIN

DECLARE maxLoanRecordID INTEGER DEFAULT = 999;
DECLARE minLoanRecordID INTEGER DEFAULT = 0;

WHILE (minLoanRecordID) < (maxLoanRecordID)

SELECT * FROM DU_Requestfile
WHERE RequestID != (SELECT MAX(RequestID) FROM DU_Requestfile
WHERE loanRecordID = minLoanRecordID)
AND LoanRecordID = minLoanRecordID
ORDER BY RequestID

SET minLoanRecordID = minLoanRecordID + 1

END WHILE;

END

View Replies !   View Related

---

For Loop
Hello,

Please, could someone explain me how  "For Loop" funtions?

because I need to load data depending on a variable?

Can I use For loop to do that?

thanks

View Replies !   View Related

---

For Each Loop
Hi,

I have a dataset created in a data flow task. I want use a for each loop in the control flow area of the package to process every row individually in the dataset. How can I do this?

I am currently using a "Recordset Destination" because it says that it creates and populates in memory ADO dataset and I read somewhere that this could be used anywhere in the package. I have even assigned a variable DS which has an object datatype to the Recordset Destination task. How do I use this now in the for each loop container in the Control Flow task? I also need to know if I can use the DataReader Destination task instead as it uses ADO.NET vs just ADO for performance reasons?

Please help me with the above problem as I am working on a demo.

Thank you!!

View Replies !   View Related

---

Loop Help
Hi, I'm trying to figure out a way to do this in one step with a loop or something in SSIS.  I was trying to use a sql task in a foreach loop but can't figure out how to loop through each column in a row and then insert.
 

INSERT INTO Table1(Id,Sequence_Number,Date,TextCol)

SELECT a.Id,a.Sequence_Number,getdate(),'Changed first name value from ' + a.First_Name + ' to new value ' + b.First_Name

FROM Table1 a

JOIN Table2 b

ON a.Id = b.Id

WHERE a.First_Name <> b.First_Name and a.Rec_Type=10
 

GO



INSERT INTO Table1(Id,Sequence_Number,Date,TextCol)

SELECT a.Id,a.Sequence_Number,getdate(),'Changed prefix value from ' + a.Prefix + ' to new value ' + b.Prefix

FROM Table1 a

JOIN Table2 b

ON a.Id = b.Id

WHERE a.Prefix <> b.Prefix and a.Rec_Type=10

View Replies !   View Related

---

FOR EACH LOOP
 

HI FRIENDS
 
WHAT IS THE USE OF FOREACH LOOP AND GIVE ME THE ONE EXAMPLE
 
REGARDS
KOTI
 

View Replies !   View Related

---

For Loop
I have a table which has an identity column. I want to traverse through the table one row at a time using FOR Loop. Can someone help me with the syntax.

View Replies !   View Related

---

Get Max And Min In A Set Using While Loop
Hi Experts
 
Can anyone please guide me in getting the max and minimum rows in a set while looping through the set. I know there is a way to use a while loop and preserve the value in a variable to get the max and minimum rows. Here is what I have:
 



Code Block
CREATE TABLE TstDbinfo (DBNAME VARCHAR(60),DBSize INT,Sqno INT)
 
Go
 
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('ADB1',2400,1)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('ADB2',2200,2)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('ADB3',2000,3)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('ADB4',1400,4)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('ADB5',1300,5)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('DDB1',1200,6)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('DDB2',900,7)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('DDB3',200,8)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('DDB4',200,9)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('DDB5',150,10)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('DDB6',10,11)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('DDB7',5,12)
 
 
What I am looking is for a solution like this:
 



Code Block
DBName DBSize Sqno
ADB1 2400 1
DDB7 5 12
ADB2 2200 2
DDB6 10 11
ADB3 2000 3
DDB5 150 10
ADB4 1400 4
DDB4 200 9
DDB3 200 8
ADB5 1300 5
DDB2 900 7
DDB1 1200 6
 
 







As you can see I need to get one Max and one Minimum record. I want to avoid the use of cursors but was wondering if this can be easily done using a while loop and looping through it to get the next max and min values. If I have two dbs of the same size then both of them should be displayed. Sqno is unique in the table.
 
Any Solutions are appreciated.
 
Thank you very much
 
Ankith

View Replies !   View Related

---

Loop Or Do While?
 I am totally missing something here and feel ridiculous but I need help.
I have a DTS package that I plan to run once a month. The first task is as follows:

----Delete the records out of the table Reminder
BEGIN
DELETE FROM Reminder
END
----Populate the table based on the following criteria
BEGIN
INSERT INTO Reminder (record_id, form_type, act_comp_date, resp_party, resp_party_email, subject)
SELECT record_id, form_type, act_comp_date, resp_party, resp_party_email, subject
FROM form_data
WHERE (act_comp_date IS NULL) AND (form_type = 'Preventive Action') OR
(act_comp_date IS NULL) AND (form_type = 'Corrective Action')
END


Now what I want to do is send an email message to each resp_party_email record, there is usually about 5. The problem is only the last record is getting an email. Am I missing some kind of loop command here to make each record get the email?  I'm guessing I need some type of loop or do while in there?

----This is what I am trying but only the last record is getting the email
DECLARE
@recordid varchar(10),
@formtype varchar(50),
@respparty varchar(75),
@resppartyemail varchar(75),
@subject varchar(50),
@mailSTR varchar(800)

BEGIN
Select @recordid=reminder.record_id,
@formtype=reminder.form_type,
@respparty=reminder.resp_party,
@resppartyemail=reminder.resp_party_email,
@subject=reminder.subject
from reminder
END

BEGIN
SELECT @mailSTR = 'd:postiepostie -hostmtp.company.com -to:' + @resppartyemail + ' -s:"Open Action Update Reminder" -fromysadmin@company.com -msg:"
' + @respparty +'

Just a reminder to submit an updated Work History for your Open ' + @formtype + ', record # ' + @recordid + ' - ' + @subject + ' - at least every 30 days.

Thank you, Quality Management" '
BEGIN
EXEC master..xp_cmdshell @mailSTR
END
END



Any eye opening fixes would be great!
Stacey

View Replies !   View Related

---

Loop
Hi

I want to loop this command with a pause. It must run every 30 min to refresh my query. Can anyone assist me.



select * from BULKPICKXLOC



Thanks

View Replies !   View Related

---

SQL Loop
hi i need to do a loop inside a stored procedure, just not sure how to go about it!

this is what i need



select count(TI_ID) AS REC_COUNT
FROM TRANSIT...T_TBL_TRANSIT
WHERE DATEDIFF(m, TI_Date, getdate())>12

CASE WHEN REC_COUNT > 1 THEN

===LOOP THROUGH AND DELETE 1000 AT A TIME===

DELETE top 1000 FROM TRANSIT...T_TBL_TRANSIT
WHERE DATEDIFF(m, TI_Date, getdate())>12

END CASE

View Replies !   View Related

---

Sql Loop
Hi i have a sql loop query which i have working in asp fine, i have altered it to try and get it working as a stored procedure.

but i am not sure what the syntax is.

can someone help please.

many thanks



DECLARE
CURSOR GetWebOrder_cur IS
SELECT O_R_ID, O_Name, O_Add_1, O_DB_Code, O_Add_2, O_Add_3, O_Add_4, O_Add_5, O_Add_6, O_PostCode, O_CCode, O_Service, O_Instore, O_STC_Code, O_ID
FROM [newserver].dbo.X_TBL_ORDER
WHERE NewOrder = 0

BEGIN
FOR GetWebOrder_rec IN GetWebOrder_cur LOOP

-- SET ALL FIELDS
set R_ID34 = GetWebOrder_cur("O_R_ID")
set R_Name = GetWebOrder_cur("O_Name")
set R_Contact = GetWebOrder_cur("O_Add_1")
set R_Code = GetWebOrder_cur("O_DB_Code")
set R_Add_1 = GetWebOrder_cur("O_Add_2")
set R_Add_2 = GetWebOrder_cur("O_Add_3")
set R_Add_3 = GetWebOrder_cur("O_Add_4")
set R_Add_4 = GetWebOrder_cur("O_Add_5")
set R_Add_5 = GetWebOrder_cur("O_Add_6")
set R_Add_6 = GetWebOrder_cur("O_Add_6")
set R_PostCode = GetWebOrder_cur("O_PostCode")
set R_CostCode = GetWebOrder_cur("O_CCode")
set R_Delivery = GetWebOrder_cur("O_Service")
set R_Instore = GetWebOrder_cur("O_Instore")
set R_STCODES = GetWebOrder_cur("O_STC_Code")
set WebOrderID = GetWebOrder_cur("O_ID")

-- GET MAX ID
SELECT Max(O_ID) AS MAXOID FROM dbo.X_TBL_ORDER

-- INSERT ORDER INTO F4 ORDERS
INSERT INTO dbo.X_TBL_ORDER
(O_Date, O_R_ID, O_Name, O_DB_Code, O_Add_1, O_Add_2, O_Add_3, O_Add_4, O_Add_5, O_Add_6, O_PostCode, O_CCode, O_Service, O_O_By, O_Instore, O_STC_Code)
VALUES
(getdate(), R_ID34, R_Name, R_Code, R_Contact, R_Add_1, R_Add_2, R_Add_3, R_Add_4, R_Add_5, R_PostCode, R_CostCode, R_Delivery, R_Contact, R_Instore, R_STCODES)

-- UPDATE VIRTUAL SERVER SET NewOrder = 1
UPDATE [newserver].dbo.X_TBL_ORDER
SET NewOrder = 1
WHERE O_ID = WebOrderID

-- SET CURSOR FOR ORDERLINES
CURSOR orderlines_cur IS
SELECT *
FROM [newserver].dbo.X_TBL_ORDER_LINE
WHERE OL_O_ID = @WebOrderID
-- OPEN LOOP THROUGH ORDERLINES
FOR orderlines_rec in orderlines_cur LOOP

-- SET ORDERLINE FIELDS
set B_St_Code = orderlines_cur("OL_St_Code")
set B_Description = orderlines_cur("OL_Desc")
set B_Qty = orderlines_cur("OL_Qty")
set B_dbcode = orderlines_cur("OL_DB_Code")

-- INSERT INTO F4 ORDERLINES
INSERT INTO dbo.X_TBL_ORDER_LINE
(OL_O_ID, OL_St_Code, OL_Desc, OL_Qty, OL_Allocated, OL_Despatch, OL_DB_Code)
VALUES
(B_preorderID, B_St_Code, B_Description, B_Qty, B_Qty, B_Qty, B_dbcode)
-- CLOSE LOOP THROUGH ORDERLINES
END LOOP;

END LOOP;
END;

View Replies !   View Related

---