存储过程
CREATE PROCEDURE SearchRoomType
-- Add the parameters for the stored procedure here
@RoomTypeID varchar(10),
@TypeName varchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @m_SqlString varchar(8000);
DECLARE @m_RoomTypeID varchar(8000);
DECLARE @m_TypeName varchar(8000);
SET @m_SqlString='SELECT * FROM dbo.RoomType Where TypePrice<>0';
iF(@RoomTypeID<>'')
Begin
Set @m_RoomTypeID =' AND TypeNameID LIKE '+CHAR(40)+'%'+@RoomTypeID+'%'+CHAR(40);
End
IF(@TypeName<>'')
Begin
SET @m_TypeName=' AND TypeName LIKE '+CHAR(40)+'%'+@TypeName+'%'+CHAR(40);
END
EXEC(@m_SqlString+@m_TypeName+@m_RoomTypeID);
END
GO
调用存储过程:
private void SearchRoomType()
{
try
{
string Connstr = "server=.;database=Hostel;uid=sa;pwd=123";
SqlCommand sc = new SqlCommand();
sc.Connection = new SqlConnection(Connstr);
sc.CommandText = "SearchRoomType";
sc.CommandType = CommandType.StoredProcedure;
SqlParameter sp = sc.Parameters.Add("@RoomTypeID", SqlDbType.VarChar, 10);
sp.Value = txtNumber1.Text;
sp = sc.Parameters.Add("@TypeName", SqlDbType.VarChar, 20);
sp.Value = txtName1.Text;
SqlDataAdapter sda = new SqlDataAdapter(sc);
DataSet ds = new DataSet();
sda.Fill(ds,"RoomType");
this.dgRoomTypeList.DataSource = ds.Tables["RoomType"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
这是一个完成搜索功能的存储过程
我在txtNumber1输入编号 或是在txtName1输入名称
都能显示在dgRoomTypeList中 要是不输入 也就是全显示出来了
但是和我想象的不太一样
还有存储过程还是觉得写的有点问题:
SET @m_SqlString='SELECT * FROM dbo.RoomType Where TypePrice<>0';
要是没有TypePrice也为空呢
这里已经不太会写了 要是不按编号搜索 这里可以用编号的
但是现在我要用编号搜索啊
一个问题已经都捆饶我多日了 希望高手给你答复。。。