收集存储过程!(大量收集经典的和现代的存储过程的书写,希望大家不吝!)

[setfocus]

本人也是在学习阶段,希望大家赐教存储过程的书写,不论是简单的单表的增删改操作的和多表的操作,还是针对某一过程的!经典和现代的皆可!

[setfocus]

以下为浪之间所写:
CREATE PROCEDURE Pr_Modif_T_user
(@oldCity varchar,
@newCity varchar,
@ACityCode varchar,
@opflag int)
AS
declare @RI int
declare @RS varchar
if (@opflag = 1)--添加
begin
if exists(select * from Citys where City = @newCity)
begin
set @RI = 0
Set @rs = '记录重复'
end
else
begin
insert into Citys (City,CityCode)values (@newCity,@ACityCode)
set @RI = 1
Set @rs = '添加成功'
end
end else
if (@opflag = 2 )--修改
begin
if exists(select * from Citys where (City = @newCity) and (@oldCity<>City ))
begin
set @RI = 0
Set @rs = '记录重复'
end else
begin
delete from Citys where (City = @oldCity )
insert into Citys (City,CityCode)values (@newCity,@ACityCode)
set @RI = 1
Set @rs = '修改成功'
end
end else
if (@opflag = 3 )--删除
begin
delete from Citys where (City = @oldCity )
set @RI = 1
Set @rs = '删除成功'
end
select @RI as riflag,@rs as rstring from Citys
GO

[lizongqi]

gz

[lizongqi]

现眼了

@_@

CREATE PROCEDURE GetAccount
@accType VARCHAR(6)='ALL',
@accId INTEGER=0,
@currUserId INTEGER=0,
@currUserMode INTEGER=1
AS
BEGIN
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
SET NOCOUNT ON
DECLARE
@varUserName varchar(10),
@varUserLevel INTEGER,
@ErrorMsg VARCHAR(400)

/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*最新客户的精简，主页显示的最新客户*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/

IF @accType='HOME'
BEGIN
SELECT
ta.accID AS accountvID,
LEFT(ta.accName,10) AS accountvName,
CONVERT(CHAR(10),ta.accCreateDate,120) AS accountvCreateDate
FROM dbo.tblACCOUNT ta
WHERE ta.accUserId=0
ORDER BY accountvCreateDate DESC
END


/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*插入资料，打开数据集*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/

ELSE IF @accType='INSERT'
BEGIN
SELECT
ta.accID AS accountvID,
ta.accType AS accountvType,
ta.accName AS accountvName,
ta.accTrade AS accountvTrade,
ta.accNation AS accountvNation,
ta.accProvince AS accountvProvince,
ta.accCity AS accountvCity, ta.accAddr AS accountvAddr,
ta.accZip AS accountvZip, ta.accTel AS accountvTel,
ta.accFax AS accountvFax,
ta.accEMail AS accountvEMail,
ta.accWebSite AS accountvWebSite,
ta.accComments AS accountvComments,
ta.accCreateDate AS accountvCreateDate,
ta.accUserID AS accountvuserID,
ta.accLockedType AS accountvLockedType,
ta.accSource AS accountvSource,
ta.accState AS accountvState
FROM dbo.tblACCOUNT ta
WHERE ta.accId=0
END

