CREATE PROC dbo.JdjcSearch @Wzsz_Cpmc1 varchar(200), @Wzsz_Sjqy1 varchar(200) ASDeclare @Sql nvarchar(1024) Set @Sql = ' SELECT Col001, Col002, Col014 FROM Jdjc where (Col001 is not null) ' IF IsNull(@Wzsz_Cpmc1, '') != '' --將''的情況也加上 begin Set @Sql = @Sql+' and ' Set @Sql = @Sql +' Col002 LIKE ''%'+@Wzsz_Cpmc1+ '%''' ENDIF IsNull(@Wzsz_Sjqy1, '') != '' begin Set @Sql = @Sql+' and ' Set @Sql = @Sql +' Col014 LIKE ''%'+@Wzsz_Sjqy1+ '%''' ENDEXEC sp_executeSql @SqlWITH RECOMPILE GO你傳入的時候,傳入一個空字符串就可以。
default.aspx是一个多条件的搜索框。
当文本框Wzsz_Cpmc1 or Wzsz_Sjqy1 条件,有可能同时满足,有可能任一个潢足,有可能都为空。<form runat="server">
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" CellPadding="4" DataSourceID="SqlDataSource1"
ForeColor="#333333" GridLines="None" Width="559px">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#EFF3FB" />
<EditRowStyle BackColor="#2461BF" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="http://10.4.0.5/zlc/search4.asp?mingc=062272011002" Text="详细内容"></asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SOUCon %>"
SelectCommand="JdjcSearch" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="Wzsz_Cpmc1" QueryStringField="Wzsz_Cpmc1" Type="String" />
<asp:QueryStringParameter Name="Wzsz_Sjqy1" QueryStringField="Wzsz_Sjqy1" Type="String" />
</SelectParameters>
</asp:SqlDataSource></form>
@Wzsz_Cpmc1 varchar(200),
@Wzsz_Sjqy1 varchar(200)
ASDeclare @Sql nvarchar(1024)
Set @Sql = ' SELECT Col001, Col002, Col014 FROM Jdjc where (Col001 is not null) '
IF IsNull(@Wzsz_Cpmc1, '') != '' --將''的情況也加上
begin
Set @Sql = @Sql+' and '
Set @Sql = @Sql +' Col002 LIKE ''%'+@Wzsz_Cpmc1+ '%'''
ENDIF IsNull(@Wzsz_Sjqy1, '') != ''
begin
Set @Sql = @Sql+' and '
Set @Sql = @Sql +' Col014 LIKE ''%'+@Wzsz_Sjqy1+ '%'''
ENDEXEC sp_executeSql @SqlWITH RECOMPILE
GO你傳入的時候,傳入一個空字符串就可以。