MS-SQL Stored Procedure usage (Collection)

I. Differences between return and output in Stored Procedures
<1> commonalities: All return values (but return can only return int type, output can be custom type, and then need to be defined as the variable of the stored procedure, such as @ out varchar output)
<2> Differences:

1. output is the definition variable.
2. The output does not return any work that exits unconditionally from the query or process.
3. the return value does not need to be defined by output when defining functions and procedures.

2. How can I obtain the query records of a stored procedure in Asp.net?

(1) execute a stored procedure without Parameters Code As follows:
Sqlconnection conn = new sqlconnection ("connectionstring ");
Sqldataadapter da = new sqldataadapter ();
Da. selectcommand = new sqlcommand ();
Da. selectcommand. Connection = conn;
Da. selectcommand. commandtext = "nameofprocedure ";
Da. selectcommand. commandtype = commandtype. storedprocedure;

(2) The code for executing a stored procedure with parameters is as follows:
Sqlconnection conn = new sqlconnection ("connectionstring ");
Sqldataadapter da = new sqldataadapter ();
Da. selectcommand = new sqlcommand ();
Da. selectcommand. Connection = conn;
Da. selectcommand. commandtext = "nameofprocedure ";
Da. selectcommand. commandtype = commandtype. storedprocedure;
To add the input parameter @ username:
Param = new sqlparameter ("@ username", sqldbtype. datetime );
Param. Direction = parameterdirection. input;
Param. value = username;
Da. selectcommand. Parameters. Add (PARAM );

To add the output parameter @ out:
Param = new sqlparameter ("@ out", sqldbtype. datetime );
Param. Direction = parameterdirection. output; 'output parameter
Execute the query statement comm. executenonquery ();
Int out = Param. value;

To obtain the return value of the parameter store process: @ return_value is the default return parameter.
Param = new sqlparameter ("@ return_value", sqldbtype. datetime );
Param. Direction = parameterdirection. returnvalue; 'return value:

Execute the query statement comm. executenonquery ();
Int out = Param. value; // out indicates the returned parameter value.

======================

Iii. Example
Example 1: The following Stored Procedure implements the user verification function. If it fails, 0 is returned, and 1 is returned if it succeeds.

Create procedure validate @ username char (20), @ password char (20), @ legal bit output
As

If exists (select * from Ren where sname = @ username and Pwd = @ password)
Select @ legal = 1
Else
Select @ legal = 0

InProgramAnd determine whether the user is valid based on the value of @ legal.

Main Code for displaying the return value in. net
Comm. Parameters. Add ("@ legal", 0 );
Comm. Parameters ["@ legal"]. Direction = parameterdirection. output;
Comm. executenonquery (); // executenonquery: returns the number of affected rows.
I = (INT) Comm. Parameters ["@ legal"]. value;
Response. Write (I. tostring ());

Example 2: return the original code of the output parameters and return values of the stored procedure

Alter procedure SP_2
@ P int output,
@ P1 int,
@ P2 int
As
Set @ P = @ P1 + @ p2
Return 99

sqlcommand cmd = new sqlcommand ();
cmd. connection = conn;
cmd. commandtext = "SP_2";
cmd. commandtype = commandtype. storedprocedure;
'@ return_value "this parameter is at the beginning of the input parameter and the name is fixed
sqlparameter Param = cmd. createparameter ("@ return_value", adinteger, adparamreturnvalue);
cmd. parameters. add (PARAM);
'input and output parameter sequence is the same as that of Stored Procedure Parameter sequence
param = cmd. createparameter ("@ P", adinteger, adparamoutput);
cmd. parameters. add (PARAM);
param = cmd. createparameter ("@ p1", adinteger, adparaminput, 1)
cmd. parameters. add (PARAM);
param = cmd. createparameter ("@ p2", adinteger, adparaminput, 2)

Cmd. executenonquery ();
For I = 0 to cmd. Parameters. Count-1
For (int I; I <cmd. Parameters. Count-1, I ++)
Response. Write (CMD. parameters (I). Name & "=" & cmd. parameters (I). value)
Cmd. Connection. Close ();

Result:
@ Return_value = 99
@ P = 3
@ P2 = 2
@ P1 = 1

4. How to call a stored procedure in a stored procedure?

Create Table Test (ID int, name varchar (10 ))
Insert into test select 1, 'aaa'
Insert into test select 2, 'bbbbbb'
Go
Create procedure sp_test1 (@ count int output)
As
Select @ COUNT = count (*) from test
Go
Create procedure sp_test2
As
Begin
Declare @ count int
Exec sp_test1 @ count output
Select @ count
End
Go
Exec sp_test2
Go
-- Output result
/*
2
*/