TeraByteMe,

Looking at your original post, I think I know the problem. Modified the code to return max(id) + 1 and it doesn't use any dynamic sql.

ALTER PROC dbo.sp_GetMaxID (@MaxID INT OUTPUT)

AS

BEGIN

SELECT @MaxID = max(ID) + 1 FROM tblFileWatchMaxID;

RETURN @MaxID

END

To test the code, run this:

declare @MyMaxID int;

exec dbo.sp_GetMaxID @MaxID = @MyMaxID OUTPUT;

select @MyMaxID;

As for what you are trying to explain to everyone, please let me know if I am paraphrasing this correctly.

The value of ID relates to one file, and there may be multiple instances of this ID in the table as the file moves around (what ever that means). Each time a new file is added, you need a new ID value, which you determine by getting the current MAX(ID) value and adding 1 to it.

Does that pretty much sum it up?