Apologies for the delay in replying Luis.

This is a great tip for the dynamic SQL. Thanks for taking the time to respond to my query.

Thanks Luis - that's very much appreciated.

I will use Excel to generate the case statements as I have about 125 references!

Unfortunately my hands are tied with the structure of the...

Hi, thanks for the quick response.

Here is the code to create the sample table.....

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#ctr_ref','U') IS NOT NULL

DROP TABLE #ctr_ref

--===== Create the...

Hi all,

I've got an update.....

We have two XP clients that are exactly the same - well out the box anyway, perhaps someone has changed something.

I still have the problem with...

That works - thanks Chris......so it was just doubling up the quotes basically?

Sure.....

SELECT 'PK1|'

+ ...

I increased it to 4000 and it still doesn't work. Slightly amended the query as well taking out the CONVERT's...

ALTER PROCEDURE dbo.Sun5_OFAC_Export

...

Chris Morris (12/4/2008)

---

Give this a try. When you've got it working, then's the time to put it into a stored procedure. Notice the PRINT statement? Take the output from this,...

rbarryyoung (11/30/2008)

---

OK, leaving aside the rest of the select statement, here is what you need to do with just the table names and the FROM clause for Dynamic SQL:

Declare

Aha, I did see another thread actually where the query was actually a parameter - thanks very much!!

Evening all,

Thanks for your suggestions.

However, its the table name that I need assistance on rather than the SELECT clause.

How would I do this dynamically?

Thanks!

Richard

GilaMonster (10/30/2008)

---

You are correct, an order by in a view or subquery is ignored. Order by only applies if it's in the outer select statement, unless there is a row-limiting...

OK, have done some further tests:

1. created new table:

CREATE TABLE TEST

REF INT NOT NULL,

DATE DATETIME NOT NULL

2. Inserted 4 rows - made sure that the REF was sequential but...

Same result - I created this for testing this issue - the real view contains more columns and joins...

Thanks BigBucket - you set me on the right path.

I just put an IF statement in as follows:

IF @DisplayNo = '%%'

SET @DisplayNo = ''