SQL Server 2008格納プロセス例

出典:http://www.jb51.net/article/54730.htm -- --createproc GetComment(@commentid int)asselect* fromComment whereCommentID=@commentid 
呼び出し方式：exec GetComment 3   -- --createproc GetCommentCount@newsid int,@countintoutputasselect@count=count(*) fromComment whereNewsID=@newsid 
呼び出し方法:
declare@cnt int
exec GetCommentCount 1,@cnt  out put
print@cnt
  -- --createfunctionMyFunction(@newsid int)returnsintasbegindeclare@countintselect@count=count(*) fromComment whereNewsID=@newsidreturn@countend 
呼び出し方法:
declare@cnt int
exec@cnt=MyFunction1
print@cnt
  -- --CreatefunctionGetFunctionTable(@newsid int)returnstableasreturn(select* fromComment whereNewsID=@newsid)go   ：select* from・GetFunctionTable(2) 
CREATE proc func_withconditions
(
 @first Name varhar（20）、
 @lastName varhar（20）
)
AS
begin
    declare@sql varhar(500)
    set@sql='select*from eployee where 1=1 '
    if(@first Name is not null)
          set@sql=@sql+'and first_name='＋
'''+@first Name+
''
    if(@lastName<>'and@lastName is not null)
          set@sql=@sql+'and last_name='＋
'''+@lastName+
''
    exec(@sql)
end
GO
 
呼び出し方法:
exec func_withconditions'ah'
exec func_withconditions'ah'NULL
exec func_withconditions NULL、'jhg'