Insert n rows stored procedure

Question

Trying to insert multiple rows based on a passed parameter.

Tried this and it only repeated once:

ALTER PROCEDURE [dbo].[ActivateCertificates]

    @Count              int,
    @CertificateNumber  int,
    @Buyer              VarChar(50)


AS
Declare @x int
Declare @InitialCharacter   Char(1)
SET @InitialCharacter='C'
Declare @Last3      Char(3)
SET @Last3='867'

    while @x <= @Count 
begin
    /* CREATE THE CERTIFICATE NUMBER */
    set @CertificateNumber = @CertificateNumber +1
    /* insert into certificates  cert number and who sold to. */
    INSERT into Certificates (CertificateNumber,Buyer) 
    VALUES (@InitialCharacter + ltrim(rtrim(cast(@CertificateNumber as char))) + @Last3, @Buyer) 
end 
set @x =@x + 1


GO

Initialize @x to 0; increment it inside the begin..end block. — Tim, Jun 14 '13 at 22:04
Don't write loops in TSQL. Write a set based query to do that instead. — JohnFx, Jun 14 '13 at 22:30
Thanks, Got that working, how would i return an error message if one of the inserts tried to insert a cert number that already existed as this is a unique value in the DB — Doug Farrell, Jun 14 '13 at 22:41
Ok, I will start using set based query, can you show me the differance?? — Doug Farrell, Jun 14 '13 at 22:48
Of greater concern to me than your use of loops is your CertificateNumber, which seems to be one of those legacy critters that have embedded meaning. Does that three-digit suffix have significance? And why are you trimming @CertificateNumber? It is passed an integer. When you cast it to char it shouldn't accrete any spaces. — Tim, Jun 15 '13 at 12:55
`how would i return an error message if one of the inserts tried to insert a cert number that already existed as this is a unique value in the DB` Is there a unique index on CertificateNumber column in the table? — Tim, Jun 15 '13 at 12:58
three-digit suffix static for first million records @CertificateNumber passed as integer so it can increment by 1 — Doug Farrell, Jun 15 '13 at 17:56
Static initial character + @CertificateNumber + three-digit suffix inserted into dB Character field. — Doug Farrell, Jun 15 '13 at 17:58

score 1 · Answer 1 · answered Jun 14 '13 at 22:26

1

Your variable is incremented outside the BEGIN/END block. Also I would recommend you initially set your variable to 0 to avoid any potential garbage data stored in that memory location.

answered Jun 14 '13 at 22:26

logixologist

3,694
4
28
46

Got that figured out, couldn't answer my own Q as this is my first day here. What do you think about the above comment to use a set based query instead? Can anyone show me one vs the one i wrote above? – Doug Farrell Jun 14 '13 at 22:51

score 0 · Accepted Answer · edited May 23 '17 at 12:05

To achieve your goal using a set-based query instead of a loop (BTW there are lots of such examples here on StackOverflow) you'll have to have a tally (numbers) table or create it on a fly with a subquery or recursive CTE.

CREATE TABLE tally (id INT NOT NULL PRIMARY KEY);

To populate it up to 100000 (Celko-style)

INSERT INTO tally (id) 
SELECT a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 + 1 as N
  FROM (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) b
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) c
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d
      , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) e
ORDER BY N;

Now your stored procedure boils down to one statement

CREATE PROCEDURE ActivateCertificates
  @Count              INT,
  @CertificateNumber  INT,
  @Buyer              VARCHAR(50)
AS
INSERT INTO Certificates (CertificateNumber, Buyer) 
SELECT 'C' + CAST(q.number + t.id AS VARCHAR(12)) + '867', q.buyer 
  FROM
( 
  SELECT @CertificateNumber number, @Buyer buyer
) q, tally t
WHERE t.id <= @Count;

Here is SQLFiddle demo

Now if you generate relatively small amounts of certificates (< 32768) then you can use recursive CTE to build a sequence of numbers (and don't need a persisted tally table)

CREATE PROCEDURE ActivateCertificates
  @Count              INT,
  @CertificateNumber  INT,
  @Buyer              VARCHAR(50)
AS
WITH tally AS (
  SELECT 1 id
  UNION ALL
  SELECT id + 1 FROM tally WHERE id < @Count
)
INSERT INTO Certificates (CertificateNumber, Buyer) 
SELECT 'C' + CAST(q.number + t.id AS VARCHAR(12)) + '867', q.buyer 
  FROM
( 
  SELECT @CertificateNumber number, @Buyer buyer
) q, tally t OPTION (MAXRECURSION 32767);

Here is SQLFiddle demo for that case

Excellent alternative but I still have on burning question.....I got it to work the way I wanted using a variation of my original idea, what are the advantages of your way? — Doug Farrell, Jun 18 '13 at 16:54
@DougFarrell Good for you. RDBMSes are optimized to work with sets so thats more efficient. You can take a look at some comparison (loops vs. sets) here http://stackoverflow.com/a/17139749/1920232. — peterm, Jun 18 '13 at 17:14
Thank you for the answer, it I will definitely use this method next time. — Doug Farrell, Jun 18 '13 at 19:49

Insert n rows stored procedure

2 Answers2