/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/* 最新客户*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/

ELSE IF @accType='NEW'
BEGIN
SET @varUserName='未定'
SELECT
ta.accID AS accountvID,
ta.accType AS accountvType,
ta.accName AS accountvName,
ta.accCity AS accountvCity,
ta.accTel AS accountvTel,
ta.accEMail AS accountvEMail,
LEFT(ta.accComments,100) AS accountvComments,
@varUserName AS accountvUserName ,
ta.accState AS accountvState
FROM dbo.tblACCOUNT ta
WHERE ta.accUserId=0
ORDER BY accountvId

END

/*888888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*重要客户*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
ELSE IF @accType='VIP'
BEGIN
SET @varUserLevel =dbo.UserIdCheck(@currUserId)

IF @varUserLevel=1000
BEGIN
SET @ErrorMsg= '**您的用户ID在数据库中不存在，不能执行当前资料操作**'+CHAR(13)+CHAR(10)+'**如有疑问，请参照用户手册或者与管理员联系**'
RAISERROR(@ErrorMsg,16,1)
RETURN
END



IF (@varUserLevel=0) AND (@currUserMode=0)
/*所有的重要客户,高权限管理模式*/
BEGIN
SELECT
ta.accID AS accountvID,
ta.accType AS accountvType,
ta.accName AS accountvName,
ta.accCity AS accountvCity,
ta.accTel AS accountvTel,
ta.accEMail AS accountvEMail,
LEFT(ta.accComments,100) AS accountvComments,
(CASE WHEN ta.accUserId<>0 THEN
(SELECT tu.userName FROM dbo.tblUserO tu WHERE tu .UserId=ta.accUserId)
ELSE '未定'
END) AS accountvuserName,
'重要' AS accountvState
FROM dbo.tblACCOUNT ta
WHERE (ta.accState='重要')
ORDER BY accountvId
END

/*低权限，高权限使用模式*/
ELSE
BEGIN

SELECT @varUserName=tu.userName FROM dbo.tblUSERO tu WHERE tu.userId=@currUserId
SELECT
ta.accID AS accountvID,
ta.accType AS accountvType,
ta.accName AS accountvName,
ta.accCity AS accountvCity,
ta.accTel AS accountvTel,
ta.accEMail AS accountvEMail,
LEFT(ta.accComments,100) AS accountvComments,
@varUserName AS accountvuserName,
'重要' AS accountvState
FROM dbo.tblACCOUNT ta
WHERE (ta.accUserId=@currUserId) and (ta.accState='重要')
ORDER BY accountvId
END
END/*END OF VIP*/

[lizongqi]

/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*个人负责的客户*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
ELSE IF @accType='PSL'
BEGIN

SET @varUserLevel =dbo.UserIdCheck(@currUserId)

IF @varUserLevel=1000
BEGIN
SET @ErrorMsg= '**您的用户ID在数据库中不存在，不能执行当前资料操作**'+CHAR(13)+CHAR(10)+'**如有疑问，请参照用户手册或者与管理员联系**'
RAISERROR(@ErrorMsg,16,1)
RETURN
END

IF (@varUserLevel=0) AND (@currUserMode=0)/*管理员管理模式显示所有客户*/
BEGIN
SELECT ta.accID AS accountvID,
ta.accType AS accountvType,
ta.accName AS accountvName,
ta.accCity AS accountvCity,
ta.accTel AS accountvTel,
ta.accEMail AS accountvEMail,
LEFT(ta.accComments,100) AS accountvComments,
tu.userName AS accountvuserName,
ta.accState AS accountvState
FROM dbo.tblACCOUNT ta
INNER JOIN dbo.tblUSERO tu ON ta.accUserId=tu.userId
ORDER BY accountvId
END

/*低权限或者高权限使用模式*/
ELSE
BEGIN

SELECT @varUserName=tu.userName FROM dbo.tblUserO tu WHERE tu.userId=@currUserId

SELECT ta.accID AS accountvID,
ta.accType AS accountvType,
ta.accName AS accountvName,
ta.accCity AS accountvCity,
ta.accTel AS accountvTel,
ta.accEMail AS accountvEMail,
LEFT(ta.accComments,100) AS accountvComments,
@varUserName AS accountvuserName,
ta.accState AS accountvState
FROM dbo.tblACCOUNT ta
WHERE (ta.accUserId=@currUserId)
ORDER BY accountvId

END

END/*END OF PSL*/

/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*特定的客户资料*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
ELSE IF @accType='ONE'
BEGIN
IF (dbo.AccountIdCheck(@accId))=0
BEGIN
SET @ErrorMsg= '**当前操作的资料在数据库中不存在，可能已被删除**'+CHAR(13)+CHAR(10)+'**如有疑问，请参照用户手册或者与管理员联系**'
RAISERROR(@ErrorMsg,16,1)
RETURN
END

