[請教]MS SQL SQL statement

程式設計資料庫

海綿寶寶 2015-01-12 22:27:11 ‧ 8958 瀏覽

分享至

有 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 &lt;> 0 
		 PRINT '         &lt;&lt;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');

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

外獅佬

iT邦大師 1 級 ‧ 2015-01-12 22:41:11

最佳解答

&lt;pre class="c" name="code">
DECLARE @vendor_name nvarchar(50);
.....(略)
DECLARE @sql nvarchar(MAX);
SET sql = 'SELECT COUNT(*) FROM [' + @vender_name + ']';
EXECUTE (@sql);
.....(略)

回應 11
分享
檢舉

看更多先前的回應...收起先前的回應...

外獅佬 iT邦大師 1 級 ‧ 2015-01-12 22:42:20 檢舉

這個很常用在備份資料庫....
我最常用在備份TFS的資料庫

海綿寶寶 iT邦大神 1 級 ‧ 2015-01-13 08:12:50 檢舉

繼續請教
改成如下之後可正常執行
但是只有出現「命令已順利完成」
沒有看到各 table 的 count 數
請問單兵該如何處置？

&lt;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;

外獅佬 iT邦大師 1 級 ‧ 2015-01-13 08:48:34 檢舉

改用參數化查詢...Dynamic SQL：

&lt;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;

外獅佬 iT邦大師 1 級 ‧ 2015-01-13 08:50:51 檢舉

多定義一個@count變數，如行6
原本的行22改成SELECT @count=COUNT(*) FROM..
原本的行23，改用sp_executesql執行，帶入兩個參數，
第一個參數，定義動態SQL使用的變數，第二個參數，定義導出的變數。

外獅佬 iT邦大師 1 級 ‧ 2015-01-13 08:51:52 檢舉

sp_executesql

海綿寶寶 iT邦大神 1 級 ‧ 2015-01-13 08:59:38 檢舉

出現錯誤如下

&lt;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' 之處的語法不正確。

海綿寶寶 iT邦大神 1 級 ‧ 2015-01-13 09:00:31 檢舉

加個單引號後如下
仍是「命令已順利完成」

&lt;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;

外獅佬 iT邦大師 1 級 ‧ 2015-01-13 09:01:15 檢舉

如果需要記錄每個資料表的名稱與資料筆數，可以加上一個table型態的變數
改寫一下...

外獅佬 iT邦大師 1 級 ‧ 2015-01-13 09:04:10 檢舉

&lt;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;

外獅佬 iT邦大師 1 級 ‧ 2015-01-13 09:05:55 檢舉

N'@count bigint OUTPUT, @count OUTPUT'

抱歉
這個應該是
N'@count bigint OUTPUT', @count OUTPUT

海綿寶寶 iT邦大神 1 級 ‧ 2015-01-13 09:35:22 檢舉

我搞了個烏龍
table name 弄錯了

這個版本就得到結果了
再次謝謝

&lt;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;

登入發表回應

一級屠豬士

iT邦大師 1 級 ‧ 2015-01-12 22:47:34

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組合部份,就無須我多說啦,晚安.

http://msdn.microsoft.com/zh-tw/library/ms186224.aspx

回應 1
分享
檢舉

海綿寶寶 iT邦大神 1 級 ‧ 2015-01-13 08:15:01 檢舉

了解

登入發表回應

Albert

iT邦高手 1 級 ‧ 2015-01-12 22:52:27

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