sql 2008 存储过程 积累 分页sql 多类分别
GO
/****** Object: UserDefinedFunction [dbo].[f_splitSTR] Script Date: 12/26/2008 18:22:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[f_splitSTR](
@s varchar(8000), --要分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
T-SQL象数组一样处理字符串,分割字符串
T-SQL对字符串的处理能力比较弱，比如我要循环遍历象1,2,3,4,5这样的字符串，如果用数组的话，遍历很简单，但是T-SQL不支持数组，所以处理下来比较麻烦。下边的函数，实现了象数组一样去处理字符串。
一、按指定符号分割字符串，返回分割后的元素个数，方法很简单，就是看字符串中存在多少个分隔符号，然后再加一，就是要求的结果。
CREATE function Get_StrArrayLength
(
@str varchar(1024), --要分割的字符串
@split varchar(10) --分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int
declare @len int
set @len=len(@split) --add
set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
set @start=@location+@len
set @location=charindex(@split,@str,@start)
set @length=@length+1
end
return @length
end
调用示例：select dbo.Get_StrArrayLength('78,1,2,3',',')
返回值：4
二、按指定符号分割字符串，返回分割后指定索引的第几个元素，象数组一样方便
CREATE function Get_StrArrayStrOfIndex
(
@str varchar(1024), --要分割的字符串
@split varchar(10), --分隔符号
@index int --取第几个元素
)
returns varchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split)
set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@str,@start)
set @next=@next+1
end
if @location =0 select @location =len(@str)+1
--这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。
return substring(@str,@start,@location-@start)
end
调用示例：select dbo.Get_StrArrayStrOfIndex('8,9,4',',',2)
返回值：9
三、结合上边两个函数，象数组一样遍历字符串中的元素
declare @str varchar(50)
set @str='1,2,3,4,5'
declare @next int
set @next=1
while @next<=dbo.Get_StrArrayLength(@str,',')
begin
print dbo.Get_StrArrayStrOfIndex(@str,',',@next)
set @next=@next+1
end
调用结果：
1
2
3
4
5
===================================================
===================================================
create proc USP_SelectPSH_InformationSimple
@pCreateTime datetime =null,
@pCreateTimeEnd datetime =null,
@pUpdateTime datetime=null,
@pUpdateTimeEnd datetime =null
as
SELECT [ItemID]
,[EditionType]
,[RegionCode]
,[BeginTime]
,[EndTime]
,[TargetUrl]
,[DisplayText]
,[InfoName]
,[InfoData]
,[CreateTime]
,[UpdateTime]
FROM [BASDB].[dbo].[PSH_InformationSimple] where CreateTime >= ISNULL(@pCreateTime,'1900-01-01') and CreateTime <= ISNULL(@pCreateTimeEnd,'9999-01-01') and UpdateTime >=ISNULL(@pUpdateTime,'1900-01-01')
and UpdateTime <=ISNULL(@pUpdateTimeEnd,'9999-01-01')
GO
DROP PROC USP_SelectPSH_InformationSimple
分页
-- =============================================
-- Author:
-- Create date: 2008-10-9
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[USP_LOG_UserBasedSecurityGdsQuery]
@pPageIndex int,
@pPageSize int,
@pAction nvarchar(100)
AS
BEGIN
declare @Action nvarchar(100)
select @Action = '%'+@pAction+'%';
select * from LOG_UserBasedSecurity as m
inner join
(
select * from
(
select LogID from
(
select LogID, row_number() over( order by LogID desc) as rn from LOG_UserBasedSecurity
WHERE [Action] like @Action
) as s
where s.rn >@pPageSize*@pPageIndex and s.rn <= @pPageSize*(@pPageIndex+1)
) as d
) as c
on m.LogID = c.LogID
Select Count(*) From LOG_UserBasedSecurity
WHERE [Action] like @Action
END
-----------------------------------------------------------------------------
按照大类 查找出每个大类别的前9个小类别
select small_type_id,pro_big_type_id,small_type_name from pro_small_type a
where small_type_id in (select top 9 small_type_id from pro_small_type where pro_big_type_id = a.pro_big_type_id)
-----------------------------------------------------------------------------
普通事物
begin transaction sqlArticletransaction
update FeatureArticle set isDisplay=2 where; IF @@ERROR<>0 begin rollback transaction sqlArticletransaction end else begin commit transaction sqlArticletransaction end;
DECLARE Employee_Cursor CURSOR FOR
select UnitID from unitinfo where DataTemplateID=29
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor into @unitid;
WHILE @@FETCH_STATUS = 0
BEGIN
select min(id) from Choose where unitid=@unitid group by issn,unitid,magazinetype)
END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor
update a set a.a2=b.b2 from b where a.a1=b.b1
