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