I'm not sure what you're trying to do here, but whatever it is, this is overcomplicating it.

Aside from that, you're using dynamic SQL and a flag to determine if the person is a teacher or not. Use a Role id instead. That's what you want. The flag is a trick, and, it may bite you in the future.

Can more than one person be in the same session? Why does it matter if the person is a teacher or student?

It looks like you're inserting each person twice, once as a teacher and once as a student. If so, it can be done in one query:

INSERT INTO SessPeople (SessId, PersonId, IsTeacher) SELECT ?, ? 0 UNION ALL SELECT ?, ? 1;

The second query is daunting. I have no idea what you're trying to do. If the original query (topic starting post) table was used, you can check via many ways. The easiest is to connect the two columns via UNION ALL:

WITH Session2 AS(SELECT AS (SELECT SessId, TeacherId, DoneDate, Topic FROM Sessions UNION ALL SELECT SessId, LearnerId, DoneDate, Topic FROM Sessions) SELECT SessId, TeacherId, DoneDate, Topic FROM Sessions2 WHERE TeacherId IN ();

The rewritten query here might be more complicated, but it does't look right. Could you explain a little more clearly what you;re trying to achieve? I have a feeling the solution is considerably simpler.

In reply to Re^3: Avoiding SQL double jeopardy by Pope-O-Matik
in thread Avoiding SQL double jeopardy by GrandFather

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.