I second what Jack said on the updating of the statistics, that would certainly be your first step.

I would also look how the join is made on the table :...

This is exactly the solution I posted, using row_number(). It's the best...

Hi,

Here's what I propose, I think it's one of...

GilaMonster (12/2/2009)

---

It does allow the scenario where...

Hi,

SQL Server does not let you assign a value to a column when it COULD be returning more than 1 row. In this example, I don't think you need to...

Nice one Jeff,

Once again, you've proven we don't need RBAR code to solve even a format problem for SQL.

Keep it up, love to learn from you,

Happy it works, thanks for the feedback!

So, you used the stored procedure call in the select statement of the ASP.net, with the string parameter, and everything is fine? Good to know you can do that!

You have 2 solutions for this.

1. Can you try using the statement I've provided you directly in your XML, in the Select statement parameter? Sometimes, you can use more complex...

Why? you got a working solution now. Just use dynamic SQL and add your parameter to...

Matthew, what you will need to do, if you need to accept multiple roles like this in a stored procedure, is use some dynamic sql.

Considering your query, i've build a...

I have to say this is pretty cool a teacher actually took the time to build a small website for testing queries! I would have loved to learn SQL in...

You can still try it on the SQL 2000, but as Lutz pointed out, you will have to add the "included" column as part of the index. So just add...

lmu92 (11/11/2009)

---

@ J-F: Just a side note:...

JakesterUK (11/11/2009)

---

I am fairly sure the SP is as optimised as it can be, however, I've posted it below: -

ALTER PROCEDURE CheckContext

@CxID int,

...