How To Loop A Cursor And Accumulate A String Value ?

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

Cursor While Loop Problem.. Please Help
*** 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 Replies !

---

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

Cursor Count Loop - Update Table


View Replies !

---

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 !

---

Query That Accumulate Detail Values
How would be a query that read detailed sales from several dates and accumulated them in a table that has months as fields

MSSQL 2005

Table : DETAIL
Customer_Date___________Value
1________01-Jan-2008____1,234.00
2________01-Feb-2008____3,456.00
3________01-Mar-2008______500.00
4________01-Apr-2008____1,567.00
1________01-Apr-2008____2,123.00

Table : TOTAL
Year__Customer_Jan______Feb______Mar_____Apr ...
2008__1________1,234.00_0________0_______2,123.00
2008__2________0________3,456.00_0_______0
2008__3________0________0________500.00__0
2008__4________0________0________0_______1,567.00

Thanks for your help

JG

View Replies !

---

Concatenate A String Within A Loop From A Temp Table
I need help.

I have a large table that looks like this.

(ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10))

1, 1, p1
2, 1, p2
3, 2, p3
4, 2, p4
5, 3, p5
6, 3, p6
7, 4, p7
8, 5, p1
9, 5, p2
10,5, p83

i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.

can anyone help?

Emad

View Replies !

---

Declare Dynamic Cursor From String
Hi,is it possible to create a cursor from a dynamic string?Like:DECLARE @cursor nvarchar(1000)SET @cursor = N'SELECT product.product_idFROM product WHERE fund_amt > 0'DECLARE ic_uv_cursor CURSOR FOR @cursorinstead of using this--SELECT product.product_id--FROM product WHERE fund_amt > 0 -- AND mpc_product.status= 'aktiv'Havn't found anything in the net...Thanks,Peppi

View Replies !

---

Using Stuff To Return A String Of Values From A Column Without A Cursor
I think it was Pat Phelan who posted a little trick here where he used the STUFF function to create a string fo values from a column without using a cursor.

I am starting a brand new project and I did my table design and I am awaiting a finalized requirements document to start coding and I thought I would spend a little time writing some code to autogenerate some generic one record at a time SELECT, INSERT,UPDATE and DELETE stored procedures. With the coming holiday things are getting quiet around here.

The code that is not working is listed below. It does not work. It returns Null. I suck.

DECLARE @column_names varchar(8000)

SET @column_names = ''

SELECT @column_names = STUFF(@column_names,LEN(@column_names),0,C.COLUMN_ NAME + ', ')
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = 'MyTable'

SELECT @column_names

little help?

View Replies !

---

Inserting Parsed String Using Cursor,want To Validate Line Before Insert
Hi ,
We loading file using DTS into ''working" table with 1 column then open cursor and
parse each line into 50 columns and insert each line into
table CustomerOrders

We start to get Files with invalid lines.

Insert of line cause error
---
Server: Msg 8115, Level 16, State 8, Line 202
Arithmetic overflow error converting numeric to data type numeric.
---


Since code inside procedure ,failure returned by procedure .

I want to verify column in cursor against column in table CustomerOrders and if any column in cursor invalid insert row as text in log table and continue


Is possible to do it without testing each column (have 50 columns!!!) against every possible scenario

Thanks
Alex

View Replies !

---

‘default To Local Cursor’ Causes Errors In String-built Declares
Hi,

I would like to use the dboption ‘default to local cursor’ to true.

But when I set it to true for my database, cursors that are built in a ‘string’ and then executed return an error :

Select @SQL = 'Declare SICCursor cursor For Select SIC1 From ' + @StateTable + ' Where BusinessName = ' + '''' + @BusinessName + '''' + ' Order By PubDate desc'

Exec(@SQL)

Open SICCursor

Openning the cursor returns the error message

“A cursor with the name 'SICCursor' does not exist.”

What am I doing wrong (cursors declared ‘normally’ do not have a problem)?

Thanks,
Judith

View Replies !

---

How Can I Pass A String Parameter More Than 4000 Characters Into Execute() And Return Result For FETCH And Cursor?
Dear All
 
I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor.
 
Here is my query sample for yours to understand.
 
 

SET NOCOUNT ON

DECLARE @ITEMCODE int, @ITEMNAME nvarchar(50), @message varchar(80), @qstring varchar(8000)

Set @qstring = 'select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union 
select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm'

PRINT '-------- ITEM Products Report --------'

DECLARE ITEM_cursor CURSOR FOR

execute (@qstring)

OPEN ITEM_cursor

FETCH NEXT FROM ITEM_cursor

INTO @ITEMCODE

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT ' '

SELECT @message = '----- Products From ITEM: ' +

@ITEMNAME

PRINT @message

-- Get the next ITEM.

FETCH NEXT FROM ITEM_cursor

INTO @ITEMcode

END

CLOSE ITEM_cursor

DEALLOCATE ITEM_cursor

 
Why i use @qstring? It is because the query will be changed by different critiera.
 
Regards
Edmund

View Replies !

---

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 !

---

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

View Replies !

---

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

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 !

---

Employing XML Formatted String Data Rather Than Normal String(char(), Nchar() Or Varchar() Values
Hello,
Is there a way of passing in an xml formatted string or text to the report through a data set and have the textbox or table in which it displays keep the formatting specified in the xml string rather than in the textbox properties?
 
Thanks.

View Replies !

---

String Or Binary Data Would Be Truncated. (only For 1700 Character String?)
I am trying to insert a row into a table of Microsoft SQL Server 2000.

There are various columns.















[SNO] [numeric](3, 0) NOT NULL ,
[DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,

The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-



StaleConnecti A CONM7007I: Mapping the following
SQLException, with ErrorCode 0 and SQLState 08S01, to a
StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC]Connection reset

      at
com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)


Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?

Can anyone please tell me what's wrong?

View Replies !

---

Adding String To Database, But Name Of String Is Added, Not Data
Hello, I am tring to add a string my database.  Info is added, but it is the name of the string, not the data contained within.  What am I doing wrong?  The text "Company" and "currentUserID" is showing up in my database, but I need the info contained within the string.  All help is appreciated!
 
 
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClientPartial Class _DefaultInherits System.Web.UI.Page
 
Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'First Command DataDim Company As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Company"), TextBox)).Text)
Dim insertSQL1 As StringDim currentUserID As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox)).Text)
insertSQL1 = "INSERT INTO Company (CompanyName, UserID) VALUES ('Company', 'currentUserID')"Dim cmd1 As New SqlCommand(insertSQL1, con)
'2nd Command Data
Dim selectSQL As String
selectSQL = "SELECT companyKey FROM Company WHERE UserID = 'currentUserID'"Dim cmd2 As New SqlCommand(selectSQL, con)
Dim reader As SqlDataReader
'3rd Command Data
Dim insertSQL2 As String
insertSQL2 = "INSERT INTO Company_Membership (CompanyKey, UserID) VALUES ('CompanyKey', 'currentUserID')"Dim cmd3 As New SqlCommand(insertSQL2, con)
'First CommandDim added As Integer = 0
Try
con.Open()
added = cmd1.ExecuteNonQuery()
lblResults.Text = added.ToString() & " records inserted."Catch err As Exception
lblResults.Text = "Error inserting record."
lblResults.Text &= err.Message
Finally
con.Close()
End Try
'2nd Command
Try
con.Open()
reader = cmd2.ExecuteReader()Do While reader.Read()
Dim CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
lbl1Results.Text = "Error selecting record."
lbl1Results.Text &= err.Message
Finally
con.Close()
End Try
'3rd Command
Try
con.Open()
added = cmd3.ExecuteNonQuery()
lbl2Results.Text = added.ToString() & " records inserted."Catch err As Exception
lbl2Results.Text = "Error inserting record."
lbl2Results.Text &= err.Message
Finally
con.Close()End Try
 
 
 End Sub
End Class

View Replies !

---

Help: About Ms Sql Query, How Can I Check If A Part String Exists In A String?
Hello to all,
I have a problem with ms sql query. I hope that somebody can help me. 
i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)).  Example Datas for Table Relationships:                               IDMember     Relationships              .
                                                                                                                3387            (2345, 2388,4567,....)
                                                                                                                4567           (8990, 7865, 3387...)
