CODEWITHSUNDEEP
sql-serverstored-procedures
Alon Shmiel
Alon Shmiel

Reputation: 7121

Duplicate rows and return a table

I have an array of strings (Ids) in my C#.

I want to send it to a Procedure in sql-server and duplicate all the rows that their ids are found in the array (but let them new Id and name).

Then I want to insert: copied_id, new id, new name, and return it into the c#.

For example:

Ads_Table

adId   |    Name    |   Status    |  Date
------------------------------------------
 2g3   |    abc     |     3       |  03.10
 2g4   |    xyz     |     4       |  04.10
 2g5   |    123     |     4       |  03.10

assuming I have:

string[] adsIds = new string();
adsIds[0] = "2g3";
adsIds[1] = "2g5";

NewAdsDetails newAds = Duplicate_Ads(adsIds); // run the prosedure

the table will be:

Ads_Table

adId   |    Name    |   Status    |  Date
------------------------------------------
 2g3   |    abc     |     3       |  03.10
 2g4   |    xyz     |     4       |  04.10
 2g5   |    123     |     4       |  03.10
 gsd   |  abc_gsd   |     3       |  03.10 // this ad was copied
 hfs   |  123_hfs   |     4       |  03.10 // this ad was copied

The new Ids were generated by NEWID(), and the name was generated by: name of the copied adId + '_' + the newId of the new ad.

so the returned table will contains:

 copiedId, newAdId, newName
 --------------------------
 [2g3, gsd, abc_gsd;
  2g5, hfs, 123_hfs]

I tried to create the procedure:

CREATE PROCEDURE [dbo].[Duplicate_Ads] @adsIds table(adId varchar(20))
AS
BEGIN
    DECLARE @newAdsIds table(adId varchar(20))
    DECLARE @newAdId NVARCHAR(100);


    DECLARE @Ad_cursor CURSOR;  
    SET @Ad_cursor = CURSOR FOR
    SELECT * FROM Ads_Table where adid in @AdsIds

    OPEN @Ad_cursor;
    FETCH NEXT FROM @Ad_cursor INTO @newAdsIds ;

    WHILE @@FETCH_STATUS = 0   
        BEGIN
           BEGIN
             @newAdId = NEWID();
             INSERT INTO sensplit_ads values(@newAdId, Name + '_' + @newAdId, Status, date);
           END

           FETCH NEXT FROM @Ad_cursor INTO adId, @newAdId, Name + '_' + @newAdId;

       END

       CLOSE @Ad_cursor;
       DEALLOCATE @Ad_cursor;
       Return @newAdsIds;
END

so again, I want to write a procedure:

  1. Get an array of string.
  2. Duplicate all the ads that their Id are found in the given array, but generates new Id and new name for the new recored.
  3. Return a table of: copied_id, new Id, and new Name.

any help appreciated!!

Upvotes: 0

Views: 84

Answers (1)

Kevin Suchlicki
Kevin Suchlicki

Reputation: 3145

Like @FreshPrinceOfSO said, don't use cursors if you can at all avoid them. Something like the below should work (I can't test without actual data). I used your NEWID() idea to generate the new id's, but I think it's not a good idea... NEWID is for guids, not 3-character id's. There is no guarantee of uniqueness when taking only three characters from NEWID. You should be using integers (IDENTITY values) as unique IDs.

;WITH NewAds as (
    SELECT  a1.adId AS CopiedId
            , a1.Name AS CopiedName
            , RIGHT(CAST(NEWID() AS VARCHAR(36)), 3) AS newAdId
    FROM    Ads_Table a1
            JOIN @AdsIds a2 ON a1.adId = a2.adId
)
SELECT  CopiedId, newAdId, CopiedName + '_' + newAdId AS [newName]
FROM    NewAds;

Upvotes: 1

Related Questions