Asp.net mssql stored procedure output parameter call

Asp tutorial. net mssql stored procedure output parameter call

Simplest syntax

Create proc p
As
Select * from tb

Concepts, advantages, and syntax of SQL Stored Procedures

Before learning the program process, let's first understand what a stored procedure is? What advantages does stored procedure have?

Definition: Store common or complex tasks in advance with SQL statements and a specified name, when you call the database tutorial to provide services with the same functions as the predefined stored procedure, you only need to call execute to automatically complete the command.

At this point, someone may ask: is the stored procedure just a bunch of SQL statements? Why does microsoft add this technology?

What is the difference between a stored procedure and a general SQL statement?

Advantages of stored procedures:

1. the stored procedure is compiled only when it is created. You do not need to re-compile the stored procedure every time you execute it. Generally, the SQL statement is compiled every time it is executed, therefore, using stored procedures can speed up database execution.

2. when performing complex operations on the database (for example, performing update, insert, query, and delete operations on multiple tables ), this complex operation can be encapsulated in a stored procedure and used together with the transaction processing provided by the database.

3. The stored procedure can be reused to reduce the workload of database developers.

4. High security. You can set that only one user has the right to use the specified stored procedure.

Types of stored procedures:

1. System stored procedure: starts with sp _. It is used to set the system, obtain information, and manage the system,

For example, sp_help is used to obtain information about the specified object.

2. The extended stored procedure starts with xp _ and is used to call the functions provided by the operating system.

Reference content is as follows:

Exec master .. xp_mongoshell 'ping 10.8.16.1'

3. User-defined stored procedures, which we refer to as stored procedures

Common formats

Reference content is as follows:

Create procedure procedue_name

[@ Parameter data_type] [output]

[With] {recompile | encryption}

As

SQL _statement

Explanation:

Output: indicates that this parameter can be returned.

With {recompile | encryption}

Recompile: indicates re-compiling every time this stored procedure is executed.

Encryption: the content of the created stored procedure is encrypted.

II. SQL Stored procedure learning: Creating a stored procedure

The table book content is as follows:

Title price

001 C language entry $30

002 powerbuilder report development $52

Example 1: query the stored procedure of the table book content

Create proc query_book
As
Select * from book
Go
Exec query_book

Example 2: Add a record to the table book and query the total amount of all books in the table

Create proc insert_book
@ Param1 char (10), @ param2 varchar (20), @ param3 money, @ param4 money output
With encryption --------- encryption
As
Insert book (number, title, Price) values (@ param1, @ param2, @ param3)
Select @ param4 = sum (price) from book
Go
Example:

Declare @ total_price money
Exec insert_book '003 ', 'Delphi control development Guide', $100, @ total_price
Print 'total amount is '+ convert (varchar, @ total_price)
Go

Three types of stored procedure return values:

1. return an integer with return

2. Return parameters in output format

3. recordset

Differences between return values:

Both output and return can be received using variables in a batch program, while recordset is passed back to the client that executes the batch.

Next, let's take a look at the application example.

The stored procedure is as follows:

Create proc [dbo]. [exchange_userfinance]
@ Uid int,
@ Utype int,
@ Uamount decimal (9, 2 ),
@ Uafterremainamount decimal (9, 2 ),
@ Uopid int,
@ Utext varchar (200 ),
@ Unote1 varchar (200 ),
@ Unote2 varchar (200 ),
@ Unote3 varchar (200 ),
@ Uremainamount decimal (9, 2) output

As
Declare @ utime datetime
Set @ utime = getdate ()
Set @ uremainamount = 0.00
Begin
Begin tran
Insert into [ip_userfinance] (
[Uid], [utime], [utype], [uamount], [uafterremainamount], [uopid], [utext], [unote1], [unote2], [unote3]
) Values (@ uid, @ utime, @ utype, @ uamount, @ uafterremainamount, @ uopid, @ utext, @ unote1, @ unote2, @ unote3)
If @ rowcount <> 1
Begin
Rollback tran
Set @ uremainamount = 0
Return
End
Update [ip_userremainamount] set [uremainamount] = @ uafterremainamount, [utime] = @ utime where uid = @ uid
If @ rowcount <> 1
Begin
Rollback tran
Set @ uremainamount = 0
Return
End
Commit tran
Set @ uremainamount = (select uremainamount from ip_userremainamount where uid = @ uid)
End

. Net call

Public static decimal modifyofficeoragentfinance (ip_userfinance agentmodel)
        {

Sqlparameter [] parms = new sqlparameter []
                        {
New sqlparameter ("@ uid", sqldbtype.int, 4 ),
                        
};
Parms [0]. value = agentmodel. uid;
Object remainagent = null;
Try
            {
Remainagent = sqlhelper.exe cutescalar (sqlhelper. connectionstring, commandtype. storedprocedure, "wsselectip_userinfofinanceone", parms );
If (remainagent = null)
                {
Return-1;
                }

} Catch (exception)
            {
Return 0;
            }
Sqlparameter [] parameters = {
New sqlparameter ("@ uid", sqldbtype.int, 4 ),
New sqlparameter ("@ utype", sqldbtype.int ),
New sqlparameter ("@ uamount", sqldbtype. decimal, 9 ),
New sqlparameter ("@ uafterremainamount", sqldbtype. decimal, 9 ),
New sqlparameter ("@ uopid", sqldbtype.int, 4 ),
New sqlparameter ("@ utext", sqldbtype. varchar, 200 ),
New sqlparameter ("@ unote1", sqldbtype. varchar, 200 ),
New sqlparameter ("@ unote2", sqldbtype. varchar, 200 ),
New sqlparameter ("@ unote3", sqldbtype. varchar, 200 ),
New sqlparameter ("@ uremainamount", sqldbtype. decimal )};

If (convert. todecimal (remainagent) <agentmodel. uamount)
            {
Return-2;
            }

Parameters [0]. value = agentmodel. uid;
Parameters [1]. value = agentmodel. utype;
Parameters [2]. value = agentmodel. uamount;
Parameters [3]. value = decimal. add (convert. todecimal (remainagent), agentmodel. uamount );
Parameters [4]. value = agentmodel. uopid;
Parameters [5]. value = agentmodel. utext;
Parameters [6]. value = agentmodel. unote1;
Parameters [7]. value = agentmodel. unote2;
Parameters [8]. value = agentmodel. unote3;
Parameters [9]. direction = parameterdirection. output;

Try
            {
Convert.todecimal(sqlhelper.exe cutescalar (sqlhelper. connectionstring, commandtype. storedprocedure, "exchange_userfinance", parameters ));
Return convert. todecimal (parameters [9]. value );

            }
Catch
            {
Return 0.00 m;
            }

        }