Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I did something like this with a stored procedure once. Basically, I wanted to specify any permutation of fields to match on in my WHERE clause, but I didn't want to write 100 sprocs with slightly different param lists and where clauses.</p> <p>So, I did something like this:</p> <pre><code>CREATE PROCEDURE [GetSimpleCustomers] ( @ID varchar(50) = null, @Name varchar(50) = null, @IsActive bit = null, @Address1 varchar(50) = null, @Address2 varchar(50) = null, @City varchar(50) = null, @State varchar(50) = null, @Zip varchar(50) = null ) AS SELECT ID, Name, IsActive, Address1, Address2, City, State, Zip FROM SimpleCustomerExample WHERE (ID = @ID OR @ID is NULL) AND (Name = @Name OR @Name is NULL) AND (IsActive = @IsActive or @IsActive is NULL) AND (Address1= @Address1 or @Address1 is NULL) AND (Address2= @Address2 or @Address2 is NULL) AND (City= @City or @City is NULL) AND (State= @State or @State is NULL) AND (Zip= @Zip or @Zip is NULL) </code></pre> <p>This will let you call the sproc in your code and only pass the params you are interested in filtering on, and the rest will not be factored in if you leave them null.</p> <p>So, you can do something like </p> <pre><code>public List&lt;SimpleCustomer&gt; GetAllCustomersFromOhio() { List&lt;SimpleCustomer&gt; list = new List&lt;SimpleCustomer&gt;(); using (SqlCommand cmd = new SqlCommand(blah blah)) { cmd.Parameters.AddWithValue("State", "Ohio");//or "OH" depending on your convention using(IDataReader oDR = cmd.ExecuteReader()) { //hydrate your list of SimpleCustomers from the record set. } } return list; } </code></pre> <p>EDIT: In reply to comment: You could easily enough alter the GetSimpleCustomers to be DeleteSimpleCustomers by changing the </p> <pre><code>SELECT &lt;columns&gt; FROM SimpleCustomers </code></pre> <p>to </p> <pre><code>DELETE FROM SimpleCustomers </code></pre> <p>and keep the same logic. The same is true for an Update. Also, I'll answer a question with a question: How many tables do you have that actually need this level of custom filtering? The syntax would be so similar you could pound it all out in a day (or less if you hammered together a simple script to write it for you).</p>
    singulars
    1. This table or related slice is empty.
    1. POADO.net without writing SQL (esp. WHERE)
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAndy_Vulhop
    1. USAndy_Vulhop
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWell, it's an interesting solution if you have one table which is hammered with these type of queries. But would you do that for all tables? Plus, I am merely interested in all them Select/Insert/Delete statements. Thanks.
      singulars
      1. PO
      1. USVlagged
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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