locked
How To Terminate SQL Statement RRS feed

  • Question

  • Hi All,

    I have a table called Order with Columns Name,Amount,Match


    Name,Amount,Match

    1     10     1

    2     20     1

    3     30     1

    4     40     1

    Whenever a Match goes to = 0,The SQL Statement need's to be Killed.


    Can anyone guide me how can I acheive this ?

    Thursday, August 26, 2010 6:17 PM

Answers

  • If I understand correct, then I would do this with an if:

    if exists (select * from Table where Match = 0)

    return

    else

    select ...

    • Proposed as answer by ramireddy Thursday, August 26, 2010 7:37 PM
    • Marked as answer by Anoop83 Friday, August 27, 2010 2:03 PM
    Thursday, August 26, 2010 7:35 PM

All replies

  • Which SQL Statement do you use? More information will give a better chance for answers.

    You can use a select-statement like this:

    select ... from Table where match > 0

    Thursday, August 26, 2010 6:21 PM
  • If you are looking for result that only before the first occurence of zero then

    SELECT * FROM TABLE_NAME

    WHERE Name < (SELECT MIN(Name) FROM TABLE_NAME WHERE Match = 0 GROUP BY Match)


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Thursday, August 26, 2010 6:24 PM
  • What do u mean by "Killed"??

    Is it means it needs to show all records until, Match Column to be 0, once it reaches 0, it needs to skip all remaining records, even when the value became again 1??

    Eg: for the below rows, what is the expected output? you need that last row or not?? and also what is the order by column in your table?

    1  40 1

    2  20 1

    3  10 0

    4  15 1

     

    Thursday, August 26, 2010 6:26 PM
  • Thanks for the reply.once it reaches to the record 3 immeditately the SQL stMt needs to be terminated.
    Thursday, August 26, 2010 6:52 PM
  • If you are looking for result that only before the first occurence of zero then

    SELECT * FROM TABLE_NAME

    WHERE Name < (SELECT MIN(Name) FROM TABLE_NAME WHERE Match = 0 GROUP BY Match)


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.

    In sub-query "Group by Match" Clause is not required. Remove that.  

     

    Anoop, u can use this query by Deven, with removing "Group by Match"  in subquery.

     

    and in btn, Is the Name column is "Order" column for the table??? If Name column indicates the order of table, then only the above query is correct.

    Thursday, August 26, 2010 6:57 PM
  • All  I am looking When Match Column Encounters Zero , The SQL Statatment Shoud be Terminated.It should not retrun any records. How Can we done this?
    Thursday, August 26, 2010 7:30 PM
  • I am Looking for something

     

    SELECT NAME,AMOUNT

    CASE WHEN

    MATCH =0 THEN "MY SQL STMT SHOULD NOT WORK AND IT SHOULD BE TERMINATED"

    ELSE

    MATCH = 1 THEN 1

    END

    FROM  ORDER

    Thursday, August 26, 2010 7:33 PM
  • If I understand correct, then I would do this with an if:

    if exists (select * from Table where Match = 0)

    return

    else

    select ...

    • Proposed as answer by ramireddy Thursday, August 26, 2010 7:37 PM
    • Marked as answer by Anoop83 Friday, August 27, 2010 2:03 PM
    Thursday, August 26, 2010 7:35 PM
  • Hi Thanks for the Reply. I am not looking for Result Set. If my Match Column Encounters Zero Immedaitely witout returning any resuly the SQL Statement should be terminated.

     How Can we do this? Please help me out in this ?

    Thursday, August 26, 2010 7:36 PM
  • I know Your Query will be Useful when Order table Population is done. But  in my scenaro the SQL Stmt should be terminated while we populate the Order table itself. I mean When in the Order table If Match Column encounters " 0" Immedaitely SQL Stamt should be terminated.
    Thursday, August 26, 2010 7:41 PM
  • Did you read what I'm posted?

    If you don't need the output, then let off the else-statement.

    Thursday, August 26, 2010 7:44 PM
  • It is unclear what you are trying to achieve.

    If you dont want any result set if there is a match = 0 then what are you trying to achieve.

    Please explain the whole process, what are you trying to achieve with this. It absoultely makes no sense if you just want to check the value of match and do nothing.


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Thursday, August 26, 2010 7:45 PM
  • I know Your Query will be Useful when Order table Population is done. But  in my scenaro the SQL Stmt should be terminated while we populate the Order table itself. I mean When in the Order table If Match Column encounters " 0" Immedaitely SQL Stamt should be terminated.

    It seems to be he is importing the data into the table. If any row consists of 0, he dont want to import entire data......

     

    if that is the case, how you are importing? sqlBulkCopy??

    as you will be getting all the data into a datatable, do the below thing.

    DataView dv = yourdatatable.DefaultView;

    dv.RowFilter = " Match = 0";

    if(dv.ToTable().Rows.Count > 0)

        // then import data

    else

       // give the message that it has invalid values.

     

    Otherwise, u can create a check constraint on the column also...

    Thursday, August 26, 2010 8:01 PM
  • Hi Thanks for the Reply. Actually I am writing a Batch Script. In this Prcoess The Batch Script needs to execute set of SQL Stmt's. In one of the SQL Stmts I have Column called Match. When this Column Encounter a Value = "0" .Then the Entire Set Of  SQL Stmt's should not be executed.

    SQLCMD -S ServerName -U UserName -P Password -i "SQL Script location" -o "Output File If SQL Script Fails"

    An Output file should be generated only when my Input file is not executed.

    How can we do this ?

    Thursday, August 26, 2010 8:18 PM
  • Hi Thanks for the Reply. Actually I am writing a Batch Script. In this Prcoess The Batch Script needs to execute set of SQL Stmt's. In one of the SQL Stmts I have Column called Match. When this Column Encounter a Value = "0" .Then the Entire Set Of  SQL Stmt's should not be executed.

    SQLCMD -S ServerName -U UserName -P Password -i "SQL Script location" -o "Output File If SQL Script Fails"

    An Output file should be generated only when my Input file is not executed.

    How can we do this ?


    This post should be the first post in this thread. that means, you need to tell this kind of details as a first post... This is more clear....

    I will advise you to start a new thread with the above text.  This is not my specialized area. So, i can't help you.

    Next time onwards, when you are asking a question, give these kind of details in beginning itself.....

     

    Friday, August 27, 2010 5:34 AM
  • Sorry for not giving the Proper Details.Next Time onwards I will try to give all the Information about my query.
    Friday, August 27, 2010 2:02 PM
  • Thanks for  the Answer and Thanks for All of Your Time !!!!!
    Friday, August 27, 2010 2:04 PM