x

Stored Procedure,Not returning any records, query running into a Loop

I am requesting help on the following Stored Procedure, which does not return any records for me. If i run the query independently it gives me the result, meaning rows . However, the SP , seems to be running into a cursor ,can someone please advise on the code below: Thanks in advance and let me know if more information is required.

 USE [pduort]
 GO
 --Object: StoredProcedure [dbo].[Pareto_bucket1] Script Date: 02/21/2013 14:45:06
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER OFF
 GO
 ALTER PROCEDURE [dbo].[Pareto_bucket1] @cbs_prm varchar(20)
 AS
 declare @CBS varchar(20),
 @Bucket nvarchar(200),
 @SEARCHPHRASE nvarchar(200),
 @SEARCHPHRASE1 nvarchar(200),
 @SEARCHPHRASE2 nvarchar(200),
 @SEARCHPHRASE3 nvarchar(200),
 @SEARCHPHRASE4 nvarchar(200),
 @subbucket nvarchar(200)
 --@cbs_prm varchar(20)
 --where cbs = @cbs_prm
 DECLARE Total_cursor CURSOR for
 SELECT CBS,Bucket,SEARCHPHRASE,SEARCHPHRASE1,SEARCHPHRASE2,SEARCHPHRASE3,SEARCHPHRASE4,subbucket from bucketmaster
 where cbs = '@cbs_prm';
 --delete from RCA_tmp;
 Open Total_cursor ;
 -- I think it has to be outside of the cursor, before "Open Total_cursor"
 FETCH NEXT from Total_cursor into @cbs_prm,@bucket, @SEARCHPHRASE, @SEARCHPHRASE1,@SEARCHPHRASE2,@SEARCHPHRASE3,@SEARCHPHRASE4,@subbucket ;
 While @@FETCH_STATUS = 0
 BEGIN
 --insert into rca_tmp
 --(ticketnumber,openeddate,cleareddate,message,serviceline)
 ((select ticketnumber,Openeddate, ClearedDate, Message, serviceline
 from PM_RCAImport
 WHERE serviceline = '@cbs_prm' and
 message like '%@SEARCHPHRASE%'
 and message like '%@SEARCHPHRASE1%'
 and message like '%@SEARCHPHRASE2%'
 and message like '%@SEARCHPHRASE3%'
 and message like '%@SEARCHPHRASE4%' AND
 openedDate between '02/01/2013' and'02/10/2013' AND ClearedDate between '02/01/2013' and'02/10/2013'))
 
 FETCH NEXT from Total_cursor into @cbs_prm,@bucket, @SEARCHPHRASE, @SEARCHPHRASE1,@SEARCHPHRASE2,@SEARCHPHRASE3,@SEARCHPHRASE4,@subbucket ;
 END ;
 close Total_cursor
 deallocate Total_cursor
 GO
  

 
more ▼

asked Feb 23, 2013 at 05:49 PM in Default

avatar image

andyk
10 1 1 3

A simple question is why you need a cursor for this? (Unless you have not provided the whole script)

Feb 25, 2013 at 09:05 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

when you run the query by itself, do you mean you're just removing the ALTER PROCEDURE and AS lines then running the rest, complete with the cursor?

I can see where you have an issue with the WHERE clause of the query inside the cursor. Instead of concatenating the variable values into the query, you're embedding their names.

Try the following format for your WHERE

 WHERE serviceline = @cbs_prm
 AND message LIKE '%' + @SEARCHPHRASE1 + '%'
more ▼

answered Feb 23, 2013 at 10:49 PM

avatar image

KenJ
25k 3 10 20

i thank you sir . i will try and let you know. i thank you so much.

Feb 24, 2013 at 02:27 AM andyk
(comments are locked)
10|1200 characters needed characters left
 USE [pduort]    
 GO
 
 --Object: StoredProcedure [dbo].[Pareto_bucket1] Script Date: 02/21/2013 14:45:06
 
 SET ANSI_NULLS ON    
 GO
 
 SET QUOTED_IDENTIFIER OFF    
 GO
 
 ALTER PROCEDURE [dbo].[Pareto_bucket1] @cbs_prm varchar(20)    
 AS    
 declare @CBS varchar(20),    
 @Bucket nvarchar(200),    
 @SEARCHPHRASE nvarchar(200),    
 @SEARCHPHRASE1 nvarchar(200),    
 @SEARCHPHRASE2 nvarchar(200),    
 @SEARCHPHRASE3 nvarchar(200),    
 @SEARCHPHRASE4 nvarchar(200),    
 @subbucket nvarchar(200)
 
 --@cbs_prm varchar(20)
 
 --where cbs = @cbs_prm
 
 DECLARE Total_cursor CURSOR for    
 SELECT     CBS,Bucket,SEARCHPHRASE,SEARCHPHRASE1,SEARCHPHRASE2,SEARCHPHRASE3,SEARCHPHRASE4,subbucke 
 from bucketmaster    
 where cbs = '@cbs_prm';
 
 --delete from RCA_tmp;
 
 Open Total_cursor ;
 
 -- I think it has to be outside of the cursor, before "Open Total_cursor"
 
 FETCH NEXT from Total_cursor into @cbs_prm,@bucket, @SEARCHPHRASE, 
   @SEARCHPHRASE1,@SEARCHPHRASE2,@SEARCHPHRASE3,@SEARCHPHRASE4,@subbucket ;
 
 While @@FETCH_STATUS = 0
 
 BEGIN
 
 --insert into rca_tmp
 
 --(ticketnumber,openeddate,cleareddate,message,serviceline)
 
 ((select ticketnumber,Openeddate, ClearedDate, Message, serviceline    
 from PM_RCAImport    
 WHERE serviceline = '@cbs_prm' and    
 message like '%@SEARCHPHRASE%'    
 and message like '%@SEARCHPHRASE1%'    
 and message like '%@SEARCHPHRASE2%'    
 and message like '%@SEARCHPHRASE3%'    
 and message like '%@SEARCHPHRASE4%' AND    
 openedDate between '02/01/2013' and'02/10/2013' AND ClearedDate between '02/01/2013' and'02/10/2013'))
     
 FETCH NEXT from Total_cursor into @cbs_prm,@bucket, @SEARCHPHRASE,     
 @SEARCHPHRASE1,@SEARCHPHRASE2,@SEARCHPHRASE3,@SEARCHPHRASE4,@subbucket ;    

 END ;
 
 close Total_cursor    
 deallocate Total_cursor    
 GO
more ▼

answered Feb 23, 2013 at 05:55 PM

avatar image

andyk
10 1 1 3

This is pretty long: is it different than the query in the original question?

Feb 23, 2013 at 10:50 PM KenJ
(comments are locked)
10|1200 characters needed characters left

In addition to the very successful review by Ken agregaria dating them in the comparison you used a neutral format 'yyyymmdd' to avoid any confusion with the reginal configuration.

openedDate between '02/01/2013' and '02/10/2013'... change to openedDate between '20130102' and ' 20130210'...

more ▼

answered Feb 24, 2013 at 11:07 PM

avatar image

JLGonzalez
70 3 3 6

thank you, i will try the same. i thank you all.

Feb 25, 2013 at 12:12 AM andyk
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x476

asked: Feb 23, 2013 at 05:49 PM

Seen: 861 times

Last Updated: Feb 25, 2013 at 09:05 AM

Copyright 2017 Redgate Software. Privacy Policy