CODEWITHSUNDEEP
sqlsql-servert-sql
GibboK
GibboK

Reputation: 73908

How to use WHILE inside a Stored Procedure?

I have create this script that allow to insert some dump data in a table. When I execute this SPROC and inserting @N the SPROC run for infinity.

Any idea what I am doing wrong.

CREATE PROCEDURE CreateSampleData
    @N INT 
AS 
    DECLARE @row INT 
    SET @row = 1 

    DECLARE @randomGuid VARCHAR(36) 
    DECLARE @randomText VARCHAR 

    WHILE @row <= @N
      BEGIN 
      -- Set Random Values 
      SET @randomText = (SELECT LEFT(Cast(Newid() AS VARCHAR(36)), 20)) 
      SET @randomGuid = Newid()

      INSERT INTO [XXX].[dbo].[YYY] 
                  ([Id],
                   [eventid], 
                   [eventtitle], 
                   [day number], 
                   [day], 
                   [datetimestart], 
                   [datetimeend], 
                   [location], 
                   [staff], 
                   [uniquestudentreference], 
                   [reminder]) 
      VALUES      ( @randomGuid,
                    @randomText, 
                    @randomText, 
                    '2', 
                    'Monday', 
                    '08:50:00', 
                    '10:50:00', 
                    @randomText, 
                    @randomText, 
                    'Silvia', 
                    'n' ) 
  END 
GO

Upvotes: 0

Views: 72

Answers (3)

gbn
gbn

Reputation: 432210

Why loop? Do it as a set in one go

  INSERT INTO [XXX].[dbo].[YYY] 
              ([Id],
               [eventid], 
               [eventtitle], 
               [day number], 
               [day], 
               [datetimestart], 
               [datetimeend], 
               [location], 
               [staff], 
               [uniquestudentreference], 
               [reminder]) 
  SELECT TOP (1000)
    NEWID(),
    randomText, 
    randomText, 
    '2', 
    'Monday', 
    '08:50:00', 
    '10:50:00', 
    randomText, 
    randomText, 
    'Silvia', 
    'n'
  FROM
     (SELECT LEFT(Cast(Newid() AS VARCHAR(36)), 20) AS randomText) X
     CROSS JOIN
     sys.columns c1
     CROSS JOIN
     sys.columns c2
     CROSS JOIN
     sys.columns c3

Upvotes: 5

von v.
von v.

Reputation: 17108

You are not incrementing your counter variable,you should add:

SET @row = @row + 1

just before the END clause

Upvotes: 1

Curtis
Curtis

Reputation: 103348

You need to increment @row. At the end of your WHILE statement you should put:

SET @row = @row+1

Upvotes: 2

Related Questions