SQL Server 2008 存储过程示例

出处：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'

 

 

 

转载于:https://www.cnblogs.com/it12345/p/5199029.html