sqlserver创建存储过程、函数、

--有输入参数的存储过程--

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;

cmd.CommandType=CommandType.StoredProcedure;

cmd.CommandText="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;

cmd.CommandType=CommandType.StoredProcedure;

cmd.CommandText="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;

cmd.CommandText="MyFunction";

cmd.CommandType=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;

cmd.CommandType=CommandType.Text;//注意这儿设置为文本

cmd.CommandText="Select * from GetFunctionTable(@newsid)";

SqlDataReader dr=cmd.ExecuteReader();

DataTable dt=new DataTable();

dt.Load(dt);

GridView1.DataSource=dt;

GridView1.DataBind();