Databases
StackOverflow2013
Postsdbo
POMy query filtering is not working the way I want it to
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMy query filtering is not working the way I want it to
    primarykey
    data
    text
    <p>I have 3 ways I want to filter: </p> <ol> <li>by name </li> <li>by list</li> <li>and show all</li> </ol> <p>I'm using ASP.NET 3.5 and SQL Server 2008. Using ADO.NET and stored procs. </p> <p>I'm passing my list as a table valued parameter (but I'm testing with a table variable) and the name as a nvarchar. I have "show all" as ISNULL(@var, column) = column. Obviously the way I'm querying this is not taking advantage of short circuiting or my understanding of how WHERE clauses work is lacking. What's happening is if I make @var = 'some string' and insert a null to the table variable, then it filters correctly. If I make @var = null and insert 'some string' to the table variable, then I get every record, where I should be getting 'some string'.</p> <p>The code:</p> <pre><code>declare @resp1 nvarchar(32) set @resp1 = null declare @usersTable table (responsible nvarchar(32)) --insert into @usersTable (responsible) values (null) insert into @usersTable (responsible) values ('ssimpson') insert into @usersTable (responsible) values ('kwilcox') select uT.responsible, jsq.jobnumber, jsq.qid, aq.question, aq.section, aq.seq, answers.* from answers inner join jobno_specific_questions as jsq on answers.jqid = jsq.jqid inner join apqp_questions as aq on jsq.qid = aq.qid left join @usersTable as uT on uT.responsible = answers.responsible where answers.taskAction = 1 and (uT.responsible is not null or ISNULL(@resp1, Answers.responsible) = Answers.responsible) order by aq.section, jsq.jobnumber, answers.priority, aq.seq </code></pre> <p>This is what I've come up with. It's ugly though....</p> <pre><code>declare @resp1 nvarchar(32) set @resp1 = 'rrox' declare @filterPick int declare @usersTable table (responsible nvarchar(32)) insert into @usersTable (responsible) values (null) --insert into @usersTable (responsible) values ('ssimpson') --insert into @usersTable (responsible) values ('kwilcox') if @resp1 is null begin set @filterPick = 2 end else begin set @filterPick = 1 end select uT.responsible, jsq.jobnumber, jsq.qid, aq.question, aq.section, aq.seq, answers.* from answers inner join jobno_specific_questions as jsq on answers.jqid = jsq.jqid inner join apqp_questions as aq on jsq.qid = aq.qid left join @usersTable as uT on uT.responsible = answers.responsible where answers.taskAction = 1 and (case when uT.responsible is not null then 2 when ISNULL(@resp1, Answers.responsible) = Answers.responsible then 1 end = @filterPick ) order by aq.section, jsq.jobnumber, answers.priority, aq.seq </code></pre> <p>Ok. I think I've got it. I've removed @resp1 because it wasn't necessary and am just using the table valued parameter @usersTable (but here I'm using a table variable for testing). I've added a flag @filterPick so I can show only values in @usersTable or every record where answers.taskAction = 1. </p> <p>The code:</p> <pre><code>declare @filterPick bit declare @usersTable table (responsible nvarchar(32)) insert into @usersTable (responsible) values (null) --insert into @usersTable (responsible) values ('ssimpson') --insert into @usersTable (responsible) values ('kwilcox') if exists (select * from @usersTable where responsible is not null) begin set @filterPick = 1 end else begin set @filterPick = 0 end select * from answers inner join jobno_specific_questions as jsq on answers.jqid = jsq.jqid inner join apqp_questions as aq on jsq.qid = aq.qid left join @usersTable as uT on answers.responsible = uT.responsible where answers.taskAction = 1 and (uT.responsible is not null or (isnull(uT.responsible, answers.responsible) = answers.responsible and @filterPick = 0)) order by aq.section, jsq.jobnumber, answers.priority, aq.seq </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USdotnetN00b
    1. USdotnetN00b
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMy query filtering is not working the way I want it to
      1. USVoltaicShock
      1. VTFavorite
    2. VO
      singulars
      1. POMy query filtering is not working the way I want it to
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMy query filtering is not working the way I want it to
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you provide some sample data that you are working with? I am having trouble understanding what you are trying to ask. Also, I do not believe SQL Server uses any short circuiting: http://stackoverflow.com/questions/381224/sql-server-query-short-circuiting
      singulars
      1. POMy query filtering is not working the way I want it to
      1. USAbe Miessler
    2. COOn what exactly are you trying to filter? You seem to have both a variable (@resp1 in your script) and a table. So, you want a filter on the @resp1 variable if it is not null or a filter on everything in the table if @resp1 is null?
      singulars
      1. POMy query filtering is not working the way I want it to
      1. USAndrew
    3. COI am trying to filter on either @resp1 OR @userTable. So either filter by one name OR by a list of names.
      singulars
      1. POMy query filtering is not working the way I want it to
      1. USdotnetN00b
 

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