iT邦幫忙

0

[請教]MS SQL SQL statement

有 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');
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
8
外獅佬
iT邦大師 1 級 ‧ 2015-01-12 22:41:11
最佳解答
<pre class="c" name="code">
DECLARE @vendor_name nvarchar(50);
.....(略)
DECLARE @sql nvarchar(MAX);
SET sql = 'SELECT COUNT(*) FROM [' + @vender_name + ']';
EXECUTE (@sql);
.....(略)
看更多先前的回應...收起先前的回應...
外獅佬 iT邦大師 1 級 ‧ 2015-01-12 22:42:20 檢舉

這個很常用在備份資料庫....
我最常用在備份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;
外獅佬 iT邦大師 1 級 ‧ 2015-01-13 08:48:34 檢舉

改用參數化查詢...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; 
外獅佬 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 檢舉

出現錯誤如下

<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;
外獅佬 iT邦大師 1 級 ‧ 2015-01-13 09:01:15 檢舉

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

外獅佬 iT邦大師 1 級 ‧ 2015-01-13 09:04:10 檢舉
<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

我搞了個烏龍
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;
8
一級屠豬士
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

了解
謝謝

2
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

summertw iT邦好手 1 級 ‧ 2015-01-13 10:02:16 檢舉

SELECT @message = 'SELECT COUNT(*) FROM ' + @vendor_name
PRINT @message

這兩行,應該是樓主Debug要用的吧,不是要呈現Count數,阿伯兄,你這招不是給人家添亂子了嗎!!

我要發表回答

立即登入回答