使用T-SQL语句创建Function
今天学习了如何在SQLServer中，通过脚本创建Function，以下是今天练习的Demo脚本
/*自动生成的脚本*/USE forumsGOCREATE TABLE EMPLOYEE_DUPLICATE(ID INT IDENTITY(1,1) PRIMARY KEY,EMP_NAME NVARCHAR(40) NOT NULL,EMP_TEL NVARCHAR(20) NOT NULL,EMP_PROVINCE NVARCHAR(20) NOT NULL,EMP_COUNTRY NVARCHAR(20) NOT NULL)GO//将一个表中的数据复制到刚才新建的表中INSERT INTO EMPLOYEE_DUPLICATE(EMP_NAME,EMP_TEL,EMP_PROVINCE,EMP_COUNTRY) SELECT EMP_NAME,EMP_TEL,EMP_CITY,EMP_COUNTRY FROM EMPLOYEE/**注意：题外话，如果要跨数据库 (前提是当前用户中可以操作那个数据库) 复制数据，则以下语句似乎可以搞定INSERT INTO DataBase_1_Name.dbo.EMPLOYEE_DUPLICATE (EMP_NAME,EMP_TEL,EMP_PROVINCE,EMP_COUNTRY)SELECT EMP_NAME,EMP_TEL,EMP_CITY,EMP_COUNTRY FROM DataBase_2_Name.dbo.EMPLOYEE**/SELECT * FROM EMPLOYEE_DUPLICATE/*创建带参数&返回值的函数*/IF EXISTS (SELECT * FROM sysobjects WHERE NAME = 'getResultById')DROP FUNCTION getResultByIdCREATE FUNCTION getResultById (@id INT)RETURNS TABLEASRETURN (SELECT EMP_NAME, EMP_TEL,EMP_PROVINCE,EMP_COUNTRY FROM EMPLOYEE_DUPLICATE WHERE ID = @id)//调用该函数SELECT * FROM getResultById(2)/**Results Listing1Tom13011041125NewYorkUSA2James13000145296ShanXiChina3Harry13103452845ShanDongChina4Jessica13429990000ShanXiChina5Larry0421-2909090YutaUSA6Jakeboo0911-2657999ShanDongChina7Vencent010-28129588BeiJingChina*/IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'getIdsByProvince')DROP FUNCTION getIdsByProvinceCREATE FUNCTION getIdsByProvince(@province NVARCHAR(50))//注意：一定是RETURNS，不是RETURN ! !//另外：似乎只可以将返回类型设置为TABLE//没有什么BEGIN...END...GO之类的东东RETURNS TABLEASRETURN(SELECT ID FROM EMPLOYEE_DUPLICATE WHERE EMP_PROVINCE = @province)SELECT * FROM EMPLOYEE//查询在EMPLOYEE表中过滤重复之后的EMP_CITY以及重复EMP_CITY的计数SELECT DISTINCT(EMP_PROVINCE), COUNT(EMP_PROVINCE) FROM EMPLOYEE_DUPLICATE GROUP BY EMP_PROVINCE//调用该函数，传入CITY，然后返回相应的ID，再根据ID查询出EMPLOYEE表中的记录SELECT * FROM EMPLOYEE WHERE ID IN (SELECT ID FROM getIdsByProvince('ShanXi'))