Презентация на тему SQL Server Stored Procedure

can be performed by using system stored procedures.

System

stored procedures are prefixed by sp_, so it is not advisable to use sp_ for any of the stored procedures that we create, unless they form a part of our SQL Server installation.

Stored procedures can be: - system / sp_ help …; sp_helptext …./ - local - temporary
- remote
- extended

stored procedure is a collection of SQL statements that

can be called via a CALL statement.

• A user-defined function is also a collection of SQL statements, but it can be called and used like any Built-in function

• A prepared statement is a query that is stored on the server and that can be executed in the future

they can be called. • The declaration can include parameters. •

If parameters are changed inside the procedure, their modified values are accessible after the call.

Creating a simple stored procedure  

To create a stored procedure to do this the code would look like this:

To call the procedure to return the contents from the table specified, the code would be:

with parameters  The real power of stored procedures is the

ability to pass parameters and have the stored procedure handle the differing requests that are made. 

good practice to pass in all parameter values, but

sometimes it is not possible.  So in this example we use the NULL option to allow you to not pass in a parameter value. 

If we create and run this stored procedure as is it will not return any data, because it is looking for any City values that equal NULL.

ISNULL function to get around this.
So if a value

is passed it will use the value to narrow the result set and if a value is not passed it will return all records.

to do.  You just need to list each parameter

and the data type separated by a comma as shown below.

To execute this you could do any of the following:

stored procedure  
Overview In a previous topic we discussed how

to pass parameters into a stored procedure, but another option is to pass parameter values back out from a stored procedure. 
One option for this may be that you call another stored procedure that does not return any data, but returns parameter values to be used by the calling stored procedure.

Explanation Setting up output paramters for a stored procedure is basically the same as setting up input parameters, the only difference is that you use the OUTPUT clause after the parameter name to specify that it should return a value. 
The output clause can be specified by either using the keyword "OUTPUT" or just "OUT".

it as follows.  First we are going to declare

a variable, execute the stored procedure and then select the returned valued.

This can also be done as follows, where the stored procedure parameter names are not passed.

you first create your stored procedures it may work

as planned, but how to do you modify an existing stored procedure. 
In this topic we look at the ALTER PROCEDURE command and it is used.

Explanation Modifying or ALTERing a stored procedure is pretty simple.  Once a stored procedure has been created it is stored within one of the system tables in the database that is was created in. 
When you modify a stored procedure the entry that was originally made in the system table is replaced by this new code.  Also, SQL Server will recompile the stored procedure the next time it is run, so your users are using the new logic. 
The command to modify an existing stored procedure is ALTER PROCEDURE or ALTER PROC.

have the following existing stored procedure:  This allows us

to do an exact match on the City.

Let's say we want to change this to do a LIKE instead of an equals.
To change the stored procedure and save the updated code you would use the ALTER PROCEDURE command as follows.

Now the next time that the stored procedure is called by an end user it will use this new logic.

to creating stored procedures there is also the need

to delete stored procedures.  This topic shows you how you can delete stored procedures that are no longer needed.
Explanation The syntax is very straightforward to drop a stored procedure, here are some examples.

Dropping Single Stored Procedure
To drop a single stored procedure you use the DROP PROCEDURE or DROP PROC command as follows.

with one command you specify each procedure separated by

a comma as shown below.

Overview A great new option that was added in SQL

Server 2005 was the ability to use the TRY..CATCH paradigm that exists in other development languages.  Doing error handling in SQL Server has not always been the easiest thing, so this option definitely makes it much easier to code for and handle errors.
Explanation If you are not familiar with the TRY...CATCH paradigm it is basically two blocks of code with your stored procedures that lets you execute some code, this is the Try section and if there are errors they are handled in the Catch section. 

how this can be done.  As you can see

we are using a basic SELECT statement that is contained within the TRY section, but for some reason if this fails it will run the code in the CATCH section and return the error information.

thing to do for all of your SQL Server

objects is to come up with a naming convention to use.  There are not any hard and fast rules, so this is really just a guideline on what should be done.
Explanation SQL Server uses object names and schema names to find a particular object that it needs to work with.  This could be a table, stored procedure, function ,etc...
It is a good practice to come up with a standard naming convention for you objects including stored procedures.
Do not use sp_ as a prefix
One of the things you do not want to use as a standard is "sp_".  This is a standard naming convention that is used in the master database.  If you do not specify the database where the object is, SQL Server will first search the master database to see if the object exists there and then it will search the user database. So avoid using this as a naming convention.

to come up with a standard prefix to use

for your stored procedures.  As mentioned above do not use "sp_", so here are some other options.
usp_
sp
usp
etc...

SQL Server will figure out that it is a stored procedure, but it is helpful to differentiate the objects, so it is easier to manage.
So a few examples could be:
spInsertPerson
uspInsertPerson
usp_InsertPerson
InsertPerson

that the stored procedure takes and then give it

a name representing the object it will affect.
So based on the actions that you may take with a stored procedure, you may use:
Insert
Delete
Update
Select
Get
Validate
etc...
So here are a few examples:
uspInsertPerson
uspGetPerson
spValidatePerson
SelectPerson
etc...

that you will use when saving the objects.  A

schema is the a collection of objects, so basically just a container.  This is useful if you want to keep all utility like objects together or have some objects that are HR related, etc...
This logical grouping will help you differentiate the objects further and allow you to focus on a group of objects.
Here are some examples of using a schema:
HR.uspGetPerson
HR.uspInsertPerson
UTIL.uspGet
UTIL.uspGetLastBackupDate
etc...

To create a new schema you use the CREATE SCHEMA command
Here is a simple example to create a new schema called "HR" and giving authorization to this schema to "DBO".

stored procedures  
Overview There are many tricks that can be

used when you write T-SQL code.  One of these is to reduce the amount of network data for each statement that occurs within your stored procedures.  Every time a SQL statement is executed it returns the number of rows that were affected.  By using "SET NOCOUNT ON" within your stored procedure you can shut off these messages and reduce some of the traffic.

Explanation As mentioned above there is not really any reason to return messages about what is occuring within SQL Server when you run a stored procedure.  If you are running things from a query window, this may be useful, but most end users that run stored procedures through an application would never see these messages. 

number of rows impacted by a SQL statement, so

turning SET NOCOUNT ON will not change that behavior.
Not using SET NOCOUNT ON
Here is an example without using SET NOCOUNT ON:

The messages that are returned would be similar to this:

NOCOUNT ON as shown below.  It is a good

practice to put this at the beginning of the stored procedure.

The messages that are returned would be similar to this: