[Ugly] Drop a view using Dynamic SQL

There's a time and a place for Dynamic SQL. This is, as far as I know, one of them.

I need to ensure a view (standard_view) is dropped in a particular database (My2008DB). That database is named for the year, so it's different each year. I can't use sp_msforeachdb for a particular (REDACTED) reason. You probably don't have that problem, I do.

So, best I can tell, the way to do it is to call sp_executesql from within the context of that other database. And since I have to execute the code to verify the drop before doing it, we wind up with this particularly ugly piece of code. Yes, we're 3 layers deep. sp_executesql calls My2008DB.dbo.sp_executesql, which calls the actual drop code. And yes, it works, at least on SQL Server 2005.

DECLARE @date SMALLDATETIME, @drop_view VARCHAR(8000) ,@SQLString nvarchar(500), @ParmDefinition nvarchar(500)
select @date = '5/15/2008'

--DROP VIEW.  Need to look in the DB, determined dynamically via the @date
SELECT @drop_view = 'if object_id(''My' + CONVERT(CHAR(4),@date, 112) + 'DB..standard_view'') is not null
    drop view standard_view'

--now we create a "wrapper" to run in the correct database context.
SELECT @sqlstring = N'execute My' + CONVERT(CHAR(4),@date,112) + 'DB.dbo.sp_executesql @mysql', @ParmDefinition = N'@mysql nvarchar(max)'

--execute in our dynamic db
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@mysql = @drop_view