x

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

Sql server R2 gravatar image

Sql server R2
3 3 4 4

(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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.7k 19 21 74

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

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x408

asked: Nov 17, 2011 at 03:36 AM

Seen: 737 times

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