心情

Create Procedure TestProcedure
(
@au_idIN varchar (11),
@numTitlesOUT Integer OUTPUT
)
AS
select A.au_fname, A.au_lname, T.title
from authors as A join titleauthor as TA on
A.au_id=TA.au_id
join titles as T
on T.title_id=TA.title_id
where [url=mailto:A.au_id=@au_idIN]A.au_id=@au_idIN
set @numTitlesOUT = @@Rowcount
return (5)
SqlConnection PubsConn = new SqlConnection ("Data Source=server;integrated " + "Security=sspi;initial catalog=pubs;");
SqlCommand testCMD = new SqlCommand ("TestProcedure", PubsConn);
testCMD.CommandType = CommandType.StoredProcedure;
SqlParameter RetVal = testCMD.Parameters.Add ("RetVal", SqlDbType.Int);
RetVal.Direction = ParameterDirection.ReturnValue;
SqlParameter IdIn = testCMD.Parameters.Add ("@au_idIN", SqlDbType.VarChar, 11);
IdIn.Direction = ParameterDirection.Input;
SqlParameter NumTitles = testCMD.Parameters.Add ("@numtitlesout", SqlDbType.VarChar, 11);
NumTitles.Direction = ParameterDirection.Output ;
   
IdIn.Value = "213-46-8915";
PubsConn.Open();
SqlDataReader myReader = testCMD.ExecuteReader();
Console.WriteLine ("Book Titles for this Author:");
while (myReader.Read())
  {
  Console.WriteLine ("{0}", myReader.GetString (2));
  };
myReader.Close() ;
Console.WriteLine("Number of Rows: " + NumTitles.Value );
Console.WriteLine("Return Value: " + RetVal.Value);


Create Procedure TestProcedure2
(
@au_idIN varchar (11)
)
As
/* set nocount on */
select count (T.title)
from authors as A join titleauthor as TA on
A.au_id=TA.au_id
join titles as T
on T.title_id=TA.title_id
where [url=mailto:A.au_id=@au_idIN]A.au_id=@au_idIN
Return(5)

string strCount;
SqlConnection PubsConn = new SqlConnection ("Data Source=server;integrated " + "Security=sspi;initial catalog=pubs;");
SqlCommand testCMD = new SqlCommand ("TestProcedure2", PubsConn);
testCMD.CommandType = CommandType.StoredProcedure;
SqlParameter RetVal = testCMD.Parameters.Add ("RetVal", SqlDbType.Int);
RetVal.Direction = ParameterDirection.ReturnValue;
SqlParameter IdIn = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11);
IdIn.Direction = ParameterDirection.Input;
   
IdIn.Value = "213-46-8915";
PubsConn.Open();
strCount =testCMD.ExecuteScalar ().ToString() ;
Console.WriteLine("Number of Rows: " + strCount );
Console.WriteLine("Return Value: " + RetVal.Value);