简单的存储过程
USE pubs
IF EXISTS (SELECT name  FROM sysobjects WHERE name='first_proc'  AND type='P'  )
DROP PROCEDURE first_proc
GO
CREATE PROCEDURE first_proc 
AS
SELECT au_lname,title
    FROM authors AS a INNER JOIN titleauthor AS ta 
            ON a.au_id=ta.au_id INNER JOIN titles AS t
            ON ta.title_id=t.title_id
GO
exec first_proc
/**********************************************************************************/
带输入参数
USE pubs
IF EXISTS (SELECT name  FROM sysobjects WHERE name='first_proc'  AND type='P'  )
DROP PROCEDURE first_proc
GO
CREATE PROCEDURE first_proc 
   @lastname varchar(40),@firstname varchar(20)
AS
SELECT au_lname, au_fname, title,pub_name
    FROM authors AS a INNER JOIN titleauthor AS ta 
            ON a.au_id=ta.au_id INNER JOIN titles AS t
            ON ta.title_id=t.title_id INNER JOIN publishers p
            ON t.pub_id=p.pub_id
   WHERE au_lname=@lastname AND au_fname=@firstname
GO
exec first_proc 'White','hh'
**********************************************************************************/
带输入 和输出参数
USE pubs
IF EXISTS (SELECT name  FROM sysobjects WHERE name='first_proc'  AND type='P'  )
DROP PROCEDURE first_proc
GO
CREATE PROCEDURE first_proc 
   @title varchar(40)='默认值%',@sum money OUTPUT
AS
SELECT @sum=SUM(price)
    FROM titles
   WHERE title LIKE @title
GO
DECLARE @sum money
exec first_proc 'The%',@sum  OUTPUT
if @sum<200
   begin
     print ''
     print 'the sum is less than $200'
else
     select 'the sum is' +RTRIM (CAST(@sum as varchar(20)))