Thanks Remi - hadn't seen that before, and I'll certainly use it future.

Mind you, if I hadn't posted the question, I might not have found out about the case-sensitive cache...

That part came from a serious lack of coffee on my part yesterday afternoon:

It failed only when there was no @CustOrderNum (i.e. the value 'noproject' was passed to the PROC),...

Thanks, Remi; very interesting.

In this case the procedure isn't called very regularly, and my code is kept case-sensitive for consistency anyway. But certainly useful nonetheless. And learnt something...

Yes it does...

Found it! Here's the QA results:

(1 row(s) affected)

Server: Msg 208, Level 16, State 1, Procedure sp_Control_Insert, Line 33

Invalid object name 'dbo.ToFour'.

It would seem then, that the error in SQL...

This is the before proc. Current proc as per first post.

I'm running it from QA now so I'll see what happens.

CREATE PROCEDURE dbo.sp_Control_Insert

@MaincdeCde varchar(4),

...

I've checked the application side, but that hasn't changed and has worked right throughout test, and up until when I made the change to the stored procedure. It's the...

Spotted it: in the penultimate line below. Double up all the single quotes?

So:

Select @sql = @sql + '''','','','','','','','','','',''''

Becomes:

Select @sql =

In case it's of use, I used this script to build the data dimension in a data warehouse. Bank Holidays are based on UK holidays though. The UDFs...

You don't have a FROM clause for the first SELECT. You need the same FROM in both (i.e. they both need to be complete SELECT statements before being UNIONed).

Without...

DECLARE @tablename sysname

--DECLARE @tablename varchar(10)

set @tablename = 'Employees'

EXEC('SELECT EmployeeID, * FROM ' + @tablename)

Wow, I've posted a question on a public forum, answered it (publically) myself and subsequently helped someone else 6 months later!

Don't ya love the Internet?

Thanks--I knew there was something. I'll embed the INSERT in a stored procedure with a return value of SCOPE_IDENTITY() and would hope that does the trick.

Thanks again!

This works but I don't know if it's the best way:

USE Northwind

GO

IF EXISTS (SELECT [name] FROM sysobjects WHERE [name] = 'sp_SPinSP_1' AND [type] = 'P')

 DROP PROCEDURE...

This worked for me; I've commented out the DROP part so you can see the SP has been created:

USE Northwind

GO

CREATE PROCEDURE sp_SPinSP_1

AS

DECLARE @sSQL varchar(255)