创建无输入输出参数的存储过程
create
procedure
testProc
as
select pub_id,title_id,price,pubdate
from titles
where price is not null
order by pub_id
使用下面的程序来调用这个存储过程。在调用该存储过程时,需要告诉Command对象要调用的是存储过程。
as
select pub_id,title_id,price,pubdate
from titles
where price is not null
order by pub_id
<
form id
=
"
form1
"
runat
=
"
server
"
>
< div >
< asp:DataGrid ID = " dg " runat = " server " />
</ div >
</ form >
SqlConnection Conn;
protected void Page_Load( object sender, EventArgs e)
{
Conn = new SqlConnection( " server=localhost;database=pubs;uid=sa;pwd='' " );
SqlCommand Comm = new SqlCommand( " testProc " , Conn);
Comm.CommandType = CommandType.StoredProcedure;
Conn.Open();
SqlDataReader dr = Comm.ExecuteReader();
dg.DataSource = dr;
dg.DataBind();
Conn.Close();
}
< div >
< asp:DataGrid ID = " dg " runat = " server " />
</ div >
</ form >
SqlConnection Conn;
protected void Page_Load( object sender, EventArgs e)
{
Conn = new SqlConnection( " server=localhost;database=pubs;uid=sa;pwd='' " );
SqlCommand Comm = new SqlCommand( " testProc " , Conn);
Comm.CommandType = CommandType.StoredProcedure;
Conn.Open();
SqlDataReader dr = Comm.ExecuteReader();
dg.DataSource = dr;
dg.DataBind();
Conn.Close();
}
2、调用带输入输出参数的存储过程
CREATE
PROCEDURE
sp_CheckPass
( @CHKName VARCHAR ( 30 ), @CHKPass VARCHAR ( 30 ), @ISValid VARCHAR ( 12 ) OUTPUT)
AS
IF EXISTS ( SELECT UserName from WebUsers WHERE UserName = @CHKName and
UserPass = @CHKPass )
SELECT @ISValid = ' GOOD '
ELSE
SELECT @ISValid = ' BAD '
( @CHKName VARCHAR ( 30 ), @CHKPass VARCHAR ( 30 ), @ISValid VARCHAR ( 12 ) OUTPUT)
AS
IF EXISTS ( SELECT UserName from WebUsers WHERE UserName = @CHKName and
UserPass = @CHKPass )
SELECT @ISValid = ' GOOD '
ELSE
SELECT @ISValid = ' BAD '
SqlConnection Conn;
protected void Page_Load( object sender, EventArgs e)
{
Conn = new SqlConnection( " server=localhost;database=pubs;uid=sa;pwd='' " );
SqlCommand Comm = new SqlCommand( " sp_CheckPass " , Conn);
Comm.CommandType = CommandType.StoredProcedure;
SqlParameter parm = Comm.Parameters.Add( " @CHKName " , SqlDbType.VarChar, 30 );
parm.Value = " aa " ;
parm = Comm.Parameters.Add( " @CHKPass " , SqlDbType.VarChar, 30 );
parm.Value = " aa " ;
parm = Comm.Parameters.Add( " @ISValid " , SqlDbType.VarChar, 12 );
parm.Direction = ParameterDirection.Output;
Conn.Open();
SqlDataReader dr = Comm.ExecuteReader();
Response.Write(Comm.Parameters[ " @ISValid " ].Value);
Conn.Close();
}
protected void Page_Load( object sender, EventArgs e)
{
Conn = new SqlConnection( " server=localhost;database=pubs;uid=sa;pwd='' " );
SqlCommand Comm = new SqlCommand( " sp_CheckPass " , Conn);
Comm.CommandType = CommandType.StoredProcedure;
SqlParameter parm = Comm.Parameters.Add( " @CHKName " , SqlDbType.VarChar, 30 );
parm.Value = " aa " ;
parm = Comm.Parameters.Add( " @CHKPass " , SqlDbType.VarChar, 30 );
parm.Value = " aa " ;
parm = Comm.Parameters.Add( " @ISValid " , SqlDbType.VarChar, 12 );
parm.Direction = ParameterDirection.Output;
Conn.Open();
SqlDataReader dr = Comm.ExecuteReader();
Response.Write(Comm.Parameters[ " @ISValid " ].Value);
Conn.Close();
}