i wirte a query to check if there is Relationship between two members.
Query: 
Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567;
select *
from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in
(select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
 
But I get nothing by this query.
Can Someone tell me where is the problem? Thanks
 
Best Regards
Pinsha

View Replies !

---

Capitalize A Text String/String Function Related
Hi Folks:

How can I capitalize a string like 'JOHN DOE' into
'John Doe' with one SQL statement. SQL does provide
string function like LOWER(char_expr) which would
change the whole text string to 'john doe' and I don't
want that.
So far, in order to do that, I have to use a front-end
development language like 'Omnis' which has a
string function 'cap()' to capitalize the whole string,
then update the back-end SQL with the new string.
Thank you in advance for your time and advice.

David Nguyen

View Replies !

---

Need Help With String Manipulation - Splitting 1 String Into Multiple Columns
Hello All,

I'm a non-programmer and an SQL newbie.  I'm trying to create a printer usage report using LogParser and SQL database.  I managed to export data from the print server's event log into a table in an SQL2005 database. 

There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need).  My problem is I need to split the String column which is a varchar(255) delimited by | (pipe).  Example:

2|Microsoft Word - ราย�ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1

The first value is the job number, which I don't need.  The second value is the printed document name.  The third value is the owner of the printed document.  The fourth value is the printer name.  The fifth value is the printer port, which I don't need.  The sixth value is the size in bytes of the printed document, which I don't need.  The seventh value is the number of page(s) printed.

How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table?

In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char).  But CHARINDEX() in T-SQL only starts from the beginning of the string, right?  I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND(). 

Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion.

Thanks a bunch in advance,

Chutikorn

 

View Replies !

---

Input String -&> Table -&> Output String?
I have a nasty situation in SQL Server 7.0. I have a table, in whichone column contains a string-delimited list of IDs pointing to anothertable, called "Ratings" (Ratings is small, containing less than tenvalues, but is subject to change.) For example:[ratingID/descr]1/Bronze2/Silver3/Gold4/PlatinumWhen I record rows in my table, they look something like this:[uniqueid/ratingIDs/etc...]1/2, 4/...2/null/...3/1, 2, 3/...My dilemma is that I can't efficiently read rows in my table, match thestring of ratingIDs with the values in the Ratings table, and returnthat in a reasonable fashion to my jsp. My current stored proceduredoes the following:1) Query my table with the specified criteria, returning ratingIDs as acolumn2) Split the tokens in ratingIDs into a table3) Join this small table with the Ratings table4) Use a CURSOR to iterate through the rows and append it to a string5) Return the string.My query then returns...1/"Silver, Platinum"2/""3/"Bronze, Silver, Gold"And is easy to output.This is super SLOW! Queries on ~100 rows that took <1 sec now take 12secs. Should I:a) Create a junction table to store the IDs initially (I didn't thinkthis would be necessary because the Ratings table has so few values)b) Create a stored procedure that does a "SELECT * FROM Ratings," putthe ratings in a hashtable/map, and match the values up in Java, sinceJava is better for string manipulation?c) Search for alternate SQL syntax, although I don't believe there isanything useful for this problem pre-SQL Server 2005.Thanks!Adam

View Replies !

---

Find In String And Return Part Of String
I am trying to find a way to find a certian character in a string and then select everything after that character.

for example i would look for the position of the underscore and then need to return everthing after it so in this case

yes_no

i would return no

View Replies !

---

Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.
We have the following two tables :

Link  ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )

The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.

The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).

We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.

For instance,

Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.

Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.

View Replies !

---

Converting String To Unicode String In T-SQL
Hi,We have stored proc name proc_test(str nvarchar(30)). So far this prochas been invoked from a .NET application assuming that only Englishcharacter strings will be passed to it. The calls are likeproc_test('XYZ')We now have a requirement for passing Chinese strings as well. Ratherthan changing the calls throughout the application, we would like tohandle it in the stored procedure so that it treats the string as aunicode string. Can we apply some function to the parameter to convertit to unicode so that we don't have to call with an N prefixed to thestring?Thanks,Yash

View Replies !

---

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 !

---