That makes sense.

Would it be better to surround the @Enddate in the first example with a Convert to Varchar or the second one (the cast statement)?

Thanks,

Tom

The reason I am doing it this way is that this select is really part of an OpenQuery statement. And if you use parameters, apparently you have to put...

I don't understand.

Why does it work without casting it to varchar in the first version?

Thanks,

Tom

Sql Server Management Server project.

That worked fine.

Not sure what this means:

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

How does that work?

I was putting the waitfor in the query to test the locking and to see when it releases.

The only problem is that the 2nd query (select) never comes back.

If I...

Figured it out.

The table in the subquery was not filtering out nulls and there were nulls in that field.

So if I changed it to:

SELECT *

FROM ...

I did try the LEFT JOIN and it works correctly with the results expected.

SELECT *

FROM VEStaging ves

LEFT JOIN EHR ehr

ON ves.SSN = ehr.SSN

WHERE ehr.SSN is...

To test this, I put together this test which does work fine. If you take out the where clause, I still get one row back (ssn = 2). ...

Both Erik and Sean had the solutions I was looking for.

Thanks,

Tom

Yes, they are in the same session. As a matter a fact, if I press the debug button, go to the first line of the procedure and cancel the...

That was it.

Thanks

I was a little confused.

I know you can only use whatever values are actually passed back (defined) from the CTE, I didn't know that the value remembers which row and...

I also tried it by taking the t1.t_id off of the selection list and it still worked.

You said it knows about the column from the one table but not the...

Great.

That answers my question.

But on your CTE sample, I like that as well but am not sure why it works.

I made a small modification to your sample to add another...