使用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'))

以上就是新建FUNCTION的脚本了，创建数据库之后，就可以直接运行了！

欢迎拍砖！