USE [MusicOnline]
GO
/****** 对象: StoredProcedure [dbo].[pro__User_GetIdByName] 脚本日期: 04/27/2010 20:43:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: LWK
-- Create date: 2010-04-17
-- Description: 获取用户ID
-- Result: 正确返回ID值，错误返回0
-- =============================================
CREATE PROCEDURE [dbo].[pro__User_GetIdByName]
@uName VARCHAR(128),
@@uId INT output
AS
BEGIN
SET NOCOUNT ON;
if exists (SELECT * FROM [tb_UserInfo] WHERE [uName]=@uName)
SELECT @@uId=[uID] FROM [tb_UserInfo] WHERE [uName]=@uName;
else
set @@uId = 0;
return @@uId;
END
GO
/****** 对象: StoredProcedure [dbo].[pro_User_Add] 脚本日期: 04/27/2010 20:43:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: LWK
-- Create date: 2010-04-17
-- Description: 增加用户
-- Input： 用户名，密码，类名，昵称，E-Mail,生日
-- return: 成功返回用户Id,失败返回<0 返回-1,表示用户组不存在
-- =============================================
CREATE PROCEDURE [dbo].[pro_User_Add]
@uName VARCHAR(128),
@uPwd VARCHAR(256),
@uGroupName VARCHAR(64),
@uNiName VARCHAR(128) = NULL,
@uEmail VARCHAR(128) = NULL,
@uBirthday DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON;
declare @@uGroupId INT;
declare @uId INT;
exec pro_UserGroup_GetIdByName @uGroupName, @@uGroupId output;
if(@@uGroupId = 0)
begin
SELECT '错误:用户组不存在!';
return -1;
end
if exists (SELECT * FROM [tb_UserInfo] WHERE [uName]=@uName)
begin
SELECT '错误:该用户名已存在!';
return -2;
end
INSERT INTO [tb_UserInfo](uName,uPwd,uGroupId,uNiName,uEmail,uBirthday,uRegTime) VALUES (@uName,@uPwd,@@uGroupId,@uNiName,@uEmail,@uBirthday,GetDate());
if exists (SELECT * FROM [tb_UserInfo] WHERE [uName]=@uName)
begin
SELECT @uId=[uId] FROM [tb_UserInfo] WHERE [uName]=@uName;
return @uId;
end
else
begin
Set @uId = 0;
SELECT '数据库插入异常!请检查所填格式是否有错误!';
return -3;
end
END
GO
/****** 对象: StoredProcedure [dbo].[pro__UserGroup_GetIdByName] 脚本日期: 04/27/2010 20:43:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: LWK
-- Create date: 2010-04-17
-- Description: 获取用户组ID
-- Result: 正确返回ID值，错误返回0
-- =============================================
CREATE PROCEDURE [dbo].[pro__UserGroup_GetIdByName]
@uGroupName VARCHAR(128),
@@uGroupId INT output
AS
BEGIN
SET NOCOUNT ON;
if exists (SELECT * FROM [tb_UserGroup] WHERE [ugName]=@uGroupName)
SELECT @@uGroupId=[ugId] FROM [tb_UserGroup] WHERE [ugName]=@uGroupName;
else
SET @@uGroupId = 0;
return @@uGroupId;
END
GO
/****** 对象: StoredProcedure [dbo].[pro_User_Modify] 脚本日期: 04/27/2010 20:43:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: LWK
-- Create date: 2010-04-24
-- Description: 修改用户信息
-- Result: 成功>0,失败<0并返回错误信息
-- =============================================
CREATE PROCEDURE [dbo].[pro_User_Modify]
@uId INT,
@uName VARCHAR(128),
@uPwd VARCHAR(256),
@uGroupName VARCHAR(64),
@uScroe INT,
@uNiName VARCHAR(128) = NULL,
@uEmail VARCHAR(128) = NULL,
@uBirthday DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON;
if( @uId=0 and @uName='' )
begin
select '用户Id和用户名不能同时为空！';
return -1;
end
declare @@ugId INT;
if exists( select * from [tb_UserInfo] where [uId]=@uId or [uName]=@uName )
begin
exec pro_UserGroup_GetIdByName @uGroupName,@@ugId output
if(@@ugId=0)
begin
select '用户组不存在';
return -2;
end
update [tb_UserInfo] set
[uPwd] = @uPwd,
[uGroupId] = @@ugId,
[uNiName] = @uNiName,
[uEmail] = @uEmail,
[uBirthday] = @uBirthday,
[uScore] = @uScroe
where [uId]=@uId or [uName]=@uName;
select * from [tb_UserInfo] where [uId]=@uId or [uName]=@uName
return 1;
end
else
begin
select '该用户不存在';
return -3;
end
END
GO
/****** 对象: StoredProcedure [dbo].[pro__Common_Query] 脚本日期: 04/27/2010 20:43:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: lwk
-- Create date: 2010-04-24
-- Description: 通用查询
-- Result: 正常返回1,失败返回<0
-- =============================================
CREATE PROCEDURE [dbo].[pro__Common_Query]
@tb_name VARCHAR(50),
@where VARCHAR(256),
@topCount INT = 0,
@DISTINCT BIT = 0,
@orderBy VARCHAR(32) = '',
@groupBy VARCHAR(32) = '',
@having VARCHAR(30) = '',
@ASCorDESC VARCHAR(5) = ''
AS
BEGIN
SET NOCOUNT ON;
if( @tb_name = '' or @tb_name = NULL)
begin
select '表名不能为空！';
return -1;
end
declare @queryWords VARCHAR(320);
set @queryWords = 'select ';
if(@DISTINCT <> 0 )set @queryWords = @queryWords + ' DISTINCT ';
if(@topCount <> 0 ) set @queryWords = @queryWords + ' top ' + cast(@topCount as VARCHAR(10));
set @queryWords = @queryWords + ' * from ' + @tb_name;
if(@where <> '') set @queryWords = @queryWords + ' where ' + @where;
if(@groupBy <> '')
begin
set @queryWords = @queryWords + ' group by ' + @groupBy;
if(@having <> '') set @queryWords = @queryWords + ' having ' + @having;
end
if(@orderBy <> '')
begin
set @queryWords = @queryWords + ' order by ' + @orderBy;
if(@ASCorDESC <> '') set @queryWords = @queryWords + ' ' + @ASCorDESC;
end
print @queryWords
EXECUTE (@queryWords);
return 1;
END
GO
/****** 对象: StoredProcedure [dbo].[pro_User_Query] 脚本日期: 04/27/2010 20:43:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: lwk
-- Create date: 2010-04-24
-- Description: 查询用户信息
-- Result: 1
-- =============================================
CREATE PROCEDURE [dbo].[pro_User_Query]
@condition VARCHAR(256),
@topCount INT = 0
AS
BEGIN
SET NOCOUNT ON;
declare @result int;
exec @result = pro__Common_Query 'tb_UserInfo',@condition, @topCount ;
return @result;
END
GO
/****** 对象: StoredProcedure [dbo].[pro_User_Delete] 脚本日期: 04/27/2010 20:43:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: lwk
-- Create date: 2010-04-24
-- Description: 删除用户
-- Result: 成功>0,失败<0
-- =============================================
CREATE PROCEDURE [dbo].[pro_User_Delete]
@uId INT = 0,
@uName VARCHAR(128) = ''
AS
BEGIN
SET NOCOUNT ON;
if(@uId=0 and @uName='' )
begin
select '用户Id和用户名不能同时为空！';
return -1;
end
if not exists( select * from [tb_UserInfo] where [uId]=@uId or [uName]=@uName )
begin
select '该用户不存在！';
return -2;
end
delete from [tb_UserInfo] where [uId]=@uId or [uName]=@uName;
return 1;
END
GO
/****** 对象: StoredProcedure [dbo].[pro_UserGroup_Add] 脚本日期: 04/27/2010 20:43:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: LWK
-- Create date: 2010-04-24
-- Description: 添加用户组
-- Result: 成功返回ID,失败返回<0
-- =============================================
CREATE PROCEDURE [dbo].[pro_UserGroup_Add]
@ugName VARCHAR(64) = '',
@ugDscrp VARCHAR(128)
AS
BEGIN
SET NOCOUNT ON;
if (@ugName = '')
begin
select '用户名不能为空!';
return -1;
end
if exists ( select * from [tb_UserGroup] where [ugName]=@ugName )
begin
select '该用户组存已存在!';
return -2;
end
insert into [tb_UserGroup](ugName,ugDscrp,ugCreateTime) values (@ugName,@ugDscrp,GetDate());
declare @ugId INT;
select @ugId=[ugId] from [tb_UserGroup] where [ugName]=@ugName;
return @ugId;
END
GO
/****** 对象: Store
在线音乐欣赏系统_数据库设计.rar （2个子文件） 
StoredProcddure.sql 44KB DataBase.sql 10KB资源评论
z6463459082012-12-28可以运行，多谢- 岁月静好现世安稳2013-10-13表格比较全面，但没有文字说明，不是很清晰。
- commy2013-04-03没有仔细看，没什么意思
- 开心的哈鲁2012-11-21用不了的啊，给的东西不齐全。
- 胡萝卜学习飞行2013-08-10只是建立了表和函数，没有文字说明，不是很明晰。
wenkeliu
- 粉丝: 8
- 资源: 51
最新资源
- redis-standalone.yml redis k8s单点部署
- 2000-2023年省级产业结构升级数据-最新出炉.zip
- Python基于Scrapy兼职招聘网站爬虫数据分析设计（源码）
- zipkin.yml zipkin k8s部署
- YY9706.102-2021医用电气设备第2-47部分
- 通过运用时间序列ARIMA模型与循环神经网络（LSTM）对中国包装机器数量进行预测（python源码）
- 基于ARIMA模型的股票预测（python源码）
- 基于阿里云对象存储的对文件进行批量修改、批量解冻、批量上传
- 山东联通-海信IP501H-GK6323V100C-1+8G-4.4.2-当贝桌面-卡刷包
- IMG_6338.PNG
资源上传下载、课程学习等过程中有任何疑问或建议，欢迎提出宝贵意见哦~我们会及时处理！
点击此处反馈
