非常驚人的使用者介面：SQL

最近工作常常跟 MSSQL 打交道，默默中學到不少 MSSQL 知識，SQL 使用者介面肯定是非常驚人。

• ERROR: The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values. 最近常用 INSERT 指令插入很多筆 records，SQL SERVER 2008 提供很方便的使用者介面：
  

  INSERT INTO [dbo].[USState]
  VALUES ('AK', 'Alaska'),
         ('AL', 'Alabama'),
         ('AR', 'Arkansas'),
         ('AZ', 'Arizona'),
         ('CA', 'California')
  

  可是如果超過 1000 筆 row values，直接下 INSERT INTO 指令 1000 次，生氣。
  
• Pass user defined table type as stored procedured parameter.
  

  CREATE TYPE ItemInfo AS TABLE
  (
      ItemNumber VARCHAR(50),
      Qty INT
  )
  
  DECLARE @items AS ItemInfo
  INSERT INTO @Items (ItemNumber, Qty)
      SELECT '11000', 100 UNION ALL
      SELECT '22000', 200 UNION ALL
      SELECT '33000', 300
  
  CREATE PROCEDURE TableParamDemo
  (
      @Items ItemInfo READONLY
  )
  AS
  SELECT * FROM @Items
  
  
  -- Execute the stored procedure 
  DECLARE @items AS ItemInfo
  INSERT INTO @Items (ItemNumber, Qty)
      SELECT '11000', 100 UNION ALL
      SELECT '22000', 200 UNION ALL
      SELECT '33000', 300
  
  EXECUTE TableParamDemo @Items