SQL SERVER 存储过程实例「建议收藏」

SQL SERVER 存储过程实例「建议收藏」
由程序中传入以下5个参数
@logNamenvarchar(50),
 @roleNamenvarchar(50),
 @userNamenvarchar(50),
 @beginTimenvarchar(50),
 @endTimenvarchar(50)
在存储过程中声明一个变量:declare@strSqlnvarchar(200)
根据传入的参数的有无,为该变量进行赋值,
set@strSql

由程序中传入以下5个参数

@logName nvarchar(50),
 @roleName nvarchar(50),
 @userName nvarchar(50),
 @beginTime nvarchar(50),
 @endTime nvarchar(50)
在存储过程中声明一个变量:declare @strSql nvarchar(200)
根据传入的参数的有无,为该变量进行赋值,

set @strSql = 'select logId as 日志编号,logName as 日志名称,roleName as 操作人角色,userName as 操作人名,logObj as 操作对象,beginState as 操作前, endState as 操作后,logNotes as 操作结果, logTime as 操作时间  from tb_Log where notes = '''' '

if @logName != ''
  set @strSql = @strSql + 'and logName ='''+ @logName +''''
 if @roleName != ''
  set @strSql = @strSql + 'and roleName = '''+@roleName+''''
 if @userName != ''
  set @strSql = @strSql + 'and userName = '''+@userName+''''
 if @beginTime != ''
  set @strSql = @strSql + 'and logTime > '''+@beginTime+''''
 if @endTime != ''
  set @strSql = @strSql + 'and logTime < '''+@endTime+''''

最后执行该SQL语句:exec(@strSql)
以下是源代码。

 

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:  <op>
-- Create date: <2010-10-12>
-- Description: <根据各种条件复合查询日志>
-- =============================================
ALTER PROCEDURE [dbo].[sp_Log_QueryLog]
 -- Add the parameters for the stored procedure here
 @logName nvarchar(50),
 @roleName nvarchar(50),
 @userName nvarchar(50),
 @beginTime nvarchar(50),
 @endTime nvarchar(50)
AS
BEGIN
 SET NOCOUNT ON;
 declare @strSql nvarchar(200)
 set @strSql = 'select logId as 日志编号,logName as 日志名称,roleName as 操作人角色,userName as 操作人名,logObj as 操作对象,beginState as 操作前, endState as 操作后,logNotes as 操作结果, logTime as 操作时间  from tb_Log where notes = '''' '
 if @logName != ''
  set @strSql = @strSql + 'and logName ='''+ @logName +''''
 if @roleName != ''
  set @strSql = @strSql + 'and roleName = '''+@roleName+''''
 if @userName != ''
  set @strSql = @strSql + 'and userName = '''+@userName+''''
 if @beginTime != ''
  set @strSql = @strSql + 'and logTime > '''+@beginTime+''''
 if @endTime != ''
  set @strSql = @strSql + 'and logTime < '''+@endTime+''''
 print @strSql
 exec(@strSql)
  
END

 

 

 

 

下面是一个修改密码的存储过程的小例子

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

 

-- =============================================
-- Author:  <op>
-- Create date: <2010-10-11>
-- Description: <修改密码>
-- =============================================
ALTER PROCEDURE [dbo].[sp_XITONG_ChangePwd]
 -- Add the parameters for the stored procedure here
 @userName nvarchar(50),
 @oldPwd nvarchar(50),
 @newPwd nvarchar(50)
AS
BEGIN

 declare @pwd nvarchar(50)
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
 
 select @pwd = userPwd from tb_User where userName = @userName
    -- Insert statements for procedure here
 if @pwd = @oldPwd
 
 Begin
   update tb_User set userPwd = @newPwd where userName = @userName
  if @@Error = 0
   return 1-- 密码更新成功
  else
   return 0-- 密码更新失败
 End

 else
  return 2-- 旧密码错误
 
END

 

 

 

架构君码字不易,如需转载,请注明出处:https://javajgs.com/archives/166128
0
 

发表评论