Question

asp.net 存储过程求助

CREATE PROCEDURE P_Search
@Id int=null,
@Name varchar(50)=null
as
if @Id=0
begin
select * from Services where ServicesName LIKE'% @Name%'
end
else
begin
select * from SkillFiles where S_Title LIKE'% @Name%'
end

GO
这个存储过程怎样调用啊？谢谢大家了，是一个模糊查询的.

Answer 1

public override List<poll> Search(int id,string name)
{
using (SqlConnection cn = new SqlConnection("连接字符串"))
{
SqlCommand cmd = new SqlCommand("存储过程的名字", cn);
//说明是个存储过程
cmd.CommandType = CommandType.StoredProcedure;
//添加参数
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = name;
//打开数据库
cn.Open();

//执行操作
SqlDataReader reader = cmd.ExecuteReader();

List<poll> polls = new List<poll>();

//获取返回的值,存入到集合中
while (reader.Read())
{
polls.Add(new poll((int)reader["列1"]),
(string)reader["列2"],(bool)reader["列3"]);//等等,存储过程里的select * 最好用select 列1，列2..替换
}

return polls;
}
}
///说明
///你的Search返回的结果集，我用List<poll>代替
/// new poll里保存存储过程的返回值 reader["返回结果"];
///

Answer 2

/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程的名称</param>
/// <returns>返回存储过程返回值</returns>
public int RunProc(string procName)
{
if (!this.Open())
return SQL_RET_ERROR;

SqlCommand cmd = this.CreateCommand(procName, null);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
m_strErrorMessage = ex.Message.Replace("\"","").Replace("\r\n","").Replace("'","");
return SQL_RET_ERROR;
}
this.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
}

调用就是RunProc(P_Search )

Answer 3

比如
exec P_Search 5 'zhangsan'
exec P_Search
exec P_Search 'lisi'

Answer 4

人才啊，你都没有从程序中川过来值你还想做什么操作啊