SQL topic------------Stored Procedures

Today to explain SQL Server stored procedures, I generally used to start with a simple example, and then introduce his grammar, and finally to cite some common and typical stored procedures

1. First look at a simple example to form an impression

Suppose there is such a table people

I'm going to write a login stored procedure, let's take a look at verifying the login SQL statement, very simple select count (*) from people where username= ' xxxx ' and password= ' xxxx ' If the result is greater than 1, To prove that the user exists, otherwise the user does not exist

Here's the log-in stored procedure.

1 Create procProc_login2 (3     @name varchar( -),4     @pwd varchar( -)5 )6  as 7 Declare  @count int8 begin9     Ten    Set @count=(Select Count(*) fromDbo. PeoplewhereUserName=@name  andPassword=@pwd) One  A     if @count>0 -         Print 'Success'     -     Else the         Print 'failed' - END

This is the simplest stored procedure, but it contains a lot of things.

First of all, look at the syntax of creating stored procedures, I do not remember this syntax, in fact, the syntax to create a stored procedure is quite simple

Create proc Here write the stored procedure name created

(

Write the parameters here

The name of the parameter must be prefixed with the @ symbol, e.g.

@name varchar (20),

@pwd varchar (20)

)

As

Write the defined variables here, or you can write the SQL statements

Notice the syntax of the stored procedure, and it's over here.

The declare of the back, the begin, not the necessary syntax for creating stored procedures

Take a look at the written definition

1 Create proc | procedurePro_name2(
@ parameter data type [= default value] [Output],3      @ parameter data type [=Default Value] [Output],      )6  as7Sql_statements

Take a look at the stored procedure without parameters

Let's look at an example.

1 CREATE PROC Proc_selectall 2  as 3 SELECT *  from dbo. People

Then the execution

1 EXEC Proc_selectall

What, is it not simple, this is a stored procedure

Here's a look at the written definition

1 Create proc | procedure Pro_name 2  as 3     Sql_statements

Discover no, create a stored procedure you just need to remember a create, a proc, an as can be

Definition and modification process see this article http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html

The following is a brief description of the stored procedures, there are two types of stored procedures, one is the system stored procedures, a user-defined stored procedures

system stored Procedures I use more of the renamed stored procedures

--Table Renamingexecsp_rename'Stu','Stud';Select *  fromStud;--Rename namingexecsp_rename'Stud.name','SName','column';execSp_help'Stud';

Summary of common stored procedures

1. Paging Stored Procedures

First of all to introduce the implementation of the paging stored procedures, the paging stored procedures have n implementation methods, this article in the future must collect as much as possible

First of all, using select top and not in to implement

Let's take a look at this SQL statement select Top * from people where people.id not in (select Top (+) people.id from people)

First , select Top (people.id) from people

This is a good understanding, that is, to filter out the first 100 IDs from the People table, which temporarily excludes the ordering of these factors

The second sentence select top * from people where people.id not in (...)

This sentence is filtered from the people table with the first 10 IDs not in (...) The ID in

First filter out the first 100id, and then filter out the first 10 not the first 100 IDs, in fact, the implementation of filtering 第101-110个 elements, which is the use of select top and not to implement the principle of paging stored procedures. That's the truth, but it's a bit of a struggle to actually write this stored procedure, and we'll do it at 1.1 next.