有 N 家供應商的訂單資料表(結構相同,資料表名即為供應商代碼)
現在需要下 SQL 指令
統計出各資料表的筆數
目前寫的 SQL 如下
但第 22 列出現錯誤「需定義資料表變數:@vendor_name」
SET NOCOUNT ON;
DECLARE @vendor_name nvarchar(50);
DECLARE @vendor_count nvarchar(50);
DECLARE @message nvarchar(50);
DECLARE vendor_cursor CURSOR FOR
SELECT name AS tablename FROM sysobjects
WHERE type='u'
AND name like 'vd_%';
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor INTO @vendor_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = 'SELECT COUNT(*) FROM ' + @vendor_name
PRINT @message
------------------------------------------------------------------------------------
DECLARE count_cursor CURSOR FOR
SELECT count(*) FROM @vendor_name
OPEN count_cursor FETCH NEXT FROM count_cursor INTO @vendor_count
IF @@FETCH_STATUS <> 0
PRINT ' <<None>>'
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = 'COUNT=' + @vendor_count
PRINT @message
FETCH NEXT FROM count_cursor INTO @vendor_count
END
CLOSE count_cursor
DEALLOCATE count_cursor
------------------------------------------------------------------------------------
FETCH NEXT FROM vendor_cursor INTO @vendor_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
資料表定義如下
(因po文時出現錯誤,所以僅複製兩個 table)
若需其他資訊請提出
我再盡力補充
CREATE TABLE vd_001 (`pono` varchar(5), `podate` varchar(8), `itemname` varchar(50), `qty` int, `deliverydate` varchar(8), `invoicedate` varchar(8));
CREATE TABLE vd_002 (`pono` varchar(5), `podate` varchar(8), `itemname` varchar(50), `qty` int, `deliverydate` varchar(8), `invoicedate` varchar(8));
INSERT INTO vd_001 (`pono`, `podate`, `itemname`, `qty`, `deliverydate`, `invoicedate`) VALUES ('po001', '20150112', 'item1', 100, '20150115', '20150118');
INSERT INTO vd_002 (`pono`, `podate`, `itemname`, `qty`, `deliverydate`, `invoicedate`) VALUES ('po001', '20150112', 'item1', 100, '20150115', '20150118');
<pre class="c" name="code">
DECLARE @vendor_name nvarchar(50);
.....(略)
DECLARE @sql nvarchar(MAX);
SET sql = 'SELECT COUNT(*) FROM [' + @vender_name + ']';
EXECUTE (@sql);
.....(略)
這個很常用在備份資料庫....
我最常用在備份TFS的資料庫
繼續請教
改成如下之後可正常執行
但是只有出現「命令已順利完成」
沒有看到各 table 的 count 數
請問單兵該如何處置?
<pre class="c" name="code">
SET NOCOUNT ON;
DECLARE @vendor_name nvarchar(50);
DECLARE @vendor_count nvarchar(50);
DECLARE @message nvarchar(50);
DECLARE @sql nvarchar(MAX);
DECLARE vendor_cursor CURSOR FOR
SELECT name AS tablename FROM sysobjects
WHERE type='u'
AND name like 'vd_%';
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor INTO @vendor_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = 'SELECT COUNT(*) FROM ' + @vendor_name
PRINT @message
SET @sql = 'SELECT COUNT(*) FROM [' + @vendor_name + ']';
EXECUTE (@sql);
FETCH NEXT FROM vendor_cursor INTO @vendor_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
改用參數化查詢...Dynamic SQL:
<pre class="c" name="code">
SET NOCOUNT ON;
DECLARE @vendor_name nvarchar(50);
DECLARE @vendor_count nvarchar(50);
DECLARE @message nvarchar(50);
DECLARE @sql nvarchar(MAX);
DECLARE @count bigint;
DECLARE vendor_cursor CURSOR FOR
SELECT name AS tablename FROM sysobjects
WHERE type='u'
AND name like 'vd_%';
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor INTO @vendor_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = 'SELECT COUNT(*) FROM ' + @vendor_name
PRINT @message
SET @sql = 'SELECT @count=COUNT(*) FROM [' + @vendor_name + ']';
EXEC sp_executesql @sql,N'@count bigint OUTPUT,@count OUTPUT;
SELECT @count AS [COUNT];
FETCH NEXT FROM vendor_cursor INTO @vendor_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
多定義一個@count變數,如行6
原本的行22改成SELECT @count=COUNT(*) FROM..
原本的行23,改用sp_executesql執行,帶入兩個參數,
第一個參數,定義動態SQL使用的變數,第二個參數,定義導出的變數。
出現錯誤如下
<pre class="c" name="code">
訊息 105,層級 15,狀態 1,行 22
遺漏字元字串 '@count bigint OUTPUT, @count OUTPUT;
SELECT @count AS [COUNT];
FETCH NEXT FROM vendor_cursor INTO @vendor_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
' 後面的引號。
訊息 102,層級 15,狀態 1,行 22
接近 '@count bigint OUTPUT, @count OUTPUT;
SELECT @count AS [COUNT];
FETCH NEXT FROM vendor_cursor INTO @vendor' 之處的語法不正確。
加個單引號後如下
仍是「命令已順利完成」
<pre class="c" name="code">
SET NOCOUNT ON;
DECLARE @vendor_name nvarchar(50);
DECLARE @vendor_count nvarchar(50);
DECLARE @message nvarchar(50);
DECLARE @sql nvarchar(MAX);
DECLARE @count bigint;
DECLARE vendor_cursor CURSOR FOR
SELECT name AS tablename FROM sysobjects
WHERE type='u'
AND name like 'vd_%';
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor INTO @vendor_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = 'SELECT COUNT(*) FROM ' + @vendor_name
PRINT @message
SET @sql = 'SELECT @count=COUNT(*) FROM [' + @vendor_name + ']';
EXEC sp_executesql @sql, N'@count bigint OUTPUT, @count OUTPUT';
SELECT @count AS [COUNT];
FETCH NEXT FROM vendor_cursor INTO @vendor_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
如果需要記錄每個資料表的名稱與資料筆數,可以加上一個table型態的變數
改寫一下...
<pre class="c" name="code">
SET NOCOUNT ON;
DECLARE @vendor_name nvarchar(50);
DECLARE @vendor_count nvarchar(50);
DECLARE @message nvarchar(50);
DECLARE @sql nvarchar(MAX);
DECLARE @count bigint;
DECLATE @tmpTable table(tablename nvarchar(100),reccount bigint);
DECLARE vendor_cursor CURSOR FOR
SELECT name AS tablename FROM sysobjects
WHERE type='u'
AND name like 'vd_%';
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor INTO @vendor_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = 'SELECT COUNT(*) FROM ' + @vendor_name
PRINT @message
SET @sql = 'SELECT @count=COUNT(*) FROM [' + @vendor_name + ']';
EXEC sp_executesql @sql,N'@count bigint OUTPUT,@count OUTPUT;
INSERT INTO @tmpTable (tablename,reccount) VALUES(@vender_name,@count);
FETCH NEXT FROM vendor_cursor INTO @vendor_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
SELECT * FROM @tmpTable;
N'@count bigint OUTPUT, @count OUTPUT'
抱歉
這個應該是
N'@count bigint OUTPUT', @count OUTPUT
我搞了個烏龍
table name 弄錯了
這個版本就得到結果了
再次謝謝
<pre class="c" name="code">
SET NOCOUNT ON;
DECLARE @vendor_name nvarchar(50);
DECLARE @vendor_count nvarchar(50);
DECLARE @message nvarchar(50);
DECLARE @sql nvarchar(MAX);
DECLARE @count bigint;
DECLARE vendor_cursor CURSOR FOR
SELECT name FROM sysobjects
WHERE type='u'
AND name like 'vd_%';
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor INTO @vendor_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = 'SELECT COUNT(*) FROM ' + @vendor_name
PRINT @message
SET @sql = 'SELECT @count=COUNT(*) FROM [' + @vendor_name + ']';
EXEC sp_executesql @sql, N'@count bigint OUTPUT', @count OUTPUT;
SELECT @count AS [COUNT];
FETCH NEXT FROM vendor_cursor INTO @vendor_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
SQL Server與MySQL 均有 information_schema 這個公用schema,
裡面有個Table叫Tables, 欄位有 table_schema, table_name, table_type等.
可以指定 table_schema,這就一般在SQL Server俗稱為"資料庫"的東西,
然後再找 vd 開頭的Table,可以的話 table_type選BASE TABLE,以免有VIEW時,
可能多算了.
T-SQL組合部份,就無須我多說啦,晚安.
DECLARE count_cursor CURSOR FOR
SELECT count(*) FROM @vendor_name
你可能是要用 Cursor 方式
那就是要執行 Dynamic Cursor
set @vquery = 'SELECT count(*) FROM ' + @vendor_name
set @vsql = 'set @cursor = cursor forward_only static for ' + @vquery + ' open @cursor;'
exec sys.sp_executesql
@vsql
,N'@cursor cursor output'
,@objcursor output
fetch next from @objcursor into @v_count
while (@@fetch_status = 0)
begin
print @vendor_name + ' = ' + cast(v_count as varchar)
fetch next from @objcursor into @v_count
end
close @objcursor
deallocate @objcursor