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.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.|||...or Views...|||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.|||I rarely use Views as well, preferring procedures or user-defined table functions. But to give a complete answer they should be mentioned.|||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|||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.|||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

:) :)|||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.|||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.|||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 + '%'|||RedneckGeek,
Type SQL Server cursros are bad into Google. Read away.|||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.|||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.|||<snip>
<snip>
<snip>|||MaxA,
I am not sure if this a subtle slam at me and I am a little cranky b\c I am quitting smoking again but I assure you that I do not meet your description so I am letting this go. I have never had a failed project and I have spent my time in the classroom.|||You are going through the whole table in the subquery for each record in the main query. not in a correlated subquery, which the optimizer executes as a join

your rant about views was pretty funny

like i said: education and controls

;)|||There's a reason the average is 3 years - and that is because people w/o any formal training can call themselves developers

Hmmm...my undergraduate degree is in Behavioral Psychology and my master's degree is in Corporate Finance.

Everything I know about computers, database design, and SQL programming is self taught. I guess that makes me and all the best developers I have ever met unqualified for our jobs...

NOW what will we do with out lives? :(

"Would you like fries with that?"|||I have a BS in political science.

26 Information Systems credits at the local community college.

1/2 self taught. 1/2 classroom. + some occassional company paid training.|||Isn't that redundant? I though all politics was BS?

Okay that was a slam, but just in fun. Slap on an extra nicotine patch and calm down, buddy. ;)|||:eek:

No insult intended to anybody. I sincerely apologize to anybody who interpreted my poorly worded post as a slam.

I appreciate your contributions, Thrasymachus, and although my wrong statement may indicate otherwise, I am indifferent to whatever certificates one may possess. Instead, I value results and you have contributed more valuable posts than what I ever will.

I would attempt to clarify my statement, but prefer to avoid repeating my mistake. I was wrong and I really am sorry. I shall be more careful.

You too, blindman. I could post nonstop and never approach adding the value you have. Regardless of how you came about your SQL education, it is effective.

Finally, best of luck with kicking the habit, Thrasymachus. You've gone farther than many by just trying.

I'm gonna go edit/remove my mistake and then find a hole to bury my head in... :o|||The cool thing about this forum is that you get as many post-points for saying something stupid as you do for saying something nice or smart. Keep going, and you will quickly reach as many post counts as Thrasy...

