mssql Stored Procedure output parameter call

Source: Internet
Author: User
Tags utext

Asp tutorial. net mssql Stored Procedure output parameter call

Simplest syntax

Create proc p
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'

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}


SQL _statement


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
Select * from book
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
Insert book (number, title, price) values (@ param1, @ param2, @ param3)
Select @ param4 = sum (price) from book

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

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

Declare @ utime datetime
Set @ utime = getdate ()
Set @ uremainamount = 0.00
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
Rollback tran
Set @ uremainamount = 0
Update [ip_userremainamount] set [uremainamount] = @ uafterremainamount, [utime] = @ utime where uid = @ uid
If @ rowcount <> 1
Rollback tran
Set @ uremainamount = 0
Commit tran
Set @ uremainamount = (select uremainamount from ip_userremainamount where uid = @ uid)

. Net call

Public static decimal modifyofficeoragentfinance (ip_userfinance agentmodel)

Sqlparameter [] parms = new sqlparameter []
New sqlparameter ("@ uid",, 4 ),

Parms [0]. value = agentmodel. uid;
Object remainagent = null;
Remainagent = sqlhelper.exe cutescalar (sqlhelper. connectionstring, commandtype. storedprocedure, "wsselectip_userinfofinanceone", parms );
If (remainagent = null)

} Catch (exception)
Return 0;
Sqlparameter [] parameters = {
New sqlparameter ("@ uid",, 4 ),
New sqlparameter ("@ utype", ),
New sqlparameter ("@ uamount", sqldbtype. decimal, 9 ),
New sqlparameter ("@ uafterremainamount", sqldbtype. decimal, 9 ),
New sqlparameter ("@ uopid",, 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)

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;

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

Return 0.00 m;



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.