SQL 2008 T-SQL支持表变量参数

最近装了VS2010尝鲜。在Win7下速度明显快了很多。顺便装了Microsoft sql server 2008 R2，在看SQL 2008对T-SQL语法的增强的时候注意到了一个表变量作为参数传递的新语法觉得很不错。

　　首先建立一张测试表：

CREATE TABLE [dbo].[us](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NULL,
    [pass] [varchar](50) NULL,
 CONSTRAINT [PK_us] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

定义一个自定义表类型：

 

CREATE TYPE [dbo].[UsTable] AS TABLE(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [NAME] [varchar](50) NULL
)

然后我们写一个存储过程来传递这个类型，这里我们模拟用户批量添加默认密码为123

 

CREATE  PROC [dbo].[addus]
 @tab [UsTable] READONLY
AS
BEGIN 
     INSERT INTO us(name,pass) SELECT name,'123' FROM @tab
END

 

测试一下：

 

–-定义一个自定义表类型的变量
DECLARE @us UsTable 
--插入模拟数据
INSERT INTO @us (name) VALUES ('dc')
INSERT INTO @us (name) VALUES ('ada')
INSERT INTO @us (name) VALUES ('dacey')
--执行存储过程
EXEC dbo.addus @tab = @us

 查询us表看下是否都插入进去了：
　　1 dc 123
　　2 ada 123
　　3 dacey 123

HDMI 19P/F SMT Dual ear HDMI high definition data plug HDMI connectorSilver and Black Vertical Type 4 Ports 7 Ports USB 2.O Hub for PC LaptopsBMW Shaped Rotatable 4 Ports USB Hub for Your PC LaptopUltra Mini white USB AC power adapter for ipod iPhone 2G 3G chargerPCI four ports array card with 4 internal SATA interface up to 1.5Gbps data transfer rate PCI cardSquareness Shaped 4 Ports USB HubCool High Cylindrical Wooden 2Gb USB 2.0 Flash Memory DriveUSB3.0B Female RoHS compliant connector OEM adapter connectorSamsung 10P (half is covered by iron) straight head USB connector cellphone mobile connectorOval Wooden 4Gb USB 2.0 Flash Memory DriveUSB 2.0 to Parallel Printer Adapter Cable 6*RCA F and 4 pin MD F to 15 pin Male HD VGA Video Audio Monitor Cable5*BNC F to 15 pin M HD VGA Video Audio Monitor CableFLP Mini 10P Female sinking USB connector cellphone connector mobile connectorIPod audio transitions cable & USB cableYellow Truck Shaped USB 2.0 Flash Storage Drive3.5 DC Stereo Male Plug to 2 RCA Female Audio Output Cable DVI24+5 Male 180°heightening type plugboard connector pin OEM adapter connector4 in 1 Multifunction USB Card ReaderIPOD 30P welding on board(L=29.6mm) connector cellphone connector usb adapter mobile phone connector32Mb USB 2.0 Flash DrivePCI 1394 card 3+1 ports up to 400Mbps data transfer rate support plug and play PCI card2Gb USB 2.0 Flash MemoryLeather Button 2Gb USB 2.0 Flash Storage DriveWooden Bamboo Shaped 4Gb USB 2.0 Flash Memory DriveUSB to ESATA adapter card high-speed plug and play fashion design transmission cardMini 16P Female SMT USB connector mini usb port type cellphone connector adapterIDE to USB adapter card high-speed plug and play LED indication light fashion transmission cardLG 20P bend head connector cellphone connector LG mobile phone connectorUSB 2.0 Flash Storage Drive25 Pin DB25 Male to Female Extension Cable for Printer WhiteHDMI 19P/M wire solder typeC HDMI high definition data plug HDMI connector8P network three-channel adapter connector with long life time OEMStylish 4Gb USB 2.0 Flash Storage Drive512Mb USB 2.0 Flash Memory DriveLG 24P insert&withdraw enforcebale connector cellphone connector LG mobile phone connector12*RCA F to 15 pin Male HD VGA Video Audio Monitor CableColor Leather 256Mb USB 2.0 Flash Storage Drive With ChainDVI24+5 Female wire solder 180°plug connector pin OEM adapter connector512Mb USB 2.0 Flash MemoryDVI24+5 Female DIP straight 180° with fishgig plug connector pin OEM adapter connectorMini 5P B 5M wire solder four-piece USB connector OEM adapter Mini USB plug connectorLG 18P straight handle1 connector cellphone connector LG mobile phone connectorHDMI 19P/F SMT with screw hole H5.08mm HDMI high definition data plug HDMI connectorBlock Shaped 4Gb USB 2.0 Flash Storage DriveGold Like Portable 4 Ports USB 2.0 Hub for PC LaptopUSB3.0A/F DIP 90° RoHS compliant connector OEM adapter connector3.5 DC to 2*DC adapter connector stable performanceUSB 2.0 Data Charger Adapter Retractable Cable for LG PhoneHDMI 19P/M wire solder three-piece HDMI high definition data plug HDMI connector Replica lv Hangbags Replica Hangbags Hangbags replica handbags replica handbags replica handbags replica handbags usb cable usb cable usb cable usb cable usb flash drives