SQL Server How to dynamically create views/procedures in a different database

sgtfey8w 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(119)

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?

sql-server

来源：https://stackoverflow.com/questions/77146276/how-to-dynamically-create-views-procedures-in-a-different-database

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 call Otherdb.sys.sp_executesql . If the database name itself is dynamic then you can construct that procedure name in a variable and do EXEC @proc @query... .

You should also use QUOTENAME to correctly quote names.

DECLARE @TargetDatabase sysname = 'DatabaseA';
DECLARE @Query nvarchar(MAX) = '
CREATE VIEW dbo.ViewA
AS
SELECT 1
FROM dbo.TableA;
';

DECLARE @proc nvarchar(1000) = QUOTENAME(@TargetDatabase) + '.sys.sp_executesql';

PRINT @Query;  -- your friend

EXEC @proc @Query;

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server How to dynamically create views/procedures in a different database

1条答案

相关问题

热门标签

最新问答