声明:本例转自http://hi.chinaunix.net/?214950/viewspace-1713,主要是为了学习之用
比如有个表，内容如下
id,text
1,春花秋月何时了
2,往事知多少
3,小楼昨夜又春风
4,古国不堪回首月明中
......
其中id为系列号，text为文本内容，我想使用个sql语句，达到如下效果：
查询text列，查询的值累加，即查询结果显示如下：
春花秋月何时了 往事知多少 小楼昨夜又春风 古国不堪回首月明中 ...
用存储过程+游标实现，示例如下
ALTER PROCEDURE [dbo].[abc]
-- Add the parameters for the stored procedure here
@p1 int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @ttt varchar(100);
declare @bbb varchar(10);
set @ttt=''
set @bbb=''
declare mycur cursor for
select meno from test where gid= @p1
open mycur
fetch next from mycur into @bbb
WHILE @@FETCH_STATUS = 0
BEGIN
set @ttt=@ttt+@bbb
fetch next from mycur into @bbb
end
close mycur
select @ttt
-- Insert statements for procedure here
END
下面是自己的小测试:
ALTER PROCEDURE dbo.fetchtest
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
declare @ss varchar(2000),@aa varchar(2000),@cc varchar(2000)
set @ss=''
set @aa=''
set @cc=''
SELECT @@CURSOR_ROWS
DECLARE authors_cursor CURSOR FOR
SELECT top 100 RegionalName,userid FROM areaInfo
OPEN authors_cursor
FETCH NEXT FROM authors_cursor into @ss,@cc--这里要与字段数相同
while @@FETCH_STATUS=0
begin
set @aa=@aa+ltrim(@cc)
FETCH NEXT FROM authors_cursor into @ss,@cc--这里要与字段数相同
end
SELECT @@CURSOR_ROWS
SELECT @aa
CLOSE authors_cursor
DEALLOCATE authors_cursor
posted on
