Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Join Date
    Feb 2005
    Location
    Thane, Maharashtra,India
    Posts
    17

    Unanswered: How to use queries in Sql Server

    In MS-ACCESS we can write and store queries and then deploy them in our applications. How can we do the same in SQL-SERVER 2000

    Queries written in MS-ACCESS were converted into tables while I migrated the database from ACCESS to SQL SERVER.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Store Procedures. Go to Start\Programs\Microsoft SQL Server\Books Online. Choose the search tab and type 'stored procedures'. And start reading and never stop.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or Views...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    views are poop

    views are poop. Before SQL 2K, you could not index them. and if they sit around long enough they get misused. People will build views on top of views or they will build inefficient queries referencing views and the queries start bogging down and bad application writers who dont know how to use the QA fully start wondering why their queries take so long. I put views in the same camp as subqueries against large tables and cursors as things I do not use.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I rarely use Views as well, preferring procedures or user-defined table functions. But to give a complete answer they should be mentioned.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2005
    Location
    Thane, Maharashtra,India
    Posts
    17
    Thanks,
    I have created Views, but how to call them in my VB 6.0 application? also please give me how to write procedures by giving one demo code

  7. #7
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74
    Views acts like tables when it comes useability. Internally, well, that's another matter ...
    This means that you could use select's to retrieve data from them, like

    select someField, SomeOtherField from MyView Where SomeCondition = SomeValue

    Pretty straightforward SQL if you ask me. How you do it in VB6 is probably by doing some ADO-connection and executing the above statement and looping thorugh a recordset that holds that answer to the query. This is described in the helpsystem for VB6.
    The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Thrasymachus
    views are poop...I put views in the same camp as subqueries against large tables and cursors as things I do not use.
    oh my

    i would agree with cursors being bad, generally, but views and subqueries are good

    i suspect in your shop the problem is not views or subqueries but education and controls

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by r937
    oh my

    i would agree with cursors being bad, generally, but views and subqueries are good

    i suspect in your shop the problem is not views or subqueries but education and controls

    Cursors = Bad???

    Out of ignorance.... Why?

    I don't use them a lot, but I do use them when I can't figure out another way to accomplish something.
    Inspiration Through Fermentation

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Rudy,

    How many posts on this site have you seen where the poster says "I can't update my view that is made up of multiple tables, darrrrr...."

    They cause more problems then they are worth. They are the lazy man's approach to managing security.

    I may not be as seasoned as some of you guys but people consider me a good mid level developer (5 years in). And I have been dropped into situations where people have gone view crazy and their queries run like molasses and the server is cranked out all of the time because people are running queries against these views which is basically querying a query and the query they are executing is poorly suited to the original attention of the view. Then you have these guys who reference views from their views.

    You and I might be able to figure this out, but Mr. "young-hot-shot-recent- computer-science-grad-who-spent-too-much-time-learning-worthless-crap- like-ADA" won't know what's up.

    It's all about the 5 percent rule. 5% of people know what they are doing and everyone else is along for the ride and that is why my first law of development is KEEP IT SIMPLE because you do not know who is coming in behind you.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Rudy,
    If you do not know why subqueries against large tables are bad, well I just do not know what to say. You are going through the whole table in the subquery for each record in the main query. The pain here should be obvious. 99% of the time you can use a standard join or an Exists or you could do the old ansi 92 if you need a like.

    Update table1 Set field1 = table2.field2
    FROM table1,table2
    Where table1.field3 like table2.field3 + '%'
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    RedneckGeek,
    Type SQL Server cursros are bad into Google. Read away.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Views are not bad. It is just that most of the time Stored Procedures and User-Defined functions are better. Though not for the reasons you cite:

    "How many posts on this site have you seen where the poster says "I can't update my view that is made up of multiple tables, darrrrr....""
    ...and the result sets of Stored Procedures are NEVER updateable, so I don't see this as a valid argument.

    If you do not know why subqueries against large tables are bad, well I just do not know what to say. You are going through the whole table in the subquery for each record in the main query.
    Not necessarily true. It depends upon the structure of the statement.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I do not have time for a drawn out back and forth today but...

    "...and the result sets of Stored Procedures are NEVER updateable, so I don't see this as a valid argument."

    no s**t. But if your stored procedures reference tables, it is a little more obvious about how you need to write your update statements. You have seen the afore mentioned posts I am referencing.

    "Not necessarily true. It depends upon the structure of the statement."

    But it does happen.

    The average career of a programmer is 3 years. My audience here are those folks and the advice is meant to keep them out of trouble.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  15. #15
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    <snip>
    <snip>
    <snip>
    Last edited by MaxA; 02-17-05 at 21:08.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •