存儲過程和使用者自定義函數

一:存儲過程的簡單建立,修改與删除

1.建立簡單的存儲過程

use AdventureWorks

go

create proc spEmployee

as

select * from HumanResources.Employee

執行上面的代碼就建立了一個存儲過程

如果想運作這個存儲過程

可以直接執行exec spEmployee這個語句

2.更改存儲過程

ALTER proc [dbo].[spEmployee]

select top 13 * from HumanResources.Employee

3.删除存儲過程

drop proc dbo.spEmployee

二:存儲過程的輸入參數和輸出參數

1.有輸入參數的存儲過程

    @LastName nvarchar(50) = null

if @LastName is null

    select top 13 * from HumanResources.Employee

else

    select top 10 * from HumanResources.Employee

檢視該存儲過程的結果可以用

exec spEmployee '123'

或直接exec spEmployee

存儲過程的重載...

2.有輸出參數的存儲過程

alter proc spEmployee

    @LastName nvarchar(50) = null output

    begin

        print 'null'

        return '123'

    end

        print @LastName

        return '456'

看第一個測試該存儲過程的語句

declare @myval nvarchar(50)

exec @myval = spEmployee @myval output

print @myval

輸出null  123

第二個測試該存儲過程的語句

set @myval = 'xland'

輸出xland  456

三:使用者定義函數

1.傳回标量值的使用者定義函數

先做一個簡單的日期處理函數

把長日期縮短成短日期

Create function dbo.DayOnly(@date datetime)

returns varchar(12)

begin 

    return convert(varchar(12),@date,101)

end

為了測試上面的函數先做個腳本

use Accounting

declare @counter int

set @counter = 1

while @counter <= 10

begin

    insert into Orders values(1,dateadd(mi,@counter,getdate()),1)

    set @counter = @counter +1

end 

然後檢索這個腳本 新插入的資料記錄

select * from orders where dbo.DayOnly(date1) = dbo.DayOnly(getdate())

2.傳回表的使用者定義函數

先看例子

create function dbo.fnContactSearch(@LastName nvarchar(50))

returns table

return (select * from Person.Contact where LastName like @LastName+'%')

執行這個例子

select * from fnContactSearch('Ad')

3.綜合例子:傳回表,有輸入參數

use xland

create function dbo.funGetMytable

    (@id as int)

    returns @allrows table

    (

        id  int not null,

        title  nvarchar(max) null

    )

    insert into @allrows  select id,title from mytable where id = @id

return

select * from funGetMytable(1)