No offense taken! :)|||Yeah, heck almost ten percent of my postings are actually in The Corral (http://www.dbforums.com/t989246.html). I'm just as happy posting about music, jokes, etc as I am about data modeling, SQL, programming, etc. I'm an equal opportunity idiot!

-PatP|||I've never even gotten a degree...and the only class I took was in cobol (2 classes)...should I feel bad about it? Or better yet, have I ever worried about my job, or (god forgive me) its security?|||Ok so everyone that has posted to your reply clearly did not answer your ? So I will try my best to explain. First you need to let people know that you understand or dont understand SQL Server. If you dont then I would suggest taking a weekend to read a book. Play Play Play with SQL Server. Ofcourse backup before you play around. Other wise you will be reinstalling. You will mess up and stuff wont work right but thats the best way to learn. Trust me dont try to setup and fully functional database without first know the software. Even then you are bound to have a bad DB the first time. How much DBA/DA experiance do you have?
Ok so from what i understand you converted your access database to SQL Server? Im assuming yes. This can cause all kinds of problems if done incorrectly. Dont use Access to convert. Use the DTS (Data Transformation Services) in SQL to do this conversion. Now for your issues with views. Views are easy to construct but really stored procedures are more efficient because they are precompiled so the Server doesnt have to do extra work when the request occurrs. You may not know what the hell a stored procedure is, but just do a little research. Some places are books online, safari books (very good) and take a look at www.vtc.com (great site for introductory lessons on all IT software). Views are not bad to use but are limited in functionality and as stated before can reduce performance.
Now for your vb side (client front end). Are you using Access or .net? Depending on your level of programming expieriance, if its minimal, I would go with Access. Its a good software but it will depend on how many people use this software (Access). Access can be unstable. Plus you will need to know VBA its very close to VB. Once again to learn VBA goto safari books and VTC. To get data from your sql database you will need to use ADO. ADO has an object called Recordset. Think of recordsets as virtual tables. Read more about them to understand how to use this great little object.
If I could i would like to recommend looking into using C# with ADO.net. Its very easy to use. It may take some time to learn C# but well worth it. You can get C# express for free. You will need to check to see if it is compatible with sql 2000 its meant for the new line of express shit microsoft came out with. I use c# and find it very powerful yet easy to use. Plus with the reporting features of SQL Server you dont have to use crystal reports(good but shitty program)
If this is your first time using the SQL Database then dont think you are the only one whos ever thought "holy crap too many buttons". Just put in as much time as you can to learn the software not just Enterprise Manager, or as some of the geeks(me too) say, "EPM". As if the acronyms arent bad enought in the IT industry. Dont try to learn it all at once. First learn backing up!!!!!!! Before anything learn it and love it! You always want to back up the system DB's.
I hope this helped and if you have more ?'s maybe some people will answer them instead of posting crap!! Once again when you post specify exactly what is wrong, your experiance with the DB (your knowledge) and once again be detailed.|||jayblaze2,

the guy asked about converted Access db and you gave him a lecture of how to survive in IT industry... And as a tipical professor made a lot of unproven statements that do not hold water even from the first look...I did read the whole "blah" that you typed, but what leaped out (there may be more) was this "precompiled" perception about procs. Who told you that? Or you figured it on your own? And how's a controlled selection from a view different from an execution of a stored procedure in terms of performance? Oh, because the stored procedure is precompiled? ;)|||And the precompiled thing is overblown anyway. Honestly, how long does it take the server to construct a query plan? The effects would only be noticed when a view is called hundreds of times a minute, and SQL Server will cache frequently used execution plans.

Concentrate on the FUNCTIONAL differences. Views are useful because they are simple (the have to be single-step processes), and because they can frequently be made updateable and thus can masquerade as tables. Their big drawback is that they cannot accept parameters, such as MS Access queries can.

Stored procedures can accept parameters, and are capable of a much higher degree of complexity than views, but cannot be used with the transparent ease that views can.

Table functions can take parameters like stored procedures and are capable of nearly the same complexity, plus they can be joined like regular tables. Unlike views, they are not updateable, and the syntax of their usage is a little awkward.|||Views are easy to construct but really stored procedures are more efficient because they are precompiled so the Server doesnt have to do extra work when the request occurrsHey ... ever read this in BOL

In SQL Server version 6.5 and earlier, stored procedures were a way to partially precompile an execution plan. At the time the stored procedure was created, a partially compiled execution plan was stored in a system table. Executing a stored procedure was more efficient than executing an SQL statement because SQL Server did not have to compile an execution plan completely, it only had to finish optimizing the stored plan for the procedure. Also, the fully compiled execution plan for the stored procedure was retained in the SQL Server procedure cache, meaning that subsequent executions of the stored procedure could use the precompiled execution plan.

SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans. The database engine uses an efficient algorithm for comparing new Transact-SQL statements with the Transact-SQL statements of existing execution plans. If the database engine determines that a new Transact-SQL statement matches the Transact-SQL statement of an existing execution plan, it reuses the plan. This reduces the relative performance benefit of precompiling stored procedures by extending execution plan reuse to all SQL statements.|||Lesson learned, jayblaze2? There are lot of smart and knowledgeable peope on this forum, and no single person here has greater knowledge than the collective. So beware before slamming the whole group.|||Nice signature quote .. its funny and soo very true :rolleyes:
Just wondering who is this Douglas Adams
I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams|||The late Adams Douglas Adams was just this guy, you know? He was one hoopy frood who really knew where his towel was.|||Just wondering who is this Douglas Adamsgoogle is your friend

there are at least 42 sites that will tell you

:)