2012年11月15日 星期四

SQL Server - 如何動態的變更資料庫

        最近接到一個需求,需要透過T-SQL進行檢示表(View)的建立,但是在程式中需要在A資料庫上建立在B資料庫上,相信大家都知道如何透過GUI的畫面與T-SQL變更目前的資料庫的方式,在T-SQL中,我們可以使用 USE 的關鍵字,但是如果要透過動態的SQL語法時,就無法正常的使用這個關鍵字,一但執行時會發生下列的錯誤訊息。

1、嘗式透過動態SQL的方式進行執行,但會遇到下列的錯誤。

原本的語法:
DECLARE @sql nvarchar(4000);

set @sql = 'USE Northwind '
set @sql = @sql + ' go '
set @sql = @sql + ' create view dbo.test_view '
set @sql = @sql + ' as'
set @sql = @sql + ' select * from customers;'

EXEC (@sql)
錯誤訊息:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'go'.
Msg 111, Level 15, State 1, Line 1
'CREATE VIEW' must be the first statement in a query batch.


2、透過上述的語法,想說可能不可以在動態SQL中進行,所以直接在View的前面嘗試加上資料庫名稱進行,但仍然有下列的錯誤。

調整後語法:
DECLARE @sql nvarchar(4000);

set @sql = ' create view Northwind.dbo.test_view '
set @sql = @sql + ' as'
set @sql = @sql + ' select * from customers;'

EXEC (@sql)
錯誤訊息:
Msg 166, Level 15, State 1, Line 1
'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name.

3、最後終於找到一個方法,也就是透過在內層同時執行USE與建立View的語法,即可解決這個問題。

修改後的語法:
DECLARE @sql nvarchar(4000);
DECLARE @sql_package nvarchar(4000);
DECLARE @dbName varchar(100);

set @dbName = 'Northwind';
set @sql = 'create view dbo.test_view '
set @sql = @sql + ' as'
set @sql = @sql + ' select * from customers;'

SET @sql_package = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @sql + '''';
EXEC (@sql_package)


參考網址:
sp_executesql (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188001.aspx
USE (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms188366.aspx


關鍵字:SQL ServerUSEsp_executesqlDynamic SQL Command

沒有留言:

張貼留言