Cursor Help

  • 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.

  • 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/%5B/url%5D.

    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/

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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.

  • Hi guys thank you for your reply, I created below CURSOR and its work fine,

    DECLARE @sidint

    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.

  • rocky_498 (9/20/2012)


    Hi guys thank you for your reply, I created below CURSOR and its work fine,

    DECLARE @sidint

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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.

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • @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/[/url]

  • 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/[/url]

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (9/20/2012)


    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.

    Yes, @sean I agree with you... CURSORS compared to similar SET based solution will be bad on performance.

    But there are cases where you could not device proper set-based solutions and CURSORs are the only last choice. Its upto you how you perceive them as they are only there to deal with those type of cases only.

  • Okay, a non-cursor way to accomplish this same task:

    declare @SQLCmd varchar(max);

    select @SQLCmd = (select 'exec insert_sp ' + cast(md.sid as varchar) + ';' + char(13) + char(10)

    from mdb md

    where md.ADate = dateadd(day,datediff(day,1,GETDATE()),0) and not exists( select 1 from MRequest mr where mr.sid = md.sid)

    for xml path(''),TYPE).value('.','varchar(max)');

    exec (@SQLCmd);

  • manub22 (9/20/2012)


    Sean Lange (9/20/2012)


    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.

    Yes, @sean I agree with you... CURSORS compared to similar SET based solution will be bad on performance.

    But there are cases where you could not device proper set-based solutions and CURSORs are the only last choice. Its upto you how you perceive them as they are only there to deal with those type of cases only.

    We are on the same page here. 😀

    _______________________________________________________________

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Lynn Pettis (9/20/2012)


    Okay, a non-cursor way to accomplish this same task:

    declare @SQLCmd varchar(max);

    select @SQLCmd = (select 'exec insert_sp ' + cast(md.sid as varchar) + ';' + char(13) + char(10)

    from mdb md

    where md.ADate = dateadd(day,datediff(day,1,GETDATE()),0) and not exists( select 1 from MRequest mr where mr.sid = md.sid)

    for xml path(''),TYPE).value('.','varchar(max)');

    exec (@SQLCmd);

    Excellent example of never say never to a set based solution. Well played sir!!!

    _______________________________________________________________

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (9/20/2012)


    Lynn Pettis (9/20/2012)


    Okay, a non-cursor way to accomplish this same task:

    declare @SQLCmd varchar(max);

    select @SQLCmd = (select 'exec insert_sp ' + cast(md.sid as varchar) + ';' + char(13) + char(10)

    from mdb md

    where md.ADate = dateadd(day,datediff(day,1,GETDATE()),0) and not exists( select 1 from MRequest mr where mr.sid = md.sid)

    for xml path(''),TYPE).value('.','varchar(max)');

    exec (@SQLCmd);

    Excellent example of never say never to a set based solution. Well played sir!!!

    I knew someone would like this solution! Thank you.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply