Insertion without using cursor

Question

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

Answer 1

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.