Understanding SQL stored procedures and SQL

Understanding SQL stored procedures and SQL

Stored Procedure is a set of compiled T-SQL statements, can be called at any time, fast, easy to error.

Parameters, common parameters, and output parameters can be passed)

Instance 1

CreateProc Newpro

@ TestVarA int,

@ TestVatB int,

@ TestSum int Output

As

Begin

Set@ TestSum = @ testVarA + @ testVarB

End

Call Stored Procedure Newpro

Declare@ TestA int

ExecuteNewpro 100,200, @ testA output

Print @ testA

 

Instance 2

CreateProc testUser

@ TestUserName varchar (30 ),

@ TestPassWord varchar (30)

As

Begin

Declare@ TestMsg varchar (100)

If @ testUserName = 'user1'

Begin

If @ testPassWord = '000000'

Set @ testMsg = 'Welcome to start'

Else

Set @ testMsg = 'Sorry, the password is incorrect'

End

Else if @ testUserName = 'user2'

Begin

If @ testPassWord = 'abc'

Set @ testMsg = 'Welcome to start'

Else

Set @ testMsg = 'Sorry, the password is incorrect'

End

Else

Set @ testMag = 'Enter the correct username'

Print @ testMsg

End

Call Stored ProcedureTestUser

ExecTestUser 'user1', '123'

 

SQL review knowledge points

There are many rumors about the new SQL Server 2005. For this undecided release, the following lists some of its important functions. You can use SQL Server in the near future.

At the June TechEd 2005 Conference, it was Microsoft's harvest: although the product release date was not announced, many people are very concerned about Microsoft's new products. We have been waiting for the new SQL Server for a long time. In this upgraded version of database technology, Microsoft provides the features we want. Here are some of the most useful features we have found.

1. XML Data Type
If SQL Server 2005 has any function that deserves everyone's cheers, it should be a new local XML data type. Why? In addition to the significant progress in Web friendliness, it also provides us with more design options (which are not generally supported by Microsoft ), it brings many aspects to our design.

New XML data type:

Can be used as a column in a table.
It can be used as a parameter or variable in the storage program.
It can store non-standard data.

Check the plan to check whether the data stored in the volume matches the XML of the relevant plan (if there is no plan, the data will be considered unclassified)
The ing between XML data and related data is bidirectional.

2. DMO (Distributed Management Objects) is changed to SMO (SQL Server Management Objects)
SQL Server Management Objects (SMO, SQL Server Management object) is a. NET-based Management architecture that allows you to create personalized applications for Server Management. SMO (same as DMO) allows you to process columns, worksheets, databases, and servers as objects. SMO supports new functions of SQL Server 2005, such as Service Broker.

SMO is optimized. Unless the object is explicitly involved, it is not completely an example object (all attributes are reset ). You can also use SQL commands to create scripts to create objects in batches. You can also define the Server management applications used to manage SQL Server 7 in SQL Server 2000.

3. CTEs (Common Table Expresssions) supports recursive queries
CTE supports recursive queries. The CTE can be self-directed and has a ceiling on intrusion. You can use the CTE as a command such as WITH, SELECT, UPDATE, INSERT, or DELETE.

4. Service Broker helps SQL Server Implement Asynchronous Communication
This is a front-end system that changes everything. Now you can use the new Service Broker function to manage SQL Server Communication asynchronously. By helping your system process more communication logically than physically, Service Broker improves performance. The Service Broker can be accessed through SQL commands and can process queued events.

This feature impressed me so much that I had to emphasize it again and again. Adding an easy-to-configure synchronization function to the data layer of an enterprise system is a good news for developers and brings a lot of possibilities to network applications. This kind of application can bring great economic benefits. Service Broker itself is a good reason to upgrade to SQL Server 2005.

5. Create a. NET trigger event
In SQL Server 2005. NET integration to a certain extent, a very useful result of this integration is that in Visual Studio 2005, users can define the trigger event (user-defined triggers, UDTs ).

You can find the trigger event option in the template list of Visual Studio to generate a file to trigger code. SqlPipe is used to try this code in SOL. You can go to "Build" |... the remaining full text>

The "study" in "learning and learning" is similar to the "warm" in "learning and learning". It means that you should review your studies.

The two words have similar meanings. 1 "learning and learning" focuses on learning knowledge methods. 2. "Learn from the new" emphasizes a studious attitude and warns people not to satisfy the current situation. While reviewing old knowledge, we also need to understand new knowledge.