What is a stored procedure

0 votes

What is the make-up of a stored procedure (things each must have to be a stored procedure)?

Feb 4 in Database by Vaani
• 7,020 points
116 views

1 answer to this question.

0 votes

A stored procedure is a set of precompiled SQL statements that are used to perform a special task.

Stored procedures are a batch of SQL statements that can be executed in a couple of ways. Most major DBMs support stored procedures; however, not all do. You will need to verify with your particular DBMS help documentation for specifics. As I am most familiar with SQL Server I will use that as my samples.

To create a stored procedure the syntax is fairly simple:

CREATE PROCEDURE <owner>.<procedure name>

     <Param> <datatype>

AS

     <Body>

So for example:

CREATE PROCEDURE Users_GetUserInfo

    @login nvarchar(30)=null

AS

    SELECT * from [Users]
    WHERE ISNULL(@login,login)=login

Stored procedures also have a security benefit in that you can grant execute rights to a stored procedure but the user will not need to have read/write permissions on the underlying tables. This is a first step against SQL injection.

answered Feb 4 by Neha
• 8,560 points

Related Questions In Database

0 votes
1 answer

What does recursive stored procedure?

Recursive stored procedure refers to a stored ...READ MORE

answered Sep 27, 2018 in Database by Sahiti
• 6,380 points
9,905 views
0 votes
1 answer

What is a covered Index?

A covering index is an index that contains all ...READ MORE

answered Oct 10, 2018 in Database by Frankie
• 9,830 points
320 views
0 votes
1 answer

What is a Relationship and what are they?

Relation or links are between entities that ...READ MORE

answered Oct 23, 2018 in Database by DataKing99
• 8,240 points
216 views
0 votes
1 answer

What is a Foreign key?

Foreign key maintains referential integrity by enforcing ...READ MORE

answered Oct 26, 2018 in Database by Sahiti
• 6,380 points
321 views
0 votes
0 answers

What is a stored procedure?

119 What is a "stored procedure" , how do they work? What ...READ MORE

May 27 in Others by avinash
• 940 points
18 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3 in Database by Vaani
• 7,020 points
56 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4 in Database by Vaani
• 7,020 points
20 views
0 votes
1 answer

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

At the top level there are mainly ...READ MORE

answered Feb 4 in Database by Neha
• 8,560 points
89 views
0 votes
1 answer

What is the definition of cardinality in SQL?

Cardinality is defined as the "number of ...READ MORE

answered Feb 15 in Database by Neha
• 8,560 points
158 views
0 votes
1 answer

Function vs. Stored Procedure in SQL Server

Functions are calculated values that cannot make ...READ MORE

answered Feb 17 in Database by Neha
• 8,560 points

edited Feb 17 by Neha 161 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP