sql server 存储过程例子

之前对存储过程不是很熟悉，现在写出来例子，以后用。会持续更新的。

例子 修改存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author:        <Author,,xxx>
-- Create date: <Create Date,11/16/2010,>
-- Description:    <Description,修改tbl_admin_config的数据，>
-- =============================================
ALTER PROCEDURE [dbo].[sp_admin_config_update] 
    -- Add the parameters for the stored procedure here

    @typename      nvarchar(50),
    @typevalue     nvarchar(10)
AS
BEGIN

    -- Insert statements for procedure here
    UPDATE database_name.dbo.tbl_admin_config SET typevalue=@typevalue
     WHERE typename=@typename

END

例子 查询存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



-- =============================================
-- Author:        <Author,xxx>
-- Create date: <Create Date,11/16/2010,>
-- Description:    <Description,查询tbl_admin_config,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_admin_config_list] 
    -- Add the parameters for the stored procedure here

    @typeid       bigint = NULL,           --id
    @typename     nvarchar(50) = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SELECT * FROM database_name.dbo.tbl_admin_config
     where (typeid=@typeid or @typeid is null)
             and (typename=@typename or @typename is null)
END

例子：插入存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:        <Author,,SC_Zhang>
-- Create date: <Create Date,2011-05-03,>
-- Description:    <Description,往tbl_exam_materials插入一条记录>
-- =============================================

CREATE PROCEDURE [dbo].[tbl_exam_materials_insert]
-- Add the parameters for the stored procedure here

    @exam_group_id  int,
    @exam_type_name nvarchar(50) = NULL,
    @exam_name        nvarchar(200) = NULL,
    @exam_code        nvarchar(20),
    @exam_quota        int,
    @exam_account    bigint,
    @exam_fee        int,
    @exam_prereq    nvarchar(100)=NULL,
    @exam_upload_material_path  varchar(200)=NULL,
    @exam_app_form_path            varchar(200)=NULL,
    @exam_remark    nvarchar(200)=NULL,
    @create_date    datetime=NULL,
    @created_by        nvarchar(50)=NULL,
    @modify_date    datetime=NULL,
    @modified_by    nvarchar(50)=NULL
    
AS
BEGIN

    -- Insert statements for procedure here
    INSERT INTO [datebase_name].[dbo].[tbl_exam_materials]
            (exam_group_id,exam_type_name,exam_name,exam_code,exam_quota,exam_account,exam_fee,exam_prereq,exam_upload_material_path,exam_app_form_path,exam_remark,create_date,created_by,modify_date,modified_by)
    VALUES
            (@exam_group_id,
            @exam_type_name,            
            @exam_name,
            @exam_code,
            @exam_quota,
            @exam_account,
            @exam_fee,
            @exam_prereq,
            @exam_upload_material_path,
            @exam_app_form_path,
            @exam_remark,
            @create_date,
            @created_by,
            @modify_date,
            @modified_by)
END