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
• 5,040 points
73 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
• 6,260 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,360 points
9,434 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
304 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
200 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,360 points
306 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
• 5,040 points
34 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
• 5,040 points
10 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
• 6,260 points
76 views
0 votes
1 answer

SQL Server: PARTITION BY vs GROUP BY

To understand the differences between GROUP BY ...READ MORE

answered Feb 9 in Database by Neha
• 6,260 points
75 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
• 6,260 points
81 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
• 6,260 points

edited Feb 17 by Neha 55 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