1、存储过程和用户自定义函数一:存储过程的简单创建,修改与删除1.创建简单的存储过程use AdventureWorksgocreate proc spEmployeeasselect * from HumanResources.Employee执行上面的代码就创建了一个存储过程如果想运行这个存储过程可以直接执行execspEmployee这个语句2.更改存储过程ALTER proc [dbo].[spEmployee]asselect top 13 * from HumanResources.Employee3
2、.删除存储过程drop proc dbo.spEmployee二:存储过程的输入参数和输出参数1.有输入参数的存储过程use AdventureWorksgocreate proc spEmployee @LastName nvarchar(50) = nullasif @LastName is null select top 13 * from HumanResources.Employeeelse select top 10 * from HumanResources.Employee查
3、看该存储过程的结果可以用execspEmployee'123'或直接execspEmployee存储过程的重载...2.有输出参数的存储过程use AdventureWorksgoalter proc spEmployee @LastName nvarchar(50) = null outputasif @LastName is null begin print 'null' return '123' endelse begin print @
6、t into Orders values(1,dateadd(mi,@counter,getdate()),1) set @counter = @counter +1end 然后检索这个脚本新插入的数据记录use Accountingselect * from orders where dbo.DayOnly(date1) = dbo.DayOnly(getdate())2.返回表的用户定义函数先看例子use AdventureWorksgocreate function dbo.fnContactSe
7、arch(@LastName nvarchar(50))returns tableasreturn (select * from Person.Contact where LastName like @LastName+'%')执行这个例子use AdventureWorksselect * from fnContactSearch('Ad')3.综合例子:返回表,有输入参数use xlandgocreate function dbo.funGetMytable (@id as int) retu
8、rns @allrows table ( id int not null, title nvarchar(max) null )asbegin insert into @allrows select id,title from mytable where id = @idreturnend go执行这个例子select * from funGetM