Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursor Help


Cursor Help

Author
Message
rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1292
I have a question,

Here is my query,

SELECT SID FROM chartdDB
WHERE ADate = @ADate
AND SID NOT IN (SELECT SID FROM UPLOADRequest )

when i run this query sometime i get 1 SID or sometime more then One.

I have two question.

1) How i can set my Parameter to Previous day
Set @ADate = (If i run today @ADate should be Previous day) ?

2) Once i run this query
SELECT SID FROM chartdDB
WHERE ADate = @ADate
AND SID NOT IN (SELECT SID FROM UPLOADRequest )

THEN EXECUTE THIS STORE PROCEDURE

exec Save_Request @SID

, How i can create Cursor if i get more than one result this store proc run more than one time.

Please let me know if my question is not clear.

Thank You.
Adi Cohn-120898
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2224 Visits: 6079
Bellow is code that shows how to get yesterday's date by adding to a specific date (in my example January 1, 2000) the amount of days that past since that date minus 1.

declare @dt datetime

set @dt = dateadd(dd,datediff(dd,'20000101',getdate())-1,'20000101')
select @dt, getdate()




As for the cursors – there are plenty of explanation about working with cursors on the internet. Here is one of those places – http://blog.sqlauthority.com/2007/01/01/sql-server-simple-example-of-cursor/.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 17016
Cursors are notoriously bad for performance and should be avoided except for maintenance tasks if at all possible. I suspect you don't need a cursor for this at all. If you can post some ddl and sample data along with a clear explanation of what you are trying to do I am sure this can be done without a cursor. Take a look at the first link in my signature about best practices.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
OTF
OTF
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 4128
rocky_498 (9/19/2012)
I have a question,

Here is my query,

SELECT SID FROM chartdDB
WHERE ADate = @ADate
AND SID NOT IN (SELECT SID FROM UPLOADRequest )

when i run this query sometime i get 1 SID or sometime more then One.

I have two question.

1) How i can set my Parameter to Previous day
Set @ADate = (If i run today @ADate should be Previous day) ?

2) Once i run this query
SELECT SID FROM chartdDB
WHERE ADate = @ADate
AND SID NOT IN (SELECT SID FROM UPLOADRequest )

THEN EXECUTE THIS STORE PROCEDURE

exec Save_Request @SID

, How i can create Cursor if i get more than one result this store proc run more than one time.

Please let me know if my question is not clear.

Thank You.


As has already been pointed out, you don't want to use a cursor to do this.
If I understand correctly, you would like to execute Save_Request for every SID value you return from
query 2)? You haven't provided any ddl or sample data so I can only make a suggestion which may or may not work in your case.
You can insert the output of query 2) to a table and supply it to the Save_Request Procedure via a Table Parameter and do the processing for all SIDs avoiding the need for the cursor and multiple calls to the Procedure.
rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1292
Hi guys thank you for your reply, I created below CURSOR and its work fine,

DECLARE @SID int

Declare InsertSID Cursor For
Select DISTINCT Sid from MDB
where ADate = dateadd(day,datediff(day,1,GETDATE()),0)
AND SID NOT IN (SELECT SID FROM MRequest )

OPEN InsertSID
FETCH NEXT FROM InsertSampleID
INTO @SID
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@SID <> 0)
BEGIN
EXEC [INSERT_SP] @Sid
END
Fetch Next from InsertSID
into @SId


END

close InsertSID
deallocate InsertSID

IS any one have better idea, Please share with us.

Thank You.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 17016
rocky_498 (9/20/2012)
Hi guys thank you for your reply, I created below CURSOR and its work fine,

DECLARE @SID int

Declare InsertSID Cursor For
Select DISTINCT Sid from MDB
where ADate = dateadd(day,datediff(day,1,GETDATE()),0)
AND SID NOT IN (SELECT SID FROM MRequest )

OPEN InsertSID
FETCH NEXT FROM InsertSampleID
INTO @SID
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@SID <> 0)
BEGIN
EXEC [INSERT_SP] @Sid
END
Fetch Next from InsertSID
into @SId


END

close InsertSID
deallocate InsertSID

IS any one have better idea, Please share with us.

Thank You.


