Stored Procedure in the Nutshell??

Hello all,

What is a Stored procedure in terms of its execution compared with other solution.can anyone describe in detail about it.

more ▼

asked Nov 17, 2011 at 03:36 AM in Default

avatar image

Sql server R2
3 4 4 5

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

A stored procedure is nothing more simple than a way to store a query on the server so that lots of different connections can use it over & over. Stored procedures are also parameterized, meaning that they define parameters for input of values, which helps to ensure that once a plan is created for the procedure it can be reused over & over.

There are two other options for queries. You can use parameterized queries, which are a type of ad hoc query that are parameterized like stored procedures. These are almost the same as stored procedures, but are not stored on the system, so they must be generated by the calling application. Finally there are full ad hoc queries. These contain no parameters and are not stored on the system. Their plans are seldom reused and the calling application must maintain this code.

There are lots of other details around all these, but it would take pages and pages to lay it all out. What is that you're trying to get out of open-ended questions like this?

more ▼

answered Nov 17, 2011 at 04:55 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

Just as a small nitpick, while stored procedures are often (perhaps most often) used to store a query, it can go well beyond that. They can include DML, DDL, conditional logic, even do alterations such as changing permissions and certain types of security settings. Very succinctly, a stored procedure is...well a procedure that is stored in the server allowing it to be invoked repeatedly.

Nov 17, 2011 at 09:57 AM TimothyAWiseman

True. All true. Just trying to keep the answer short. There's actually way too much to say about stored procedures to put into this format.

Nov 17, 2011 at 10:01 AM Grant Fritchey ♦♦

Good Explanation.

Nov 17, 2011 at 07:48 PM Sql server R2
(comments are locked)
10|1200 characters needed characters left

Whilst we appreciate your curiosity in SQL Server, open questions like this (and your one relating to indexes) are beyond the scope of a forum such as this. To understand them fully you should access the information available in Books OnLine (http://msdn.microsoft.com/en-us/library/ms187926.aspx). This forum will be more useful to you when you have a specific problem or have a task that you are unable to complete. We will then be able to suggest specific options based on our experience and knowledge of SQL Server.

Please keep asking questions but keep in mind that answers here will not be able to cover a whole topic, especially when there are better resources already available elsewhere

more ▼

answered Nov 17, 2011 at 03:51 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

Thanks i will check for other websites where i can get answer to my questions.Mean Time i will also try to ask some meaning full questions here on this forum.

Nov 17, 2011 at 04:08 AM Sql server R2

Congrats on hitting 30K! Huge accomplishment.

Nov 17, 2011 at 04:51 AM Grant Fritchey ♦♦

Agree with @Grant Fritchey - epic effort and well done on 30k!!

Nov 17, 2011 at 04:59 AM WilliamD

thanks guys, it's a pleasure. and sometimes a chore. and other times hillarious. and other times enlightening. and other times it just makes me feel good. :)

Nov 17, 2011 at 06:52 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 17, 2011 at 03:36 AM

Seen: 971 times

Last Updated: Nov 17, 2011 at 03:36 AM

Copyright 2018 Redgate Software. Privacy Policy