As Martin pointed out in the comments, the problem is that the query is using a cached plan which is inappropriate for the parameters given.
The link he provided on Slow in the Application, Fast in SSMS? Understanding Performance Mysteries provided a lot of useful information which lead me to some solutions.
The solution I am currently using is to copy the parameters to local variables in the procedure, which I think makes SQL re-evaluate the execution plan for the query anytime it's run, so it picks the best execution plan for the parameters given instead of using an inappropriate cached plan for the query.
Other solutions which may work are using the OPTIMIZE FOR
or RECOMPILE
query hints.
This will require really having fun with escaping and re-escaping single quotes, but I think the technique you're after is:
DECLARE
@DB NVARCHAR(255) = QUOTENAME(N'dbname'),
@SQL NVARCHAR(MAX);
SET @SQL = N'EXEC ' + @DB + '.sys.sp_executesql '
+ 'N''CREATE VIEW dbo.vWhatever
AS
SELECT x = 1, y = ''''x'''', z = GETDATE();''';
EXEC sys.sp_executesql @SQL;
So you're kind of nesting the dynamic SQL; the inner one ensures that the SQL is executed at the target database, not locally.
Another way that I've picked up since this original question appeared:
DECLARE
@DB NVARCHAR(255) = QUOTENAME('dbname'),
@SQL NVARCHAR(MAX),
@DBExec NVARCHAR(MAX);
SET @DBExec = @DB + N'.sys.sp_executesql';
SET @SQL = N'CREATE VIEW dbo.whatever
AS
SELECT x = 1, y = ''x'', z = GETDATE();';
EXEC @DBExec @SQL;
This is slightly tidier because you don't have to double-nest single quotes.
And here is a slightly tidier way to do it without cursors (well, without all the scaffolding of setting up a cursor). Note that the inner dynamic SQL will only be executed in databases where (a) a categories table exists (b) this view does not already exist and (c) it is not a system database (well, not one of the primary system databases, anyway).
DECLARE @SQL NVARCHAR(MAX) = N'';
SELECT @SQL += NCHAR(13) + NCHAR(10)
+ N'IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
+ '.sys.views WHERE name = ''vNonhiddenCategories'')
AND EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
+ '.sys.tables WHERE name = ''categories'')
BEGIN
EXEC ' + QUOTENAME(name) + '.sys.sp_executesql N''
CREATE VIEW dbo.vNonhiddenCategories3
AS
SELECT x = 1, y = ''''x'''';''
END'
FROM sys.databases
WHERE database_id BETWEEN 5 AND 32766;
PRINT @sql;
-- EXEC sp_executesql @sql;
Of course "tidier" is in the eye of the beholder.
Best Answer
So per @LowlyDBA's suggestion I decided to test this for myself (to a degree). I was actually in the middle of debugging a hot production issue so couldn't really stop to write out the code for a test case. But the code I'm debugging is this scenario. So I made my update to the procedure and looked at the running queries and saw the query text changed from before and after my change to the procedure as the cursor continued to iterate.