存储过程
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也为空呢
这里已经不太会写了 要是不按编号搜索 这里可以用编号的
但是现在我要用编号搜索啊

一个问题已经都捆饶我多日了 希望高手给你答复。。。

[解决办法]
你的存储过程总是看的有点怪异，而且数据库里判断为空是 is null


[解决办法]
使用动态的SQL即可

[解决办法]
例如

CREATE PROCEDURE [dbo].[pro_T_Common_ApplyType_SelectDynamic]
@WhereCondition nvarchar(500),
@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT
[ApplyType],
[ApplyTypeName]
FROM
[dbo].[T_Common_ApplyType]
WHERE
' + @WhereCondition

IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
SET @SQL = @SQL + '
ORDER BY
' + @OrderByExpression
END

EXEC sp_executesql @SQL

[解决办法]
SET @m_SqlString= 'SELECT * FROM dbo.RoomType Where 1=1 '这样跟据条件一个一个的连接上去，即可？保证了，前面是否有选择条件。