You absolutely do NOT need a cursor for this. It probably does your data ok but the performance is going to get worse and worse as the size of the tables increase. Depending on the complexity of the your INSERT_SP this could either be done as a single insert statement or the INSERT_SP could be modified to use a table parameter. As I said before, we need to have ddl (create table and proc scripts), some sample data (insert statements) and desired output based on your sample data. The first link in my signature will help you figure out where to find and how to post that information.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1292
Once again thanks and i understand what you saying, Here is the PROBLEM.

I can't convert or paste my store procedure here (8 Pages S.P) lot of logic involve....

If i run this query

Select DISTINCT Sid from MDB
where ADate = dateadd(day,datediff(day,1,GETDATE()),0)
AND SID NOT IN (SELECT SID FROM MRequest )

and i get SID > 0

THEN run this S.P

EXEC [INSERT_SP] @Sid

When you are saying you do NOT need Cursor, Ok. Could you please tell me what else should i use to
accomplish this task. I know cursor is dam slow if i get more SID in my first query.
I know i have to provide Sample data/Sample code with insert statment and everything, but in this case
i am sorry i am not providing all information that you need.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 17016
rocky_498 (9/20/2012)
Once again thanks and i understand what you saying, Here is the PROBLEM.

I can't convert or paste my store procedure here (8 Pages S.P) lot of logic involve....

If i run this query

Select DISTINCT Sid from MDB
where ADate = dateadd(day,datediff(day,1,GETDATE()),0)
AND SID NOT IN (SELECT SID FROM MRequest )

and i get SID > 0

THEN run this S.P

EXEC [INSERT_SP] @Sid

When you are saying you do NOT need Cursor, Ok. Could you please tell me what else should i use to
accomplish this task. I know cursor is dam slow if i get more SID in my first query.
I know i have to provide Sample data/Sample code with insert statment and everything, but in this case
i am sorry i am not providing all information that you need.


I suggested a couple of ideas in my last post but it sounds like neither of those are an option. I don't know how long 8 pages is but it sounds like it is a bit complicated. I am guessing this is a lot more than just an insert into a single table. It must be adding data all over the place. Changing that proc to receive a table parameter might help but I don't know your system and how much impact that would make. There are ways around this but it takes a lot more understanding of the tables and such than I have. If changing that proc is not an option then you are probably stuck with a **cough**cursor**cough**.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
manub22
manub22
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 1860
@Sean, Cursors were never on bad performance, its upto you when and how you use them. Some developers use them very early rather than creating complex set based queries, some use them only as a last resort.

In some tightly couple systems (or rather I would say badly designed) there are cases where you cannot change existing code and objects and have to end up using cursors or some iterative approach to get things done.

Here @rocky_498 is also trying to solve a business problem by using this approach, because calling an SP for different set of values is not possible by a set based approach. Even if he tries to do so, he has to deal with 8-pages log Stored Procedure which his manager won;t let him touch, or it would become itself a big project.

In a nutshell I would suggest you to go through all responses and give them a thought wrt your scenario. And as we don't know how complex is your existing SP, so try to use CURSOR approach, check this link for using cursors optimally: http://sqlwithmanoj.wordpress.com/2011/02/07/avoid-cursors-or-use-them-optimally/


~manoj | LinkedIn | Facebook | YouTube
Blog: SQLwithManoj.com

Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 17016
manub22 (9/20/2012)
@Sean, Cursors were never on bad performance, its upto you when and how you use them. Some developers use them very early rather than creating complex set based queries, some use them only as a last resort.

In some tightly couple systems (or rather I would say badly designed) there are cases where you cannot change existing code and objects and have to end up using cursors or some iterative approach to get things done.

Here @rocky_498 is also trying to solve a business problem by using this approach, because calling an SP for different set of values is not possible by a set based approach. Even if he tries to do so, he has to deal with 8-pages log Stored Procedure which his manager won;t let him touch, or it would become itself a big project.

In a nutshell I would suggest you to go through all responses and give them a thought wrt your scenario. And as we don't know how complex is your existing SP, so try to use CURSOR approach, check this link for using cursors optimally: http://sqlwithmanoj.wordpress.com/2011/02/07/avoid-cursors-or-use-them-optimally/


Yes it is true that sometimes you can't the code and that is exactly why I said he may be stuck with it. I would vehemently disagree about your comment on cursors not being bad for performance. Find me a cursor that even come close to a set based solution for performance and I will change will my stance. They of course have their place and are sometimes unavoidable but to say they are not bad for performance is not accurate.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search