#一段存储过程

create table books (

    book_id int identity(1,1) primary key,

    book_name varchar(20),

    book_price float,

    book_auth varchar(10)

);

--插入测试数据

insert into books (book_name,book_price,book_auth)

                    values

                        ('论语',25.6,'孔子'),

                        ('天龙八部',25.6,'金庸'),

                        ('雪山飞狐',32.7,'金庸'),

                        ('平凡的世界',35.8,'路遥'),

                        ('史记',54.8,'司马迁');

if (exists (select * from sys.objects where name = 'getAllBooks'))

    drop proc proc_get_student

go

--创建存储过程

create procedure getAllBooks

as

select * from books;

go

--修改存储过程

alter procedure dbo.getAllBooks

as

select book_auth from books;

--删除存储过程

drop procedure getAllBooks;

--重命名存储过程

sp_rename getAllBooks,proc_get_allBooks;

--调用,执行存储过程

exec getAllBooks;

--创建带参数的存储过程1

create proc searchBooks(@bookID int)

as

    select * from books where book_id=@bookID;

exec searchBooks 1;

--带2个参数存储过程

create proc searchtwoBooks(

@bookID int,

@bookAuth varchar(20),

@booName varchar(20)

)

as

    select * from books where book_id=@bookID and book_auth = @bookAuth;

exec searchtwoBooks 2,'金庸';

--带输出参数的存储过程

if (exists (select * from sys.objects where name = 'getBookId'))

    drop proc getBookId

go

create proc getBookId(

    @bookAuth varchar(20),

    @bookName varchar(20) output

)

as

    select @bookName=book_name from books where book_auth=@bookAuth

declare @bookname varchar(20)

exec getBookId '孔子',@bookName output

select @bookName as bookname;

if (exists (select * from sys.objects where name = 'orderlist'))

    drop proc orderlist

go

create proc orderlist(

    @orderno varchar(20),

    @product varchar(20)

)

as

insert into t_product (orderno,product) VALUES(@orderno,@product)

--    select @bookName=book_name from books where book_auth=@bookAuth

declare @orderno varchar(20),

@product varchar(20)

set @orderno='ANS'+CONVERT(CHAR(10),GETDATE(),112)+'000002'

set @product='电视机'

exec orderlist @orderno,@product

---------------------------有用---------------------------------------

CREATE  TABLE [dbo].[SriaNum] (

    [Num] [int] NOT NULL

)

CREATE  PROC dpIDS_GetSerialNumber

@SerialNumber VARCHAR(9) OUTPUT  -- 指明为输出参数

AS

IF NOT EXISTS(SELECT

            *

        FROM

            SriaNum)     

    BEGIN

        INSERT INTO SriaNum values(1)

    END

ELSE

    BEGIN

        UPDATE SriaNum SET Num=Num+1

    END

SELECT

        @SerialNumber = REPLICATE('0',9-LEN(Num))+CONVERT(VARCHAR(9),Num)  --生成[000000001, 999999999]范围内的流水号

FROM

        SriaNum

DECLARE

@TEST VARCHAR(9)

EXECUTE [dbo].dpIDS_GetSerialNumber @TEST OUTPUT  -- 指明为输出变量

SELECT @TEST AS SERIALNUMBER    -- 获得流水号

-----------------------------------------解决并发问题-------------------------------------------

CREATE  TABLE [dbo].[SriaNum] (

    [Num] [int] NOT NULL

)

-- WAITFOR DELAY '0:0:1'

CREATE  PROC dpIDS_GetSerialNumber

@SerialNumber VARCHAR(9) OUTPUT  -- 指明为输出参数

AS

IF NOT EXISTS(SELECT

            *

        FROM

            SriaNum)

    BEGIN

        INSERT INTO SriaNum values(1)

    END

ELSE

BEGIN TRANSACTION

    BEGIN

        UPDATE SriaNum SET Num=Num+1

    END

WAITFOR DELAY '0:0:3'

COMMIT

SELECT

        @SerialNumber = 'ANS'+REPLICATE('0',6-LEN(Num))+CONVERT(VARCHAR(9),Num)

FROM

        SriaNum

DECLARE

@TEST VARCHAR(9)

EXECUTE [dbo].dpIDS_GetSerialNumber @TEST OUTPUT  -- 指明为输出变量

SELECT @TEST AS SERIALNUMBER    -- 获得流水号