SELECT ta.accID AS accountvID,
ta.accType AS accountvType,
ta.accName AS accountvName,
ta.accTrade AS accountvTrade,
ta.accNation AS accountvNation,
ta.accProvince AS accountvProvince,
ta.accCity AS accountvCity, ta.accAddr AS accountvAddr,
ta.accZip AS accountvZip, ta.accTel AS accountvTel,
ta.accFax AS accountvFax,
ta.accEMail AS accountvEMail,
ta.accWebSite AS accountvWebSite,
ta.accComments AS accountvComments,
ta.accCreateDate AS accountvCreateDate,
ta.accUserID AS accountvuserID,
ta.accLockedType AS accountvLockedType,
ta.accSource AS accountvSource,
ta.accState AS accountvState ,
(CASE
WHEN ta.accUserId<>0 THEN
(SELECT tu.userName FROM dbo.tblUSERO tu WHERE tu.UserId=ta.accUserId)
ELSE '未定'
END) AS accountvUserName
FROM dbo.tblACCOUNT ta
WHERE ta.accId=@accId


END

/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*可以选择的客户资料*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
ELSE IF @accType='SELECT'
BEGIN
SET @varUserLevel =dbo.UserIdCheck(@currUserId)
IF @varUserLevel=1000
BEGIN
SET @ErrorMsg= '**您的用户ID在数据库中不存在，不能执行当前资料操作**'+CHAR(13)+CHAR(10)+'**如有疑问，请参照用户手册或者与管理员联系**'
RAISERROR(@ErrorMsg,16,1)
RETURN
END

IF (@varUserLevel=0) AND (@currUserMode=0)/*高权限管理模式*/
BEGIN
SELECT
ta.accID AS accountvID,
ta.accName AS accountvName
FROM dbo.tblACCOUNTO ta /*管理员管理模式可以选择所有的客户*/
ORDER BY accountvId
END

/*用户权限低或者高权限使用模式*/
ELSE
BEGIN
SELECT
ta.accID AS accountvID,
ta.accName AS accountvName
FROM dbo.tblACCOUNTO ta

WHERE ta.accUserId=@currUserId
ORDER BY accountvId
END
END /*END OF SELECT */

/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*所有客户 ALL*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/

ELSE
BEGIN

SELECT ta.accID AS accountvID,
ta.accType AS accountvType,
ta.accName AS accountvName,
ta.accCity AS accountvCity,
ta.accTel AS accountvTel,
ta.accEMail AS accountvEMail,
LEFT(ta.accComments,100) AS accountvComments,
tu.userName AS accountvuserName,
ta.accState AS accountvState
FROM dbo.tblACCOUNT ta
INNER JOIN dbo.tblUSERO tu ON ta.accUserId=tu.userId
ORDER BY accountvId

END

/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
/*88888888888888888888888888888888888888888888888888888888888888888888888888888*/
SET NOCOUNT OFF
END
GO

[setfocus]

gz

[kuangning]

我考，我那点代码，没有经过任何调试和优化，你就给我放上去了.
写这个给你的时候，我手里根本就没有安装SQL Server。我自己都不知道运行出来是什么效果！考！你娃娃居然放到这个地方来！fanit

[setfocus]

没有人响应了吗？

[happydreamer]

不贴了
自己看吧：

http://expert.csdn.net/Expert/topic/1292/1292003.xml?temp=.7813532
http://expert.csdn.net/Expert/topic/1375/1375432.xml?temp=.1264917
http://expert.csdn.net/Expert/topic/1374/1374946.xml?temp=.105343

[setfocus]

今天还有吗？每天多一点

[zhousw]

我看来看去,还是感觉没事务机制啊! 这可是大问题啊!!!

[setfocus]

那就找点事务机制的！

[setfocus]

D

[erigido]

up

[dawnming]

up