CREATE TABLE #t( id int identity(1,1), a INT, b VARCHAR(20), c DECIMAL(18,2) ) INSERT #t(a,b,c) SELECT 1,'x',1.11 UNION ALL SELECT 2,'y',2.22 UNION ALL SELECT 3,'x',3.3declare @a int,@b varchar(20),@c DECIMAL(18,2),@i int,@maxi int select @i=1,@maxi=MAX(id) from #t while @i<=@maxi begin select @a=a,@b=b,@c=c from #t where id=@i EXEC getProductList @a,@b,@c set @i=@i+1 end
DECLARE @A VARCHAR(10) DECLARE @B VARCHAR(10) DECLARE @C VARCHAR(10) DECLARE cur cursor FOR SELECT A,B,C FROM #t; OPEN cur FETCH NEXT FROM cur INTO @A,@B,@C WHILE @@FETCH_STATUS=0 BEGIN EXEC getProductList @a,@b,@c FETCH NEXT FROM cur INTO @A,@B,@C END CLOSE cur DEALLOCATE cur
CREATE TABLE #t( IDX int identity(1,1), a INT, b VARCHAR, c DECIMAL ) 循环按照IDX去取数据
to perfectaction[完美行动], #t是我处理后得到的临时表, 虽然由自增长的id,但这id很可能出现严重断号, 那while循环就出现很多次无用的循环拉..不知道大家有没有好的方法呢..
to wufeng4552【水族杰纶】,我的前提是不使用游标.如果我想使用游标,我就不用开这个帖子了..
--#t是我处理后得到的临时表 然後增加個自增列 怎麼會出現斷號呢? alter table #T add id int identity
DECLARE @A VARCHAR(10) DECLARE @B VARCHAR(10) DECLARE @C VARCHAR(10) DECLARE cur cursor FOR SELECT A,B,C FROM #t; OPEN cur FETCH NEXT FROM cur INTO @A,@B,@C WHILE @@FETCH_STATUS=0 BEGIN EXEC getProductList @a,@b,@c FETCH NEXT FROM cur INTO @A,@B,@C END CLOSE cur DEALLOCATE cur 希望对你有帮助,游标也可以,但是SQL的游标的处理并不是太好
--不知道能不能解决 DECLARE @sql VARCHAR(3000),@i INT,@Max INT SELECT @Max = ISNULL(MAX(a),0),@i = 0 FROM #t WHILE @i <= @Max BEGIN SELECT @sql = ISNULL(@sql,'') + 'EXEC getProductList '+ CAST(a AS VARCHAR) + ','''+ b +''','+CAST(c AS VARCHAR) +';' FROM #t WHERE a >= @i and a < @i + 100 SET @i = CASE WHEN @i + 100 <= @Max THEN @i + 100 ELSE @Max + 1 END EXEC(@sql) SET @sql = '' END
你的存储过程为什么要用参数?不就是因为里面有些语句会用到嘛?那你能不能在SP里面才去定义这几个参数,在SP里面复制;而SP本身不带参数。下面A、B两种结果不是一样的嘛 A. declare @a int, @b int select @a=0, @b=1 exec sp_test @a, @bB. create store procedure sp_test begin declare @a int, @b int select @a=0, @b=1 …… endexec sp_test
id int identity(1,1),
a INT,
b VARCHAR(20),
c DECIMAL(18,2)
)
INSERT #t(a,b,c)
SELECT 1,'x',1.11
UNION ALL
SELECT 2,'y',2.22
UNION ALL
SELECT 3,'x',3.3declare @a int,@b varchar(20),@c DECIMAL(18,2),@i int,@maxi int
select @i=1,@maxi=MAX(id) from #t
while @i<=@maxi
begin
select @a=a,@b=b,@c=c from #t where id=@i
EXEC getProductList @a,@b,@c
set @i=@i+1
end
DECLARE @B VARCHAR(10)
DECLARE @C VARCHAR(10)
DECLARE cur cursor FOR
SELECT A,B,C FROM #t;
OPEN cur
FETCH NEXT FROM cur INTO @A,@B,@C
WHILE @@FETCH_STATUS=0
BEGIN
EXEC getProductList @a,@b,@c
FETCH NEXT FROM cur INTO @A,@B,@C
END
CLOSE cur
DEALLOCATE cur
a INT,
b VARCHAR,
c DECIMAL
) 循环按照IDX去取数据
alter table #T
add id int identity
DECLARE @B VARCHAR(10)
DECLARE @C VARCHAR(10)
DECLARE cur cursor FOR
SELECT A,B,C FROM #t;
OPEN cur
FETCH NEXT FROM cur INTO @A,@B,@C
WHILE @@FETCH_STATUS=0
BEGIN
EXEC getProductList @a,@b,@c
FETCH NEXT FROM cur INTO @A,@B,@C
END
CLOSE cur
DEALLOCATE cur
希望对你有帮助,游标也可以,但是SQL的游标的处理并不是太好
--不知道能不能解决
DECLARE @sql VARCHAR(3000),@i INT,@Max INT
SELECT @Max = ISNULL(MAX(a),0),@i = 0 FROM #t
WHILE @i <= @Max
BEGIN
SELECT @sql = ISNULL(@sql,'') + 'EXEC getProductList '+ CAST(a AS VARCHAR) + ','''+ b +''','+CAST(c AS VARCHAR) +';'
FROM #t WHERE a >= @i and a < @i + 100
SET @i = CASE WHEN @i + 100 <= @Max THEN @i + 100 ELSE @Max + 1 END
EXEC(@sql)
SET @sql = ''
END
A.
declare @a int, @b int
select @a=0, @b=1
exec sp_test @a, @bB.
create store procedure sp_test
begin
declare @a int, @b int
select @a=0, @b=1
……
endexec sp_test