有点难的问题 结贴率100%

[onebigboy]

在存储过程中，想把sql语句检索出来的记录存到一个临时表中，然后遍历这个临时表的记录，判断某一列的值是否符合某个条件，如果该列符合条件，返回该行。

问题：1怎么在存储过程中建立临时表
2如何遍历临时表的记录，并判断某列的值
3存储过程是否能返回表中的一行，譬如：Select * from product where id=2

谢谢！

[yujohny]

1、select * into #temp from 表 where ……
2、用游标
3、可以，甚至可以一个表

[txlicenhe]

1: Select * into #tmp from 表 where ...
2: 用游标
declare @a varchar(10),@b int
declare cur cursor for select a,b from #tmp
open cur
fetch next from cur into @a,@b
while @@fetch_status = 0
begin
if @a = ..
begin
...
end
fetch next from cur into @a,@b
end
close cur
deallocate cur
3:
可以，在存储过程的最后加上该语句即可
eg:
create procedure test
as
...
select * from product where id = 2

[onebigboy]

谢谢踏网无痕兄，能不能给个完整的小例子

[pengdali]

1、
select * into #temp from 表 where...
或：
create table #temp...

2、
declare cursor_insert cursor for select c from #cs
declare @i int
open cursor_insert
fetch cursor_insert into @i
while @@fetch_status=0
begin
print @i
fetch cursor_insert into @i
end
close cursor_insert
deallocate cursor_insert


3、create proc xx
as
Select * from product where id=2

[DigJim]

1怎么在存储过程中建立临时表
答：建立表变量
declare @tmpTable table (tID int identity(1,1),...)
第一列使用自增的列，如上！
insert into @tmpTable select * from 表 where ....

2如何遍历临时表的记录，并判断某列的值
答：计算出@tmpTable 总共有多少行，然后一行一行比较，如下
declare @totalRow int --定义总行数
declare @row int --定义第几行
select @totalRow=count(*) from @tmpTable --取总行数
set @row=1 --设置第一行
while(@row<=@totalRow)
begin
select * from @totalRow where tID=@row --取第几行
....（进行比较运算）
@row=@row+1 --下一行
end

3存储过程是否能返回表中的一行，譬如：Select * from product where id=2
答：select * from @tmpTable where tID=2 (可以用变量从上面计算得到)

[onebigboy]

谢谢楼上各位，还有些小问题，请兄弟们办忙。
-------------------------------------------------------------


--终端设备适配
--返回Tag说明：1:适配成功 0:中止适配 -1:适配失败
--如果适配成功，返回一条记录，其余情况返回NULL
CREATE PROCEDURE sp_DeviceAdapt

@DeviceModel NVARCHAR(100),
@FileOrder INT,
@ContentID INT,
@Tag INT OUT

AS

IF NOT EXISTS (select A.ObjectID from CPDeviceAdapt A,CPContent B where ObjectID=0 and A.ConTypeCode=B.ConTypeCode and B.ContentID=@ContentID)
BEGIN
SET @Tag=1
SELECT * INTO #temp
FROM CPContentAttachment INNER JOIN
CPContentFile ON
CPContentAttachment.ContentFileID = CPContentFile.ContentFileID INNER JOIN
CPContent ON CPContentFile.ContentID = CPContent.ContentID INNER JOIN
CPDeviceAdapt ON
CPContentAttachment.ObjectID = CPDeviceAdapt.ObjectID INNER JOIN
CPObject ON CPContentAttachment.ObjectID = CPObject.ObjectID
WHERE (CPDeviceAdapt.DeviceModel =@DeviceModel) AND (CPContent.ContentID = @ContentID) AND
(CPContentFile.ContentFileOrder = @FileOrder)
ORDER BY CPDeviceAdapt.AdaptOrder DESC

declare cursor_insert cursor for select ContentFileID,ConAttachURL from #temp
declare @id int,@url NVARCHAR(100)
open cursor_insert
fetch cursor_insert into @id,@url
while @@fetch_status=0
begin
if (@url is not null)
begin
SET @Tag=1
SELECT * FROM CPContentAttachment WHERE (ContentFileID =@id) -----------问题：1.这里@url如果非空，我想返回这条记录，是否这样写；2 应该有中止循环的语句，怎么写； 3.如果所有记录的@url都是空，我想SET @Tag=-1，并且RETURN NULL，这两行应该加在什么地方
end
fetch cursor_insert into @id,@url
end
close cursor_insert
deallocate cursor_insert

END
ELSE
SET @Tag=0
RETURN NULL

[pengdali]

