SQLSERVER 分页存储过程(2 在SQL2005下使用)
之前有一个SQLServer的分页存储过程 但是性能不是十分理想
又找了一个
--SQL2005分页存储过程
/**
if exists(select * from sysobjects where name='fenye')
drop proc fenye
**/
CREATE procedure fenye
@tableName nvarchar(200) ,
@pageSize int,
@curPage int ,
@orderBy nvarchar(200) ,
@field nvarchar(200) = '*' ,
@condition nvarchar(200)
AS
SET NOCOUNT ON
DECLARE
@STMT nvarchar(max), -- SQL to execute
@recct int -- total # of records (for GridView paging interface)
IF LTRIM(RTRIM(@condition)) = '' SET @condition = '1 = 1'
IF @pageSize IS NULL BEGIN
SET @STMT = 'SELECT ' + @field + 'from ' + @tableName +'WHERE ' + @condition + 'ORDER BY ' + @orderBy
EXEC (@STMT) -- return requested records
END ELSE BEGIN
SET @STMT = 'SELECT @recct = COUNT(*) from ' + @tableName + ' WHERE ' + @condition
-- EXEC sp_executeSQL @STMT, @params = N'@recct INT OUTPUT', @recct = @recct OUTPUT
--SELECT @recct AS recct -- return the total # of records
DECLARE
@lbound int,
@ubound int
SET @curPage = ABS(@curPage)
SET @pageSize = ABS(@pageSize)
IF @curPage < 1 SET @curPage = 1
IF @pageSize < 1 SET @pageSize = 1
SET @lbound = ((@curPage - 1) * @pageSize)
SE
相关文档：
create function comm_getpy
(
@str nvarchar(4000)
)
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--如果非汉字字符，返回原字符
& ......
SQLserver字符串分割函数
一、按指定符号分割字符串，返回分割后的元素个数，方法很简单，就是看字符串中存在多少个分隔符号，然后再加一，就是要求的结果。
CREATE function Get_StrArrayLength
(
@str varchar(1024), --要分割的字符串
@split varchar(10) --分隔符� ......
sqlserver字符串拆分(split)方法汇总
--方法0：动态SQL法
declare @s varchar(100),@sql varchar(1000)
set @s='1,2,3,4,5,6,7,8,9,10'
set @sql='select col='''+ replace(@s,',',''' union all select ''')+''''
PRINT @sql
exec (@sql)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ ......
