SQL Server 2008 Stored Procedure sample

Source: http://www.jb51.net/article/54730.htm

--有输入参数的存储过程-- create proc GetComment (@commentid int ) as select * from Comment where [email protected] call mode: EXEC getcomment 3   --有输入与输出参数的存储过程-- create proc GetCommentCount @newsid int , @ count int output as select @ count = count (*) from Comment where [email protected] Call Mode: 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 [email protected] return @ count end Call Mode: declare @cnt int exec @cnt = MyFunction 1 Print @cnt --返回值为表的函数-- Create function GetFunctionTable (@newsid int ) returns table as return ( select * from Comment where [email protected])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= ' + " '"[Email protected]+ " '"
if (@lastName <> "and @lastName is not null)
Set @sql = @sql + ' and last_name= ' + " '"[Email protected]+ " '"
EXEC (@sql)
End
GO Call Mode: exec func_withconditions ' ahg ', ' exec func_withconditions ' AHG ', NULL exec func_withconditions NULL, ' JHG '

SQL Server 2008 Stored Procedure sample