关于游标问题，急急。

[5027527]

DECLARE abc CURSOR FOR
SELECT * FROM GoodsSpec where commodityno ='0010000101' order by PackRate

OPEN abc

FETCH NEXT FROM abc
WHILE (@@FETCH_STATUS = 0)
FETCH NEXT FROM abc

CLOSE abc
DEALLOCATE abc
GO

如上所示，虽然可以列出游标所在行，但我如何根据当前游标而取得每一个字段的值呢？
比如我要根据游标实现类似的功能：
SELECT @aa=PackRate FROM GoodsSpec where commodityno ='0010000101' order by PackRate

谢谢。

[hh_fwhy]

我给你一段程序,我改写的系统存储过程,你可以直接用,不过这个东西要用到存储过程,中间提到的其他东西,你可以忽略:
获得字段名和特定值的存储过程:

/****** Object: Stored Procedure dbo.dt_column Script Date: 2002-4-25 16:39:16 ******/

/****** Object: Stored Procedure dbo.dt_column Script Date: 2002-4-24 13:35:26 ******/
CREATE PROCEDURE dt_column
@table_name nvarchar(384),
@count int output ,
@cur_column CURSOR VARYING OUTPUT

AS
DECLARE @full_table_name nvarchar(769)
DECLARE @table_id int
DECLARE @table_owner nvarchar(384),
@table_qualifier sysname ,
@column_name nvarchar(384) ,
@ODBCVer int,
@name varchar(50)
SET @table_owner = NULL
SET @table_qualifier = NULL
SET @column_name = NULL
SET @ODBCVer = 2

if @ODBCVer <> 3
select @ODBCVer = 2
if @column_name is null /* If column name not supplied, match all */
select @column_name = '%'
if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin /* If qualifier doesn't match current database */
raiserror (15250, -1,-1)
return
end
end
if @table_name is null
begin /* If table name not supplied, match all */
select @table_name = '%'
end
if @table_owner is null
begin /* If unqualified table name */
SELECT @full_table_name = quotename(@table_name)
end
else
begin /* Qualified table name */
if @table_owner = ''
begin /* If empty owner name */
SELECT @full_table_name = quotename(@table_owner)
end
else
begin
SELECT @full_table_name = quotename(@table_owner) +
'.' + quotename(@table_name)
end
end

/* Get Object ID */
SELECT @table_id = object_id(@full_table_name)
if ((isnull(charindex('%', @full_table_name),0) = 0) and
(isnull(charindex('[', @table_name),0) = 0) and
(isnull(charindex('[', @table_owner),0) = 0) and
(isnull(charindex('_', @full_table_name),0) = 0) and
@table_id <> 0)
begin
set @cur_column = CURSOR FORWARD_ONLY STATIC
FOR
/* this block is for the case where there is no pattern
matching required for the table name */
SELECT
convert(sysname,c.name)
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault = m.id
AND m.colid = 1
WHERE
o.id = @table_id
AND c.id = o.id
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
AND o.type <> 'P'
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0)
AND c.xusertype = t.xusertype
AND c.name like @column_name
end
else
begin
/* this block is for the case where there IS pattern
matching done on the table name */
if @table_owner is null /* If owner not supplied, match all */
select @table_owner = '%'

set @cur_column = CURSOR SCROLL --FORWARD_ONLY STATIC
FOR
SELECT
convert(sysname,c.name)
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault = m.id
AND m.colid = 1
WHERE
o.name like @table_name
AND user_name(o.uid) like @table_owner
AND o.id = c.id
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
AND o.type <> 'P'
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0)
AND c.xusertype = t.xusertype
AND c.name like @column_name
end

SELECT
@count = count(c.name)
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault = m.id
AND m.colid = 1
WHERE
o.name like @table_name
AND user_name(o.uid) like @table_owner
AND o.id = c.id
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
AND o.type <> 'P'
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0)
AND c.xusertype = t.xusertype
AND c.name like @column_name

OPEN @cur_column


GO


执行存储过程的方法段:
--设置光标cur_col，对表中每个字段进行跟踪操作
DECLARE @cur_col CURSOR
EXEC dt_column @tableName,@col_count output ,@cur_column = @cur_col output
FETCH @cur_col into @column --将初值赋给字段变量
然后你用循环控制就可以了

[愉快的登山者]

DECLARE abc CURSOR FOR
SELECT PackRate FROM GoodsSpec where commodityno ='0010000101' order by PackRate

OPEN abc

FETCH NEXT FROM abc into @aa
WHILE (@@FETCH_STATUS = 0)
FETCH NEXT FROM abc into @aa

CLOSE abc
DEALLOCATE abc
GO

[kaikaihe]

同意楼上

[5027527]

to tj_dns(愉快的登山者) :也许我的意思表达不够明确，是这样的：

我要将表GoodsSpec 的每条记录的字段PackRate （类型是varchar(255))的值接起来，如：第一条为“abc”，第二条是“DEF”...
这样，加起来时，就是：“abc”+ “DEF”+ ...
所以，我得要通过游标取得具体每一条的PackRate的值，然后再加起来。
问题就是如何通用游标取得每条记录的PackRate的值。谢谢。