There is an insert procedure:

ALTER PROCEDURE [dbo].[BSOs_InsertBso] @Domain_username nvarchar(50)='Max' ,@ProductCode int=100 ,@BsoNumber int=12000 AS BEGIN SET NOCOUNT ON; INSERT INTO [dbo].[BSOs] ([BsoCode] ,[BsoNumber] ,[Owner] ,[FullNumber]) Select bt.bsoCode ,@BsoNumber ,@Domain_username , bt.BsoPrefix + right('00000000000'+cast(@BsoNumber as varchar(10)), NumberLen) From dbo.BsoTypes bt inner join BsoToInsProduct bp on bp.BsoCode = bt.BsoCode where @ProductCode = bp.InsProductCode END

Now I need to create a procedure that adds to the table [dbo].[BSOs] range of 16000 - 16100 BSU numbers (entries) for each product through the cursor.
That is, setting the parameter automatically added to the table
Make the cursor with the parameters, for example:

 DECLARE @Domain_username nvarchar(50)='Alex' ,@StartBsoNumber int=16000 ,@Count int =100 -- количество бСО которые нужно создать

How to create a procedure using a cursor? Or cursor syntax in my cases

  • why the cursor? Do I understand correctly that you need to create N similar records that differ only in the BsoCode field (and, accordingly, FullNumber )? - cyadvert
  • @cyadvert, the cursor must be used as a loop and add entries to the table - Zhandos
  • if I understand the question correctly - then you can do without the cursor .... just answer, if I understood you correctly - all those records that will be created will be almost identical. The difference will only be in the one-field BsoNumber and FullNumber . So? - cyadvert
  • @cyadvert, the difference will be on the product (BsoCode) - Zhandos

2 answers 2

Calling 100 times for the sake of inserting 100 is not a SQL way.

You do not need a cursor. You need a sequence of numbers from 16000 to 16099. It can be generated as:

 DECLARE @startnum INT=16000 DECLARE @endnum INT=@startnu + 99 ; WITH gen AS ( SELECT @startnum AS num UNION ALL SELECT num+1 FROM gen WHERE num+1<=@endnum ) SELECT * FROM gen option (maxrecursion 10000)

This CTE can be mixed directly into your INSERT like this:

 DECLARE @startnum INT=@StartBsoNumber DECLARE @endnum INT=@startnum + @Count - 1 ; WITH gen AS ( SELECT @startnum AS num UNION ALL SELECT num+1 FROM gen WHERE num+1<=@endnum ) INSERT INTO [dbo].[BSOs] ([BsoCode] ,[BsoNumber] ,[Owner] ,[FullNumber]) Select bt.bsoCode ,gen.num ,@Domain_username , bt.BsoPrefix + right('00000000000'+cast(get.num as varchar(10)), NumberLen) From dbo.BsoTypes bt inner join BsoToInsProduct bp on bp.BsoCode = bt.BsoCode cross join gen where @ProductCode = bp.InsProductCode option (maxrecursion 10000)
  • ok and how to do it for each product (BsoCode) - Zhandos
  • (@ProductCode) did not specify such a parameter - Zhandos
  • @Zhandos remove where @ProductCode = bp.InsProductCode - and it will work immediately for all BsoToInsProduct - PashaPash ♦
  • about @ProductCode - you were going to call BSOs_InsertBso from the procedure you were going to write. What did you want to convey as @ProductCode ? - PashaPash ♦
  • thanks earned - Zhandos

If I understand the question correctly, I need to create N similar records that differ only in the BsoNumber field (well, and accordingly FullNumber ). Moreover, BsoNumber will increase by one, starting with @StartBsoNumber .

If so, then you can do without the cursor.

  1. You must make your SELECT .... FROM dbo.BsoTypes JOIN BsoToInsProduct ... return N identical records. I usually do a JOIN with some awesome big table. Its content is not important, it is important number of lines.
    And in this SELECT put TOP @Count
    Well, for example:

     SELECT TOP @Count [list-of-fields] FROM dbo.BsoTypes bt inner join BsoToInsProduct bp on bp.BsoCode = bt.BsoCode INNER JOIN logTable l ON id BETWEEN 1 AND 99999999 where @ProductCode = bp.InsProductCode

    Thus, we get almost what we need. What is missing is the correct counter for BsoNumber .

  2. We include automatic numbering in SELECT . RANK() OVER () function.

     SELECT TOP @Count bt.bsoCode, ((RANK() OVER (ORDER BY bso.id))+@StartBsoNumber), @Domain_username, bt.BsoPrefix + right('00000000000'+cast(((RANK() OVER (ORDER BY bso.id))+@StartBsoNumber) as varchar(10)), NumberLen) FROM dbo.BsoTypes bt inner join BsoToInsProduct bp on bp.BsoCode = bt.BsoCode INNER JOIN logTable l ON id BETWEEN 1 AND 99999999 where @ProductCode = bp.InsProductCode

    Check if the request returns what you need, you can INSERT INTO it into INSERT INTO .