可選參數的Stored Procedure範例.

/*
Author : dream.meng
 MSN: emyx@msn.com

Sample Script:

 sp_GetAuthors
 sp_GetAuthors 'White'
 sp_GetAuthors 'White','Johnson','Menlo Park'
 sp_GetAuthors '','',''
 
*/

CREATE  PROCEDURE sp_GetAuthors
     @au_lname varchar(40)='',
     @au_fname varchar(20)='',
     @city varchar(20)=''

 AS

select *
from pubs..Authors
where 1 = 1
 and @au_lname = case when @au_lname = '' then @au_lname else au_lname end
 and @au_fname = case when @au_fname = '' then @au_fname else au_fname end
 and @city = case when @city = '' then @city else city end

go