问题:1怎么在存储过程中建立临时表
2如何遍历临时表的记录,并判断某列的值
3存储过程是否能返回表中的一行,譬如:Select * from product where id=2
谢谢!
...全文
3010打赏收藏
有点难的问题 结贴率100%
在存储过程中,想把sql语句检索出来的记录存到一个临时表中,然后遍历这个临时表的记录,判断某一列的值是否符合某个条件,如果该列符合条件,返回该行。 问题:1怎么在存储过程中建立临时表 2如何遍历临时表的记录,并判断某列的值 3存储过程是否能返回表中的一行,譬如:Select * from product where id=2 谢谢!
@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
@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
SELECT * FROM CPContentAttachment WHERE (ContentFileID =@id)
-----------问题:
1.这里@url如果非空,我想返回这条记录,是否这样写;
SELECT * FROM CPContentAttachment WHERE ContentFileID =isnull(@id,ContentFileID)
@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
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 (可以用变量从上面计算得到)
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
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