出处：http://www.jb51.net/article/54730.htm
--有输入参数的存储过程--
create
proc GetComment
(@commentid
int
)
as
select
*
from
Comment
where
CommentID=@commentid
调用方式：exec GetComment 3
--有输入与输出参数的存储过程--
create
proc GetCommentCount
@newsid
int
,
@
count
int
output
as
select
@
count
=
count
(*)
from
Comment
where
NewsID=@newsid
调用方式：
declare @cnt int
exec GetCommentCount 1,@cnt output
print @cnt
--返回单个值的函数--
create
function
MyFunction
(@newsid
int
)
returns
int
as
begin
declare
@
count
int
select
@
count
=
count
(*)
from
Comment
where
NewsID=@newsid
return
@
count
end
调用方式：
declare @cnt int
exec @cnt =
MyFunction 1
print @cnt
--返回值为表的函数--
Create
function
GetFunctionTable
(@newsid
int
)
returns
table
as
return
(
select
*
from
Comment
where
NewsID=@newsid)
go
调用方式：
select * from GetFunctionTable(2)
CREATE proc func_withconditions
(
@firstName varchar(20),
@lastName varchar(20)
)
AS
begin
declare @sql varchar(500)
set @sql = 'select * from employee where 1=1 '
if(@firstName is not null)
set @sql = @sql+' and first_name='+ ''''+@firstName+ ''''
if(@lastName <> ' ' and @lastName is not null)
set @sql = @sql+' and last_name='+ ''''+@lastName+ ''''
exec(@sql)
end
GO
调用方式：
exec func_withconditions 'ahg',''
exec func_withconditions 'ahg',NULL
exec func_withconditions NULL,'jhg'