自定义函数CalculateProductNeedMaterial2有2个参数@code varchar(25),@kvol decimal(28,2)
返回一个表,我现在有一个表,里面有2000多条记录,2个字段,都是varchar(25)和decimal(28,2)型的。
我要用CalculateProductNeedMaterial2函数取每条记录的2个字段,将返回的表合并作为最后的输出结果,这个不用游标能否实现?
返回一个表,我现在有一个表,里面有2000多条记录,2个字段,都是varchar(25)和decimal(28,2)型的。
我要用CalculateProductNeedMaterial2函数取每条记录的2个字段,将返回的表合并作为最后的输出结果,这个不用游标能否实现?
set @str=''
select @str=@str+','+col1+col2 from CalculateProductNeedMaterial2
返回表设为table1现有table2,有2000条记录,为了举例,我假设只有5条记录:
--table2:
code kvol
AB00001 1000
BB00002 2000
CB00003 3000
DB00004 4000
EB00005 5000每个table2的记录的2个字段代入 CalculateProductNeedMaterial2会得到一个表,其表结构和table1一样。我如何将这些表里的记录最后合到一个“总表”里?
要实现此功能有困难
select identity(int,1,1) as id ,code,kvol into #temp from table2
declare @count int,@i int
declare @code varchar(25),@kvol decimal(28,2)
select @count=max(id) from #temp
set @i = 1
set @code=''
set @kvol=0
while @i <= @count
begin
select @code=code,@kvol=kvol from #temp where id = @i
insert into table1 select CalculateProductNeedMaterial2(@code,@kvol)
enddrop table #temp
declare @count int,@i int
declare @code varchar(25),@kvol decimal(28,2)
select @count=max(id) from #temp
set @i = 1
set @code=''
set @kvol=0
while @i <= @count
begin
select @code=code,@kvol=kvol from #temp where id = @i
insert into table1 select CalculateProductNeedMaterial2(@code,@kvol)
set @i = @i + 1
enddrop table #temp没有测试,可能有一些小的问题,但思路应该是正确
不需要用游标作循环的例子:SET NOCOUNT ON
DECLARE @iNextRowId int,@iCurrentRowId int,@iLoopControl int,@ID int
-- Initialize variables!
SELECT @iLoopControl = 1
SELECT @iNextRowId = MIN(id) FROM tblA
-- Make sure the table has data.
IF ISNULL(@iNextRowId,0) = 0
BEGIN
SELECT 'No data in found in table!'
RETURN
END
-- Retrieve the first row
SELECT @iCurrentRowId = id,@id = ID FROM tblA WHERE id = @iNextRowId
-- start the main processing loop.
WHILE @iLoopControl = 1
BEGIN
-- This is where you perform your detailed row-by-row
-- processing.
-- Reset looping variables.
SELECT 'A'
SELECT @iNextRowId = NULL
-- get the next iRowId
SELECT @iNextRowId = MIN(id)FROM tblA WHERE ID > @iCurrentRowId
-- did we get a valid next row id?
IF ISNULL(@iNextRowId,0) = 0
BEGIN
BREAK
END
-- get the next row.
SELECT @iCurrentRowId = ID,@ID = ID FROM tblA WHERE ID = @iNextRowId
END
RETURN
ST NOCOUNT OFF