procedures

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[SP_CreateData_Demo]
 
AS
 declare
 @a int
 set @a=1
begin
 while @a<45000
 begin
  insert into Person_User_Info(PersonID,UserCode,UserPassword,DepartmentIDs,登陆次数,登陆IP,登陆时间,最后访问时间,ProjectID) values(cast(@a as varchar),'userCode'+cast(@a as varchar),'Password'+cast(@a as varchar),'Department'+cast(@a as varchar),1,'192.168.0.1',getdate(),getdate(),100)
  set @a = @a+1
 end
end

 

 

 

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SP_DELETE_USER]
 -- Add the parameters for the stored procedure here
   @UserID int=0 --the selected user id
     ,@UserCode varchar(50) --the search criteria
  ,@UserSID int=0 --the search criteria
AS
 declare
    @SearchUserSQL nvarchar(200)
   ,@DeleteUserSQL varchar(200)
    set @SearchUserSQL = 'select * from Person_User_Info'
    set @DeleteUserSQL= 'delete from Person_User_Info'

  Begin try
 set nocount on;
 Begin transaction
  if @UserID<>0
   begin
    set @DeleteUserSQL = @DeleteUserSQL+' where [UserID]='+cast(@UserID as varchar)
   end
  if @UserSID<>0
   begin
    set @SearchUserSQL = @SearchUserSQL +' where UserID like %' + cast(@UserSID as varchar) + '%'
    if @UserCode is not null
     begin
      set @SearchUserSQL = @SearchUserSQL+' and UserCode like %' + @UserCode + '%'
     end
   end
  else if @UserCode is not null
    begin
    set @SearchUserSQL = @SearchUserSQL+' where UserCode like %' + @UserCode + '%'
    end
  exec(@DeleteUserSQL)
  exec(@SearchUserSQL)
 commit transaction
  end try
  begin catch
 declare @ErrMsg varchar(1000)
 set @ErrMsg = error_message()
 rollback transaction
 raiserror(@ErrMsg,14,1)
 return
 end catch
 print(@SearchUserSQL)
 exec(@SearchUserSQL)

 

 

 

 

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SP_GetCurrent_INFO]
 -- Add the parameters for the stored procedure here
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
 select convert(varchar(30),getdate(),111)+' '+convert(varchar(20),getdate(),108),datename(weekday,getdate()),datediff(day,getdate(),'05-14-2010')    
END

 

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SP_PAGING_SQL]
 -- Add the parameters for the stored procedure here
 @TableName varchar(100)
   ,@PrimaryKey varchar(50)
   ,@CurrentPageIndex int =1
   ,@CountPerPage int = 10
   ,@WhereStr varchar(100)
   ,@TotalCount int output
AS
 declare
  @searchSQL nvarchar(500)
  ,@searchTotalSQL nvarchar(500)
  set @searchSQL=''
  set @searchTotalSQL=''
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
 if @CurrentPageIndex=1
  begin
   if @WhereStr is not null
    begin
     set @searchSQL = 'select top '+cast(@CountPerPage as varchar)+' * from '+@TableName+' '+ @WhereStr +' order by '+@PrimaryKey
    end
   else
    begin
     set @searchSQL = 'select top '+cast(@CountPerPage as varchar)+' * from '+@TableName+' order by '+@PrimaryKey
    end
  end
 else
  if @WhereStr is not null
   begin
    set @searchSQL = 'select top '+cast(@CountPerPage as varchar)+' * from '+@TableName+' where '+@PrimaryKey
    +' > (select max('+@PrimaryKey+') from (select top '+cast((@CurrentPageIndex-1)*@CountPerPage as varchar)+' '+@PrimaryKey+' from '+@TableName+' order by '+@PrimaryKey+' ) as t)  '+replace(@WhereStr,'where','and')+' order by '+@PrimaryKey
   end
  else
   begin
    set @searchSQL = 'select top '+cast(@CountPerPage as varchar)+' * from '+@TableName+' where '+@PrimaryKey
    +' > (select max('+@PrimaryKey+') from (select top '+cast((@CurrentPageIndex-1)*@CountPerPage as varchar)+' '+@PrimaryKey+' from '+@TableName+' order by '+@PrimaryKey+' ) as t) order by '+@PrimaryKey
   end
  exec(@searchSQL)
 if @WhereStr is not null
  set @searchTotalSQL='select @TotalCount = count(*) from '+@TableName+' '+@WhereStr
 else
  set @searchTotalSQL='select @TotalCount = count(*) from '+@TableName
 exec sp_executesql @searchTotalSQL ,N'@TotalCount as int output',@TotalCount output
 
END