I have particular records in my db that I need to cross-reference and the only way I can think of doing this is by using a cursor executing dynamic SQL.

I want to do this in a single script I can run but the problem I have is the only examples I can find are for creating procedures but I don't want to do this because this is just something I need to run on the client's database from time to time to diagnose problems. Any idea how I can do this without creating a procedure?

why not just create the procedure, use it, then drop it?

Because the user would have to give "create" access which they don't want to do (they're a bank)

Could you explain what exactly the cross-referencing shall do? Do you want to change (insert/update/delete) some data or do you just want to retrieve a result set returning some differences or erroneous records?

You typically don't need a cursor for that - just use some regular SQL statements that express your conditions and then work with the result set in whichever way you want.

Basically the way our app has been designed is that rather than allowing the db to decide the next primary key for each table these are stored in a seperate table containing the table name and key (I know...not my idea but something I'm stuck with!! ).

Sometimes these get out of sync so when the application tries to insert a new record it does so with an invalid primary key. What I need to do is loop through each of the records on the table and check the actual primary key value (using dynamic sql) to see where the 2 are out of sync. Unfortunately I don't have the option of changing how the application does this but what I just need to run this on an adhoc basis to see where there might be some problems (hope this makes sense)

Invalid here means a duplicate value, because the next value in the key lookup table is less than the maximum PK value in the data table?

?

That's exactly the problem, unfortunately this is something that's in the application code which I don't have access to, what I just need to do is to occasionally run a script which will identify those keys which have gotten out of sync so they can be repaired (either manually or as part of the script)

So why not just execute this query as a SQL script?

That's exactly what I want to do but I need to loop through each record in the PKs table to check what the corresponding PK on the datatable is (was planning on doing this using dynamic SQL). The problem I'm having is in creating the cursor to loop through each of these records - just neater than having to manually create each select manually as it makes sure I don't miss any.

How'bout this:Obiously, all this should be on one line - I split it up for better readability.

Why do you think you need to loop? Simply join the 2 tables and be done with it - exactly as n_i posted.

p.s: SQL was not designed as a procedural language (originally). So don't try to cram it into this corset. If you do things the SQL-way (set oriented), you are ofter much better off.