--有输入参数的存储过程--
create proc GetComment
(@commentid int)
as
select * from Comment where CommentID=@commentid
C# 调用有输入参数的存储过程
SqlConnection conn=new SqlConnection("Server=.;Database=MyDB;uid=sa;pwd=123456");
SqlCommand cmd=new SqlCommand();
cmd.Connection=conn;
mandType=CommandType.StoredProcedure;
mandText="GetComment";
cmd.Parameters.Clear();
cmd.Parameters.Add("@commentid",SqlDbType.Int).Value=1;
DataTable dt=new DataTable();
SqlDataAdapter da=new SqlDataAdapter(cmd);
da.Fill(dt);
GridView1.DataSource=dt;
GridView1.DataBind();
--有输入与输出参数的存储过程--
create proc GetCommentCount
@newsid int,
@count int output
as
select @count=count(*) from Comment where NewsID=@newsid
SqlConnection conn=new SqlConnection("Server=.;DataBase=MyDB;uid=sa;pwd=123456");
SqlCommand cmd=new SqlCommand();
conn.Open();
cmd.Connection=conn;
mandType=CommandType.StoredProcedure;
mandText="GetCommentCount";
cmd.Parameters.Clear();
cmd.Parameters.Add("@newsid",SqlDbType.Int).Value=2;
SqlParameter sp=new SqlParameter();
sp.ParameterName="@count";
sp.SqlDbType=SqlDbType.Int;
sp.Direction=ParameterDirection.Output;
cmd.Parameters.Add(sp);
Response.Write(sp.Value.ToString());
--返回单个值的函数--
create function MyFunction
(@newsid int)
returns int
as
begin
declare @count int
select @count=count(*) from Comment where NewsID=@newsid
return @count
end
SqlConnection conn=new SqlConnection("Server=.;Database=MyDB;uid=sa;pwd=123456");
SqlCommand cmd=new SqlCommand();
conn.Open();
cmd.Connection=conn;
mandText="MyFunction";
mandType=CommandType.StoredProcedure; -----这儿要设置为存储过程
cmd.Parameters.Add("@newsid",SqlDbType.Int).Value=2;
SqlParameter sp=new SqlParameter();
sp.ParameterName="@count";
sp.SqlDbType=SqlDbType.Int;
sp.Direction=ParameterDirection.ReturnValue;
cmd.Parameters.Add(sp);
cmd.ExecuteNonQuery();
Response.Write(sp.Value.ToString());
--调用方法--
declare @count int
exec @count=MyFunction 2
print @count
--返回值为表的函数--
Create function GetFunctionTable
(@newsid int)
returns table
as
return
(select * from Comment where NewsID=@newsid)
--返回值为表的函数的调用--
select * from GetFunctionTable(2)
SqlConnection conn=new SqlConnection("Server=.;Database=MyDB;uid=sa;pwd=123456");
SqlCommand cmd=new SqlCommand();
conn.Open();
cmd.Connection=conn;
mandType=CommandType.Text;//注意这儿设置为文本
mandText="Select * from GetFunctionTable(@newsid)";
SqlDataReader dr=cmd.ExecuteReader();
DataTable dt=new DataTable();
dt.Load(dt);
GridView1.DataSource=dt;
GridView1.DataBind();
