stored procedures in SQL Server

Three ways that stored procedures in SQL Server are returned (including the creation of stored procedures, calls in stored procedures, methods called in VS)
There are three types of stored procedures to return:
1. Return numeric data with return
2. Returns the result with the return parameter, can return various data types (through the cursor to iterate the result each row)
3. Directly in the stored procedure with a select to return the result set, can be any SELECT statement, which means that it is arbitrary to return the result set

Method One: Return the numeric data with return
1. Create a stored procedure
--sqlserver 2005 Sample Database
Use AdventureWorks
GO
CREATE PROCEDURE CheckState
@param VARCHAR (11)
As
IF (
SELECT StateProvince
From Person.vadditionalcontactinfo
WHERE ContactID = @param
) = ' WA '
RETURN 1
ELSE
RETURN 2;
GO
2. Calling in a stored procedure
DECLARE @return_status INT;
EXEC @return_status = CheckState ' 9 '; --Assign the stored procedure return value to @return_status
SELECT ' Return Status ' = @return_status;
GO
3. Call in VS
List<dbparameter> para = new list<dbparameter> ();
Para. ADD (New SqlParameter ("@param", 9));
SqlParameter pa = new SqlParameter ();
Pa. ParameterName = "@return";
Pa. SqlDbType = SqlDbType.Int;
Pa. Direction = ParameterDirection.ReturnValue;
Para. ADD (PA);
int i = dbhelper.executesql ("CheckState", CommandType.StoredProcedure, para);
Response.Write (Para[1]. Value.tostring ());


Method Two: Returns the result with the return parameter, can return various data types (through the cursor to iterate the query result each row)
1. Create a stored procedure
--sqlserver 2005 Sample Database
Use AdventureWorks
GO
CREATE PROCEDURE Outputvalue
@param VARCHAR (11),
@param2 VARCHAR (one) OUTPUT
As
IF (
SELECT StateProvince
From Person.vadditionalcontactinfo
WHERE ContactID = @param
) = ' WA '
SET @param2 = ' good '
ELSE
SET @param2 = ' bad '
GO
2. Calling in a stored procedure
DECLARE @param1 NVARCHAR (100)
DECLARE @param2 NVARCHAR (100)
SET @param1 = ' 9 '
EXEC outputvalue ' 9 ', @param2 OUTPUT
SELECT @param2
3. Call in VS
List<dbparameter> para = new list<dbparameter> ();
Para. ADD (New SqlParameter ("@param", "9"));
SqlParameter pa = new SqlParameter ();
Pa. Direction = ParameterDirection.Output;
Pa. ParameterName = "@param2";
Pa. Size = 11;
Para. ADD (PA);
int i = dbhelper.executesql ("Outputvalue", CommandType.StoredProcedure, para);
Output return value
Response.Write (Para[1].  Value.tostring ()); Output return value


Method Three: Directly in the stored procedure with the Select return result set, can be any SELECT statement, which means that it is arbitrary to return the result set
1. Create a stored procedure
--sqlserver 2005 Sample Database
Use AdventureWorks
GO
CREATE PROCEDURE returndatatable
As
BEGIN
SELECT * from Person.vadditionalcontactinfo
END
GO
2. Calling in a stored procedure
EXEC returndatatable
3. Call in VS
Stored procedure Returns a result set that can be stored in a DataTable
DataTable dt = dbhelper.getdatatable ("returndatatable", CommandType.StoredProcedure);

stored procedures in SQL Server