CODEWITHSUNDEEP
sqlsql-serverstored-procedures
VansFannel
VansFannel

Reputation: 45961

Loop a Create type as table argument in a stored procedure

I'm developing a stored procedure to insert a set of codes inside a table. I'm working with SQL SERVER 2012 SP1 Express edition.

I've done this to pass an array to the stored procedure:

CREATE TYPE dbo.ChildsCodeList
AS TABLE
(
  CODE nvarchar(20)
);
GO

And this is my stored procedure:

create procedure dbo.MyStoredProdure
    @childs as dbo.ChildsCodeList READONLY,
    @parentCode nvarchar(20)
as
begin

[ ... ]

end
go

How can I loop @childs?

I have to insert that code inside a table with an incremental value but in this case I can't use an identity column.

I'm going to insert those codes in this table:

  CODE   |  POSITION
---------+----------
         |

Upvotes: 0

Views: 47

Answers (2)

Deepshikha
Deepshikha

Reputation: 10274

Write as:

CREATE PROCedure dbo.MyStoredProdure
    @childs as dbo.ChildsCodeList READONLY
    AS    
    SET NOCOUNT ON
    INSERT INTO tbl_test
    -- since no ordering is to be done among rows use 'Select 1'
    SELECT CODE,row_number() over (order by (select 1 )) as Rownum
    FROM   @childs

GO

You can find a detailed step by step Demo here.

Upvotes: 1

podiluska
podiluska

Reputation: 51514

with row_number(), probably.

insert sometable (code, position)
select code, row_Number() over (order by something) from  @childs

Upvotes: 0

Related Questions