游标n SQL server 2008 [英] Cursor n SQL server 2008

问题描述

我使用以下SP来添加或删除客户的产品。

Hi,

I have used the below SP to add or remove products from customer.

ALTER PROCEDURE [dbo].[Add_remove_customer_product](@prod_id int,@cust_id varchar(30),@isValid int,@result int output)
AS
BEGIN
declare @p_id int
declare @alerttype varchar(40)
declare @alertid int
DECLARE @UpdateStatus Int

if not exists (select * from custprodmap where (prodId=@prod_id))
begin
insert into custprodmap (custProdId,custId,prodId,mappedOn,mappedBy,isValid,removedOn,removedBy) values (cast(@cust_id as varchar)+cast(@prod_id as varchar),@cust_id,@prod_id,GETDATE(),'customer',@isValid,GETDATE(),'customer') 

DECLARE alerts CURSOR LOCAL FAST_FORWARD
    FOR select alertId from alertprodmap where prodId=@prod_id
	
    OPEN alerts  
    FETCH NEXT FROM alerts  
    INTO @alertId  
	
	SET @UpdateStatus = @@FETCH_STATUS
	
    WHILE @UpdateStatus = 0 
    BEGIN  
    if @alertId <> null
    begin
		
		set @alerttype=(SELECT alertType from [alerts] where alertId = @alertid)
		
        insert into analysis select @cust_id,@alertid,@alerttype,'open','open','USER',getdate(),null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,getDate(),null,null,null,null,null,null,null where not exists(select alertId from mssp_se_analysis where alertId = @alertid)
        print 'inserted'
        
        FETCH NEXT FROM alerts INTO @alertId  
        SET @UpdateStatus = @@FETCH_STATUS
    end
    End  
    CLOSE alerts  
    DEALLOCATE alerts


set @result=1
end

else
begin

set @p_id=(select isValid from custprodmap where prodId=@prod_id)

if (@isValid=1 and @p_id=1)
set @result=2

if (@isValid=1 and @p_id=0)
begin
update mssp_se_custprodmap set isValid=@isValid where (custId=@cust_id and prodId=@prod_id)

DECLARE alerts CURSOR LOCAL FAST_FORWARD
    FOR (select [alertId] from  [alertprodmap] where prodId=@prod_id)

    OPEN subject_cursor  

    FETCH NEXT FROM alerts  
    INTO @alertId  

    WHILE @@FETCH_STATUS = 0  
    BEGIN  
    if @alertId <> null
    begin
    
    set @alerttype=(SELECT alertType from [alerts] where alertId = @alertid)
        insert into analysis select @cust_id,@alertid,@alerttype,'open','open','USER',getdate(),null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,getDate(),null,null,null,null,null,null,null where not exists(select alertId from mssp_se_analysis where alertId = @alertid)
        
        FETCH NEXT FROM alerts  
        INTO @alertId  
        end
    End  
    CLOSE alerts;  
    DEALLOCATE alerts;


set @result=1
end

if (@isValid=0 and @p_id=1)
begin
update custprodmap set isValid=@isValid where (custId=@cust_id and prodId=@prod_id)
set @alertid=(select [alertId] from  [alertprodmap] where prodId=@prod_id)
set @alerttype=(SELECT alertType from [alerts] where alertId = @alertid)
delete from analysis where custId=@cust_id and alertId=@alertid and alertType=@alerttype

set @result=0
end
end
return @result

end

我执行了查询

I have executed the query as

exec Add_remove_customer_product 16,88,1,0

当我执行此查询时，它执行的时间更长，而不是将值插入分析表。当我停止执行时，它会显示'1行插入'[在custprodmap表中]。



请帮助我。

提前致谢

When I execute this query, it is executing for more time and not inserting values into analysis table. When I stop execution it shows '1 row(s) inserted' [in custprodmap table].

Please help me.
Thanks in advance

推荐答案

尝试按下面给出的例子声明光标 -



DECLARE CurSelect CURSOR STATIC READ_ONLY FOR



从员工中选择emp id

Try declaring the cursor as in example given below -

DECLARE CurSelect CURSOR STATIC READ_ONLY FOR

select emp id from employee

这篇关于游标n SQL server 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！