你可以先将Fld_ID保存到临时表,如下: CREATE proc sp_Service_UseCard2 ( @UserName nvarchar(50), @CardID int, @CardNum int, @ItemID nvarchar(1000) ) As --Begin Declare @MaxExpireDate datetime,@ID int if(@ItemID='') Set @ItemID='Select Top '+Convert(nvarchar(50),@CardNum)+' Fld_ID into #Temp From Tb_Service_CardItems Where Fld_OwnerUser='''+@UserName+''' And Fld_CardID='+Convert(nvarchar(50),@CardID)+' And Fld_IsUse=0 Order By Fld_ID Asc'
exec(@ItemID) Declare myCursor Cursor for Select Fld_ID From Tb_Service_CardItems Where Convert(nvarchar(50),Fld_ID) in (select Fld_ID from #Temp) Open myCursor Fetch Next From myCursor Into @ID Print @ID While(@@Fetch_Status=0) Begin Select @MaxExpireDate=case when max(Fld_ExpireDate) is null then getdate() When Datediff(minute,max(Fld_ExpireDate),getdate())>=0 then getdate() else max(Fld_ExpireDate) end From Tb_Service_CardItems Where Fld_OwnerUser=@UserName and Fld_CardID=@CardID Update Tb_Service_CardItems Set Fld_UseDate=getdate(),Fld_EffectDate=@MaxExpireDate where Fld_ID=@ID Fetch Next From myCursor Into @ID End Close myCursor Deallocate myCursor --End GO
To lljianmao(ghost): 是个不错的IDEA!不过在执行到Declare myCursor Cursor for Select Fld_ID From Tb_Service_CardItems Where Convert(nvarchar(50),Fld_ID) in (select Fld_ID from #Temp) 时报错:Invalid object name '#Temp'.如何解决?!
原因很简单 Declare myCursor Cursor for Select Fld_ID From Tb_Service_CardItems Where Convert(nvarchar(50),Fld_ID) in (@ItemID)里的'in (@ItemID)'语句并不会自动执行,而是转换成了字符串。 比如:@tempID='select top 10 ...'那么,in (@ItemID)就会变成in('select top 10 ...'),而不是你想象的in('1','2','3'...')那样,自然取不到结果了
办法1.使用SET ROWCOUNT来进行选择结果限制.这样,你在 Declare myCursor Cursor for Select Fld_ID From Tb_Service_CardItems Where Convert(nvarchar(50),Fld_ID) in (@ItemID) 这里可以直接使用原始的语句. 如果一个 SELECT 语句既包含 TOP 又包含 ORDER BY 子句,那么返回的行将会从排序后的结果集中选择。整个结果集按照指定的顺序建立并且返回排好序的结果集的前 n 行。限制结果集大小的另一种方法是在执行一个语句之前执行 SET ROWCOUNT n 语句。SET ROWCOUNT 与 TOP 的不同之处在于: SET ROWCOUNT 限制适用对 ORDER BY 取值后在结果集中生成行。如果指定了 ORDER BY,SELECT 语句将在从某个已根据指定的 ORDER BY 分类进行了排序的值集中选择 n 行后终止。 TOP 子句适用于指定了该子句的单个 SELECT 语句。在执行另一个 SET ROWCOUNT 语句之前,SET ROWCOUNT 会一直有效,例如执行 SET ROWCOUNT 0 将会关闭此选项。 2.办法2,游标通过exec语句打开3.办法3.使用全局临时表,注意用了trop,表名最好特殊点
有误,是trop-->drop游标通过exec语句打开的例子: ALTER Procedure GetSystemLog ( @position int,--check: >0 @pageSize int =20, @condition nvarchar(200)='' ) ASDeclare @maxTop int Set @maxTop=@position+@pageSize - 1Declare @cursorSql nvarchar(500) Set @cursorSql='Declare cs scroll Cursor for ' +'Select Top ' +cast( @maxTop as nvarchar(8)) +' * From SystemLog' if Ltrim(@condition)<>'' And @condition is not null Set @cursorSql=@cursorSql+' Where '+@conditionPrint @cursorSqlDeclare @LogId int Declare @HappenTime datetime Declare @UserName nvarchar(50) Declare @Category nvarchar(50) Declare @ClientIP nvarchar(50) Declare @Message nvarchar(500)Declare @tmp Table ( LogId int, HappenTime datetime, UserName nvarchar(50), Category nvarchar(50), ClientIP nvarchar(50), Message nvarchar(500)
)Exec(@cursorSql)Open cs--print '@positon='+cast(@position as nvarchar(50)) --print '@@Cursor_rows='+cast(@@Cursor_rows as nvarchar(50))if @@Cursor_rows<@position Begin close cs deallocate cs return -1 EndFetch ABSOLUTE @position From cs Into @LogId,@Category,@UserName,@HappenTime,@ClientIP,@Message--if @@FETCH_STATUS =0 Insert into @tmp values(@LogId,@HappenTime,@UserName,@Category,@ClientIP,@Message)Declare @i int Set @i=1while @i<@pageSize --and @@FETCH_STATUS =0 begin fetch next from cs Into @LogId,@Category,@UserName,@HappenTime,@ClientIP,@Message if @@Fetch_Status=0 Begin Insert into @tmp values(@LogId,@HappenTime,@UserName,@Category,@ClientIP,@Message) Set @i=@i+1 End Else break Endclose cs deallocate csSelect * from @tmpreturn 0
自己创建临时表不就行了if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#Temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[#Temp] create table #Temp(Fld_ID nvarchar(50))
CREATE proc sp_Service_UseCard2
(
@UserName nvarchar(50),
@CardID int,
@CardNum int,
@ItemID nvarchar(1000)
)
As
--Begin
Declare @MaxExpireDate datetime,@ID int
if(@ItemID='')
Set @ItemID='Select Top '+Convert(nvarchar(50),@CardNum)+' Fld_ID into #Temp From Tb_Service_CardItems Where Fld_OwnerUser='''+@UserName+''' And Fld_CardID='+Convert(nvarchar(50),@CardID)+' And Fld_IsUse=0 Order By Fld_ID Asc'
exec(@ItemID) Declare myCursor Cursor for Select Fld_ID From Tb_Service_CardItems Where Convert(nvarchar(50),Fld_ID) in (select Fld_ID from #Temp)
Open myCursor
Fetch Next From myCursor Into @ID
Print @ID
While(@@Fetch_Status=0)
Begin
Select @MaxExpireDate=case when max(Fld_ExpireDate) is null then getdate() When Datediff(minute,max(Fld_ExpireDate),getdate())>=0 then getdate() else max(Fld_ExpireDate) end
From Tb_Service_CardItems Where Fld_OwnerUser=@UserName and Fld_CardID=@CardID
Update Tb_Service_CardItems Set Fld_UseDate=getdate(),Fld_EffectDate=@MaxExpireDate where Fld_ID=@ID
Fetch Next From myCursor Into @ID
End
Close myCursor
Deallocate myCursor
--End
GO
是个不错的IDEA!不过在执行到Declare myCursor Cursor for Select Fld_ID From Tb_Service_CardItems Where Convert(nvarchar(50),Fld_ID) in (select Fld_ID from #Temp)
时报错:Invalid object name '#Temp'.如何解决?!
Declare myCursor Cursor for Select Fld_ID From Tb_Service_CardItems Where Convert(nvarchar(50),Fld_ID) in (@ItemID)里的'in (@ItemID)'语句并不会自动执行,而是转换成了字符串。
比如:@tempID='select top 10 ...'那么,in (@ItemID)就会变成in('select top 10 ...'),而不是你想象的in('1','2','3'...')那样,自然取不到结果了
Declare myCursor Cursor for Select Fld_ID From Tb_Service_CardItems Where Convert(nvarchar(50),Fld_ID) in (@ItemID)
这里可以直接使用原始的语句.
如果一个 SELECT 语句既包含 TOP 又包含 ORDER BY 子句,那么返回的行将会从排序后的结果集中选择。整个结果集按照指定的顺序建立并且返回排好序的结果集的前 n 行。限制结果集大小的另一种方法是在执行一个语句之前执行 SET ROWCOUNT n 语句。SET ROWCOUNT 与 TOP 的不同之处在于: SET ROWCOUNT 限制适用对 ORDER BY 取值后在结果集中生成行。如果指定了 ORDER BY,SELECT 语句将在从某个已根据指定的 ORDER BY 分类进行了排序的值集中选择 n 行后终止。
TOP 子句适用于指定了该子句的单个 SELECT 语句。在执行另一个 SET ROWCOUNT 语句之前,SET ROWCOUNT 会一直有效,例如执行 SET ROWCOUNT 0 将会关闭此选项。
2.办法2,游标通过exec语句打开3.办法3.使用全局临时表,注意用了trop,表名最好特殊点
ALTER Procedure GetSystemLog
(
@position int,--check: >0
@pageSize int =20,
@condition nvarchar(200)=''
)
ASDeclare @maxTop int
Set @maxTop=@position+@pageSize - 1Declare @cursorSql nvarchar(500)
Set @cursorSql='Declare cs scroll Cursor for '
+'Select Top '
+cast( @maxTop as nvarchar(8))
+' * From SystemLog'
if Ltrim(@condition)<>'' And @condition is not null
Set @cursorSql=@cursorSql+' Where '+@conditionPrint @cursorSqlDeclare @LogId int
Declare @HappenTime datetime
Declare @UserName nvarchar(50)
Declare @Category nvarchar(50)
Declare @ClientIP nvarchar(50)
Declare @Message nvarchar(500)Declare @tmp Table
( LogId int,
HappenTime datetime,
UserName nvarchar(50),
Category nvarchar(50),
ClientIP nvarchar(50),
Message nvarchar(500)
)Exec(@cursorSql)Open cs--print '@positon='+cast(@position as nvarchar(50))
--print '@@Cursor_rows='+cast(@@Cursor_rows as nvarchar(50))if @@Cursor_rows<@position
Begin
close cs
deallocate cs
return -1
EndFetch ABSOLUTE @position From cs
Into @LogId,@Category,@UserName,@HappenTime,@ClientIP,@Message--if @@FETCH_STATUS =0
Insert into @tmp values(@LogId,@HappenTime,@UserName,@Category,@ClientIP,@Message)Declare @i int
Set @i=1while @i<@pageSize --and @@FETCH_STATUS =0
begin
fetch next from cs
Into @LogId,@Category,@UserName,@HappenTime,@ClientIP,@Message
if @@Fetch_Status=0
Begin
Insert into @tmp values(@LogId,@HappenTime,@UserName,@Category,@ClientIP,@Message)
Set @i=@i+1
End
Else
break
Endclose cs
deallocate csSelect * from @tmpreturn 0
drop table [dbo].[#Temp]
create table #Temp(Fld_ID nvarchar(50))
如果@ItemID是传具体的ID过来,就不由系统分配ID.
此时如果传的是单个的ID值就没问题,如果是多个就有问题了.
如zhangyp(苦舟) 所说,即@ItemID='120,121,122',IN(@ItemID)就成了IN('120,121,122'),取值不到了.
前面的问题是采用了临时表解决的,所以我想能不能将@ItemID='120,121,122'这3个ID作为3条记录也插入临时表,这样做感觉要通用些!
不知道能实现否?或是还有更好的办法?望各位不吝赐教!