＋设为首页 ＋添加到收藏夹
ALTER Procedure prGetInventoryProperties_Cursor
(
@intInventoryId int,
@chvProperties varchar(8000) OUTPUT,
@debug int = 0
)
As
declare @intCountProperties int,
@intCounter int,
@chvProperty varchar(50),
@chvValue varchar(50),
@chvUnit varchar(50),
@insLenProperty smallint,
@insLenValue smallint,
@insLenUnit smallint,
@insLenProperties smallint
Set @chvProperties =
DECLARE @CrsrVar CURSOR
SET @CrsrVar= CURSOR FOR
select Property, Value, Unit
from InventoryProperty inner join Property
on InventoryProperty.PropertyId = Property.PropertyId
where InventoryProperty.InventoryId = @intInventoryId
OPEN @CrsrVar
FETCH NEXT FROM @CrsrVar
INTO @chvProperty, @chvValue, @chvUnit
WHILE (@@FETCH_STATUS = 0)
BEGIN
set @chvUnit = Coalesce(@chvUnit, )
if @debug <> 0
select @chvProperty Property,
@chvValue [Value],
@chvUnit [Unit]
select @insLenProperty = DATALENGTH(@chvProperty),
@insLenValue = DATALENGTH(@chvValue),
@insLenUnit = DATALENGTH(@chvUnit),
@insLenProperties = DATALENGTH(@chvProperties)
if @insLenProperties + 2 + @insLenProperty + 1 + @insLenValue + 1 + @insLenUnit > 8000
begin
select List of properties is too long (over 8000 characters)!
return 1
end
set @chvProperties = @chvProperties + @chvProperty + = + @chvValue + + @chvUnit + ;
if @debug <> 0
select @chvProperties chvProperties
FETCH NEXT FROM @CrsrVar
INTO @chvProperty, @chvValue, @chvUnit
END
CLOSE @CrsrVar
DEALLOCATE @CrsrVar
return 0
@debug是输入参数，不为0表示调用者希望调试此存储过程，这时会select出两个结果集给调用者
if @debug <> 0
select @chvProperty Property,
@chvValue [Value],
@chvUnit [Unit]
if @debug <> 0
select @chvProperties chvProperties
那就要看使用者是否想看@开头的内部变量的值咯
这个@debug开关是用于调试该存储过程用的。默认的情况下是@debug=0，这样的话，就不会显示出内部变量的值了；非0的话，就显示。
