Reputation: 122
Hi I have a procedure in MS SQL Server 2012, in which I am performing some insertions and updating but in some piece of that procedure, I am using cursor for looping in insertion.
Can some one please help me doing that looping insertion without using cursor?
Complete code is below
(
@TerritoryName varchar(200),
@TerritoryDescription varchar(2000),
@TerritoryID int,
@CountyIDs varchar(500),
@ZipCodeIDs varchar(500)
)
AS
BEGIN
Declare @countyid int,@zipcodeid int,@Target_GeographyTypeID int;
DECLARE @t TABLE(idx int primary key identity,id int)
DECLARE @tzip TABLE(idx int primary key identity,id int)
INSERT INTO @t
SELECT item from fn_split(@CountyIDs,',')
INSERT INTO @tzip
SELECT item from fn_split(@ZipCodeIDs,',')
IF(@TerritoryID < 1)
BEGIN
INSERT INTO Territories(TerritoryName,TerritoryDescription,Active,CreatedDate)
VALUES(@TerritoryName,@TerritoryDescription,1,Getdate())
SET @TerritoryID= @@identity
END
ELSE
BEGIN
UPDATE Territories
SET TerritoryName=@TerritoryName,
TerritoryDescription=@TerritoryDescription
WHERE TerritoryID=@TerritoryID
END
SELECT @Target_GeographyTypeID=GeographyTypeId from GeoGraphyTypes
WHERE GeographyTypeName='County'
DELETE from Territory_mapping
WHERE TerritoryID=@TerritoryID
DECLARE db_cursor CURSOR FOR
SELECT id
FROM @t
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @countyid
WHILE @@FETCH_STATUS = 0
BEGIN
if(@countyid > 0)
BEGIN
INSERT INTO Territory_mapping(TerritoryID,Target_GeographyTypeID,Target_GeographyID,CreatedDate)
VALUES(@TerritoryID,@Target_GeographyTypeID,@countyid,GETDATE())
END
FETCH NEXT FROM db_cursor INTO @countyid
END
CLOSE db_cursor
DEALLOCATE db_cursor
-------------------- Zip Code -----------------------
SELECT @Target_GeographyTypeID=GeographyTypeId from GeoGraphyTypes
WHERE GeographyTypeName='Zipcode'
DECLARE db_cursor CURSOR FOR
SELECT id
FROM @tzip
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @zipcodeid
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@zipcodeid > 0)
BEGIN
INSERT INTO Territory_mapping(TerritoryID,Target_GeographyTypeID,Target_GeographyID,CreatedDate)
VALUES(@TerritoryID,@Target_GeographyTypeID,@zipcodeid,GETDATE())
END
FETCH NEXT FROM db_cursor INTO @zipcodeid
END
CLOSE db_cursor
DEALLOCATE db_cursor
RETURN @TerritoryID
END
Upvotes: 1
Views: 75
Reputation: 4155
Have you tried this:
INSERT INTO Territory_mapping
(TerritoryID,Target_GeographyTypeID,Target_GeographyID,CreatedDate)
Select @TerritoryID, @Target_Geography, id, GETDATE()
From @t
Where id > 0
Note this is purely a rework of your code based on what you provided. I have no idea where some of those variables are coming from.
Also, you say that countryid is a comma separated list of values, but that's not how your code is working with it. My sample assumes (are yours does) that @countryid (and by extension, id) is a single value. If this isn't the case, please update your code to show how you are dealing with the comma separated list.
Upvotes: 1