Identity column in TVP

[scruper]

Hi, is it possible to insert into TVP which has identity field.

Now I get "INSERT into an identity column not allowed on table variables"

Code example

db, _ := createDbConn() defer db.Close()

createTVP := `
CREATE TYPE dbo.TestType AS TABLE
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    counter INT
);`
_, err := db.Exec(createTVP)

createProc := `
CREATE PROCEDURE dbo.TestProc
    @TVP dbo.TestType READONLY
AS
    SELECT * from @TVP
`
_, err = db.Exec(createProc)

type TestType struct {
    Counter int
}

TestTableType := []TestType{
    {
        Counter: 1,
    },
    {
        Counter: 3,
    },
}

tvpType := mssql.TVP{
    TypeName: "TestType",
    Value:    TestTableType,
}

_, err = db.Exec("exec dbo.TestProc @TVP;", sql.Named("TVP", tvpType))

if err != nil {
    fmt.Println(err)
}

Error: mssql: Trying to pass a table-valued parameter with 1 column(s) where the corresponding user-defined table type requires 2 column(s).

Using SQL Server Directly: ` declare @TVP dbo.TestType

insert @TVP ([counter])
values (1),(2)

exec dbo.TestProc @TVP ` Works fine

[NikitaDef]

Hi, is it possible to insert into TVP which has identity field.

Now I get "INSERT into an identity column not allowed on table variables"

Code example

db, _ := createDbConn() defer db.Close()

createTVP := `
CREATE TYPE dbo.TestType AS TABLE
(
  id INT IDENTITY(1,1) PRIMARY KEY,
  counter INT
);`
_, err := db.Exec(createTVP)

createProc := `
CREATE PROCEDURE dbo.TestProc
  @TVP dbo.TestType READONLY
AS
  SELECT * from @TVP
`
_, err = db.Exec(createProc)

type TestType struct {
  Counter int
}

TestTableType := []TestType{
  {
      Counter: 1,
  },
  {
      Counter: 3,
  },
}

tvpType := mssql.TVP{
  TypeName: "TestType",
  Value:    TestTableType,
}

_, err = db.Exec("exec dbo.TestProc @TVP;", sql.Named("TVP", tvpType))

if err != nil {
  fmt.Println(err)
}

Error: mssql: Trying to pass a table-valued parameter with 1 column(s) where the corresponding user-defined table type requires 2 column(s).

Using SQL Server Directly: ` declare @tvp dbo.TestType

insert @tvp ([counter]) values (1),(2)

exec dbo.TestProc @tvp ` Works fine

Hi, check #771 my MR. How to use.

For field set tag at tvp:"@identity".

Loot at the example in test. https://github.com/denisenkom/go-mssqldb/pull/771/files#diff-190109b133bdf8751fdc477fd4431d277075488688e403feeb86b0e1858c4dbbR1352