Well, the speed difference wasn't as good as I had hoped. Identifying a duplicate, changing all referencing foreign keys to the original, and deleting the duplicate record used to take about 1.5 seconds per record and after implementing your example, it is down to about 1.1 seconds. The performance is noticeably better, but I had hoped for "blazing". Overly optimistic guessing must be a dead giveaway that a programmer is green.

What I didn't take into account in my optimistic forecast was that removing a lot of string concatenations and dynamic command compilations would speed things up, but adding an additional level of embedded stored procedures would slow things down, i.e., my top-level SP calling sp_executesql which called my child-level SP. Also, sp_executesql must have to parse through the string I am passing.

Thanks for your help, Sergey. I got a performance boost which will be significant when you consider the scope of the records that I will be running this on. I just need to learn to hope more conservatively.

David

>Sergey,
>
>Thanks a lot. This will really make my process blaze.
>
>I am writing a process that identifies duplicate records, searches for references to them across many child databases, replaces foreign key references to the duplicate with the original record's primary key, and then (if successful) deletes the duplicate recordss.
>
>The SP that does all the foreign key replacements across databases is called over and over as my app identifies duplicates according to various business rules.
>
>I had originally concatenated 6 UPDATE commands that specified the tables by database and it crawled. I wanted to have an SP on each child database that I could embed calls to in the top-level SP on the parent database. My problem was that I needed to make sure that all the foreign key replacements were successful before deleting the duplicate record, so I had to get a BIT value back indicating if there were any errors.
>
>I'll follow up with the speed difference between concatenating only one precompiled SP call across databases versus six separate UPDATE commands that need to be dynamically compiled.
>
>Thanks for your great example.
>
>David
>
>>David,
>>
>>Take a look at my How to return a value from dynamicaly executed T-SQL code FAQ #8130. It explains how it can be done. Here's a sample SP based on that FAQ

CREATE PROCEDURE SP_Works
>>    @myIntParam INT
>>AS
>>    DECLARE @database CHAR(50), @myBitParam bit, @sql nvarchar(4000)
>>    SET @database = 'zTest.dbo.'
>>    SET @sql = N'EXEC ' + @database + 'SPName @BitParam_ex OUT, @IntParam_ex'
>>	
>>    EXEC sp_executesql @sql,
>>		N'@BitParam_ex bit OUTPUT, @IntParam_ex int',
>>  		@myBitParam OUT, @myIntParam
>>
>>
>>    SELECT @myBitParam
>>

>>
>>>I am trying to write an SP that will make a call to a different SP in a different database using the EXECUTE statement to pass a BIT OUT parameter and another parameter and to retrieve the value of the OUT parameter afterwards, but I keep getting an error that says, "Error converting data type nvarchar to bit."
>>>
>>>A simple version of my SP is,
>>>
>><snip>