SQL____一个简单的存储过程

最新推荐文章于 2024-10-23 17:19:43 发布

转载最新推荐文章于 2024-10-23 17:19:43 发布 · 294 阅读

文章标签：

#SQL

sql 专栏收录该内容

12 篇文章

订阅专栏

本文介绍了一个用于在数据库中插入雇员记录的存储过程。该过程检查姓名是否已存在，进行事务处理，并根据需要更新权限。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/****** Object:  StoredProcedure [dbo].[sp_InsertEmployee]    Script Date: 09/17/2012 23:28:42 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    插入一条雇员数据
-- =============================================
CREATE PROCEDURE [dbo].[sp_InsertEmployee]
-- Add the parameters for the stored procedure here
@Name varchar(50),
@UserName varchar(50),
@Password varchar(50),
@Hierarchy char(1),
@EmployeeTypeID int,
@Sex varchar(5),
@Telphone varchar(20),
@CellPhone varchar(20),
@QQ varchar(20),
@Email varchar(50),
@Statue varchar(20),
@Remark varchar(50),
@ManagerID int,
@Regions varchar(1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @ID int
declare @tempSql varchar(2000)


-- Insert statements for procedure here
if exists(select * from Employee where Name=@Name)
    return 0
begin transaction


    insert into [Employee](
    [Name],[UserName],[Password],[Hierarchy],[EmployeeTypeID],[Sex],[Telphone],[CellPhone],[QQ],[Email],[Statue],[Remark],[ManagerID]
    )values(
    @Name,@UserName,@Password,@Hierarchy,@EmployeeTypeID,@Sex,@Telphone,@CellPhone,@QQ,@Email,@Statue,@Remark,@ManagerID
    )
    set @ID = @@IDENTITY
    if exists(select * from Permission where ID=@ID)
    begin
        delete from Permission where EmployeeID=@ID
    end
    else
    begin
        set @tempSql='insert into Permission select '+str(@ID)+', '''+replace(@Regions,',','''union select '+str(@ID)+',''')+''''
        exec(@tempSql)
    end
    
if @@ERROR>0
    begin
    rollback transaction
    end
else
    begin
    commit transaction
    end
    
END


GO