SELECT * FROM CPContentAttachment WHERE (ContentFileID =@id)
-----------问题：
1.这里@url如果非空，我想返回这条记录，是否这样写；
SELECT * FROM CPContentAttachment WHERE ContentFileID =isnull(@id,ContentFileID)

2 应该有中止循环的语句，怎么写；
break

3.如果所有记录的@url都是空，我想SET @Tag=-1，并且RETURN NULL，这两行应该加在什么地你的代码看不懂你要做什么。

[onebigboy]

--终端设备适配
--返回Tag说明：1:适配成功 0:中止适配 -1:适配失败
CREATE PROCEDURE sp_DeviceAdapt

@DeviceModel NVARCHAR(100),
@FileOrder INT,
@ContentID INT,
@Tag INT OUT

AS

--终端设备在CPDevice表中不存在，适配失败
IF NOT EXISTS (select * from CPDeviceAdapt where DeviceModel=@DeviceModel)
BEGIN
SET @Tag=-1
RETURN NULL
END

--终端设备在CPDeviceAdapt表中针对某项ConType存在ObjectID=0的情况，中止适配
IF NOT EXISTS (select A.ObjectID from CPDeviceAdapt A,CPContent B where ObjectID=0 and A.ConTypeCode=B.ConTypeCode and B.ContentID=@ContentID)
BEGIN
SELECT * INTO #temp
FROM CPContentAttachment INNER JOIN
CPContentFile ON
CPContentAttachment.ContentFileID = CPContentFile.ContentFileID INNER JOIN
CPContent ON CPContentFile.ContentID = CPContent.ContentID INNER JOIN
CPDeviceAdapt ON
CPContentAttachment.ObjectID = CPDeviceAdapt.ObjectID INNER JOIN
CPObject ON CPContentAttachment.ObjectID = CPObject.ObjectID
WHERE (CPDeviceAdapt.DeviceModel =@DeviceModel) AND (CPContent.ContentID = @ContentID) AND
(CPContentFile.ContentFileOrder = @FileOrder)
ORDER BY CPDeviceAdapt.AdaptOrder DESC

declare cursor_insert cursor for select ContentFileID,ConAttachURL from #temp
declare @id int,@url NVARCHAR(100)
open cursor_insert
fetch cursor_insert into @id,@url
while @@fetch_status=0
begin
if (@url is not null)
begin
--适配成功
SET @Tag=1 SELECT * FROM CPContentAttachment WHERE (ContentFileID =@id)
BREAK
end
fetch cursor_insert into @id,@url
end
close cursor_insert
deallocate cursor_insert

SET @Tag=-1
RETURN NULL
END
ELSE
SET @Tag=0
RETURN NULL


----------------------------------------------
我想遍历#temp表中ConAttachURL字段的值，如果ConAttachURL不是NULL，返回#temp中的这一行，如果#temp表中所有记录的ConAttachURL字段都是空值，我想SET @Tag=-1,并且RETURN NULL，我已经在上面的代码中加上了这两行，还有break，是否正确？

给您添麻烦了，再次感谢

[pengdali]

CREATE PROCEDURE sp_DeviceAdapt

@DeviceModel NVARCHAR(100),
@FileOrder INT,
@ContentID INT,
@Tag INT OUT

AS

--终端设备在CPDevice表中不存在，适配失败
IF NOT EXISTS (select * from CPDeviceAdapt where DeviceModel=@DeviceModel)
BEGIN
SET @Tag=-1
RETURN NULL
END

--终端设备在CPDeviceAdapt表中针对某项ConType存在ObjectID=0的情况，中止适配
IF NOT EXISTS (select A.ObjectID from CPDeviceAdapt A,CPContent B where ObjectID=0 and A.ConTypeCode=B.ConTypeCode and B.ContentID=@ContentID)
BEGIN
SELECT *
FROM CPContentAttachment INNER JOIN
CPContentFile ON
CPContentAttachment.ContentFileID = CPContentFile.ContentFileID INNER JOIN
CPContent ON CPContentFile.ContentID = CPContent.ContentID INNER JOIN
CPDeviceAdapt ON
CPContentAttachment.ObjectID = CPDeviceAdapt.ObjectID INNER JOIN
CPObject ON CPContentAttachment.ObjectID = CPObject.ObjectID
WHERE (CPDeviceAdapt.DeviceModel =@DeviceModel) AND (CPContent.ContentID = @ContentID) AND
(CPContentFile.ContentFileOrder = @FileOrder)
and ConAttachURL is not null
ORDER BY CPDeviceAdapt.AdaptOrder DESC

if @@rowcount=0
begin

SET @Tag=-1
RETURN NULL
end
END
ELSE
SET @Tag=0
RETURN NULL

[onebigboy]

呵呵，原来不用临时表
谢谢大力兄！ --0:）