Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Cursor Help Expand / Collapse
Author
Message
Posted Wednesday, September 19, 2012 9:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, July 26, 2014 5:26 PM
Points: 205, Visits: 744
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.
Post #1361709
Posted Thursday, September 20, 2012 1:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 1:28 AM
Points: 2,105, Visits: 5,393
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/
Post #1361764
Posted Thursday, September 20, 2012 9:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
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)
Post #1362088
Posted Thursday, September 20, 2012 10:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
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.
Post #1362128
Posted Thursday, September 20, 2012 12:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, July 26, 2014 5:26 PM
Points: 205, Visits: 744
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.
Post #1362211
Posted Thursday, September 20, 2012 12:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
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)
Post #1362218
Posted Thursday, September 20, 2012 12:28 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, July 26, 2014 5:26 PM
Points: 205, Visits: 744
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.
Post #1362229
Posted Thursday, September 20, 2012 12:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
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)
Post #1362235
Posted Thursday, September 20, 2012 1:17 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:55 AM
Points: 83, Visits: 646
@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
sqlwithmanoj.wordpress.com
Post #1362271
Posted Thursday, September 20, 2012 1:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
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)
Post #1362279
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse