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
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