I am writing a dynamic query that will copy a large number of views and stored procedures from one database to another. I can not use native SSMS functionality, because this will be driving from a web front-end. There are any number of possible source and target databases:
DECLARE @TargetDatabase nvarchar(20) = 'DatabaseA'
DECLARE @Query nvarchar(MAX)
SET @Query = 'USE [' + @TargetDatabase + ']' + CHAR(10) + 'GO' + CHAR(10) + 'CREATE VIEW dbo.ViewA AS SELECT 1 FROM dbo.[TableA]'
EXEC(@Query)
--PRINT @Query
How I populate the @Query variable is much more complicated than above, but for the purposes of this question, this should be sufficient.
Without the "GO" to separate the two batches, I get the error message "'CREATE VIEW' must be the first statement in a query batch"
When I add the "GO", I get that error message, but I also get "Incorrect syntax near 'GO'."
If I print the @Query, copy/paste the results in to SSMS, it works without any issue.
The best I can figure is that it's failing because it doesn't like the CHAR(10) before 'GO'.
Thoughts?
1条答案
按热度按时间kxe2p93d1#
GO
is not an SQL keyword. It is a batch separator interpreted by tools such as SSMS in order to separate batches in a single file.In dynamic SQL, to separate batches you need to actually use
EXEC
multiple times.But
USE
does not work across batches. The correct way to do this is to callOtherdb.sys.sp_executesql
. If the database name itself is dynamic then you can construct that procedure name in a variable and doEXEC @proc @query...
.You should also use
QUOTENAME
to correctly quote names.