Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>What you're looking for is a <a href="http://connectsql.blogspot.com/2011/05/sql-server-conditional-where-clause.html" rel="nofollow">conditional where clause</a>.The stored procedure I've created will return data when:</p> <ol> <li>Only last name is supplied</li> <li>Both first and last name are supplied</li> <li>First name, last name and middle initials are supplied</li> </ol> <p>If you need to satisfy any other conditions just amend the stored procedure. The reason I've created a stored proc is because the query is substantially bigger now then the one you've started with:</p> <p><strong>Firstly create this stored procedure in SQL:</strong></p> <pre><code>CREATE PROCEDURE [dbo].[SearchUsers] @UserLastName VARCHAR(MAX), @UserFirstName VARCHAR(MAX) = 'NOVALUE', @UserMiddleInitial VARCHAR(MAX) = 'NOVALUE' AS BEGIN SELECT [UserID], [UserSuffix], [UserAddressLine1], [UserAddressLine2], [UserCity], [UserState], [UserZipCode], [UserPhoneNumber], [UserEmail] FROM [AllUsers] WHERE 1 = (CASE WHEN @UserFirstName &lt;&gt; 'NOVALUE' AND @UserMiddleInitial &lt;&gt; 'NOVALUE' AND UserFirstName = @UserFirstName AND UserLastName = @UserLastName AND UserMiddleInitial = @UserMiddleInitial THEN 1 WHEN @UserFirstName &lt;&gt; 'NOVALUE' AND @UserMiddleInitial = 'NOVALUE' AND UserFirstName = @UserFirstName AND UserLastName = @UserLastName THEN 1 WHEN @UserFirstName = 'NOVALUE' AND @UserMiddleInitial = 'NOVALUE' AND UserLastName = @UserLastName THEN 1 ELSE 0 END) END </code></pre> <p><strong>ASPX:</strong></p> <pre><code>&lt;form id="form1" runat="server"&gt; &lt;asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="&lt;%$ ConnectionStrings:WildLife_EducationConnectionString %&gt;" SelectCommand="SearchUsers" SelectCommandType="StoredProcedure"&gt; &lt;SelectParameters&gt; &lt;asp:ControlParameter ControlID="txtUILastName" Name="UserLastName" PropertyName="Text" Type="String" /&gt; &lt;asp:ControlParameter ControlID="txtUIFirstName" DefaultValue="NOVALUE" Name="UserFirstName" PropertyName="Text" Type="String" /&gt; &lt;asp:ControlParameter ControlID="txtUIMI" DefaultValue="NOVALUE" Name="UserMiddleInitial" PropertyName="Text" Type="String" /&gt; &lt;/SelectParameters&gt; &lt;/asp:SqlDataSource&gt; &lt;table id="USERINF"&gt; &lt;tr&gt; &lt;td width="196px" class="style8"&gt; &lt;asp:Label ID="lblFirstName" runat="server" Text="Please enter First Name:"&gt;&lt;/asp:Label&gt; &lt;/td&gt; &lt;td class="style6"&gt; &lt;asp:TextBox ID="txtUIFirstName" runat="server" MaxLength="20"&gt;&lt;/asp:TextBox&gt; &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td width="196px" class="style8"&gt; &lt;asp:Label ID="lblLastName" runat="server" Text="Please enter Last Name:"&gt;&lt;/asp:Label&gt; &lt;/td&gt; &lt;td class="style6"&gt; &lt;asp:TextBox ID="txtUILastName" runat="server" MaxLength="20"&gt;&lt;/asp:TextBox&gt; &lt;asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ControlToValidate="txtUILastName" ValidationExpression="[a-zA-Z ]{1,20}" ForeColor="Red" ValidationGroup="UserInf" ErrorMessage="First Name must be only a-z,A-Z characters!" Display="Dynamic"&gt;&lt;/asp:RegularExpressionValidator&gt; &lt;asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtUILastName" ErrorMessage="Please enter Last Name!" ForeColor="Red" ValidationGroup="UserInf" Display="Dynamic"&gt;&lt;/asp:RequiredFieldValidator&gt; &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td width="196px" class="style8"&gt; &lt;asp:Label ID="lblMI" runat="server" Text="Please enter Middle Initials:"&gt;&lt;/asp:Label&gt; &lt;/td&gt; &lt;td class="style6"&gt; &lt;asp:TextBox ID="txtUIMI" runat="server" MaxLength="1" Width="30px" Style="text-align: center; margin-left: 2px"&gt;&lt;/asp:TextBox&gt; &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td width="196px" class="style8"&gt; &lt;/td&gt; &lt;td class="style6"&gt; &lt;asp:Button ID="btnIFind" runat="server" Text="Find" ValidationGroup="UserInf" /&gt; &lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;asp:ListView ID="listView" runat="server" DataSourceID="SqlDataSource1"&gt; &lt;ItemTemplate&gt; &lt;tr&gt; &lt;td&gt; &lt;asp:Label ID="UserCityLabel" runat="server" Text='&lt;%# Eval("UserCity") %&gt;' /&gt; &lt;/td&gt; &lt;td&gt; &lt;asp:Label ID="UserEmailLabel" runat="server" Text='&lt;%# Eval("UserEmail") %&gt;' /&gt; &lt;/td&gt; &lt;/tr&gt; &lt;/ItemTemplate&gt; &lt;/asp:ListView&gt; &lt;/form&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload