Hi,

I'm a beginner in learning SQL, I'd to pass more than one value to a SP, so made use of the TVP. 

my SP looks something like this, how do I add the TrainerID's while executing this SP

CREATE type dbo.tvpTID AS TABLE ( TrainerID int NOT NULL, PRIMARY KEY (TrainerID) )

go

ALTER PROCEDURE Sp_insertworkshoptrainers (@Title       AS VARCHAR(50),
                                           @Topic       AS VARCHAR(50),
                                           @Date        AS DATE,
                                           @Duration    AS VARCHAR(50),
                                           @CreatedDate AS DATE,
                                           @UpdatedDate AS DATE,
                                           @tvpTID      TVPTID Readonly)
AS
  BEGIN try
      BEGIN TRANSACTION tr_Insert

      INSERT INTO dbo.tbl_Workshop
      VALUES     (@Title,
                  @Topic,
                  @Date,
                  @Duration,
                  @CreatedDate,
                  @UpdatedDate)

      DECLARE @WorkshopID AS INT

      SET @WorkshopID=Scope_identity()

      INSERT INTO dbo.tbl_WorkshopTrainer
      SELECT TrainerID,
             @WorkshopID
      FROM   @tvpTID

      COMMIT TRANSACTION
  END try

  BEGIN catch
      ROLLBACK TRANSACTION tr_insert
  END catch

Here is a sample, You have to define a variable of type TVP and pass that as input to to the SP

USE AdventureWorks2012;
GO

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo. usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO AdventureWorks2012.Production.Location
           (Name
           ,CostRate
           ,Availability
           ,ModifiedDate)
        SELECT *, 0, GETDATE()
        FROM  @TVP;
        GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT Name, 0.00
    FROM AdventureWorks2012.Person.StateProvince;

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

If you want to call this SP from your application code, here is a sample

http://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/

Thanks. I tried like this: but it gives error saying 

There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

declare @trainerTVP as tvpTID;

insert into @trainerTVP(TrainerID)
values('4588','3434')
exec sp_InsertWorkshopTrainers 's','d','01/01/01','3','01/01/01','01/01/01',@trainerTVP

salwa mirza,

correct your insert statement as follows:

declare @trainerTVP as tvpTID;

insert into @trainerTVP(TrainerID)
select '4588'
UNION 
select '3434'
exec sp_InsertWorkshopTrainers 's','d','01/01/01','3','01/01/01','01/01/01',@trainerTVP

or like below:

declare @trainerTVP as tvpTID;

insert into @trainerTVP(TrainerID)
VALUES ('4588'),('3434')
exec sp_InsertWorkshopTrainers 's','d','01/01/01','3','01/01/01','01/01/01',@trainerTVP

Thanks just did the same.

My error is:

Msg 6401, Level 16, State 1, Procedure sp_InsertWorkshopTrainers, Line 28
Cannot roll back throw. No transaction or savepoint of that name was found.
Msg 266, Level 16, State 2, Procedure sp_InsertWorkshopTrainers, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 2, current count = 3.

salwa,

can you have the catch block as given below?

BEGIN catch
IF(@@TRANCOUNT >0)
      ROLLBACK TRANSACTION tr_insert
END catch

Do have a look at this:

http://connect.microsoft.com/SQLServer/feedback/details/762588/urgent-to-fix-big-usability-problem-with-throw

Also we are looking at your initial code psted in this thread and answering. If there hve been any changes lately, do post the modified code.

Thankyou for your reply. No changes, its just the same. Still the same error I've got

Msg 6401, Level 16, State 1, Procedure sp_InsertWorkshopTrainers, Line 29
Cannot roll back tr_insert. No transaction or savepoint of that name was found.
Msg 266, Level 16, State 2, Procedure sp_InsertWorkshopTrainers, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

This code works perfectly fine for me(in SSMS) , so i think there must be something going wrong calling the SP from the app code.

Can you post the app code?

create table tbl_Workshop(WorkshopID int IDENTITY(1,1),
						  Title       VARCHAR(50),
						  Topic       VARCHAR(50),
						  Dt		  DATE,
						  Duration    VARCHAR(50),
						  CreatedDate DATE,
						  UpdatedDate DATE);

GO
create table dbo.tbl_WorkshopTrainer
      (TrainerID int,
       WorkshopID int)


GO
CREATE type dbo.tvpTID AS TABLE ( TrainerID int NOT NULL, PRIMARY KEY (TrainerID) )

go

CREATE PROCEDURE Sp_insertworkshoptrainers (@Title       AS VARCHAR(50),
                                           @Topic       AS VARCHAR(50),
                                           @Date        AS DATE,
                                           @Duration    AS VARCHAR(50),
                                           @CreatedDate AS DATE,
                                           @UpdatedDate AS DATE,
                                           @tvpTID      TVPTID Readonly)
AS
  BEGIN try
      BEGIN TRANSACTION tr_Insert

      INSERT INTO dbo.tbl_Workshop
      VALUES     (@Title,
                  @Topic,
                  @Date,
                  @Duration,
                  @CreatedDate,
                  @UpdatedDate)

      DECLARE @WorkshopID AS INT

      SET @WorkshopID=Scope_identity()

      INSERT INTO dbo.tbl_WorkshopTrainer
      SELECT TrainerID,
             @WorkshopID
      FROM   @tvpTID

      COMMIT TRANSACTION
  END try

  BEGIN catch
      ROLLBACK TRANSACTION tr_insert
  END catch

GO
declare @trainerTVP as tvpTID;

insert into @trainerTVP(TrainerID)
values ('4588'),
		('3434');
exec sp_InsertWorkshopTrainers 's','d','01/01/01','3','01/01/01','01/01/01',@trainerTVP
GO
select * from tbl_Workshop
select* from tbl_WorkshopTrainer

I think the error pops out, only when the control enters the catch block. Otherwise, the procedure seems to be doing fine.

When i tried by having the BEGIN TRAN before the BEGIN TRY, the error seems to disappear for some strange reason though.

Try this:

ALTER PROCEDURE Sp_insertworkshoptrainers (@Title       AS VARCHAR(50),
                                           @Topic       AS VARCHAR(50),
                                           @Date        AS DATE,
                                           @Duration    AS VARCHAR(50),
                                           @CreatedDate AS DATE,
                                           @UpdatedDate AS DATE,
                                           @tvpTID      TVPTID Readonly)
AS

  BEGIN TRANSACTION tr_Insert
  BEGIN try
      
      INSERT INTO dbo.tbl_Workshop
      VALUES     (@Title,
                  @Topic,
                  @Date,
                  @Duration,
                  @CreatedDate,
                  @UpdatedDate)

      DECLARE @WorkshopID AS INT

      SET @WorkshopID=Scope_identity()

      INSERT INTO dbo.tbl_WorkshopTrainer
      SELECT TrainerID,
             @WorkshopID
      FROM   @tvpTID

      COMMIT TRANSACTION
  END try

  BEGIN catch
      ROLLBACK TRANSACTION tr_insert
  END catch

And yeah, before runnin the procedure, rollback any active transactions that may be present because of faulty executions. Check for existence of any open transactions using SELECT @@TRANCOUNT

You can check the XACT_STATE and decide if you can commit the transaction,

here is the sample code

BEGIN CATCH

   IF (XACT_STATE()) = -1
    BEGIN
        PRINT
            N'The transaction is in an uncommittable state. Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    IF (XACT_STATE()) = 1
    BEGIN
        PRINT
            N'The transaction is committable. Committing transaction.'
        COMMIT TRANSACTION;
    END;
END CATCH

In addition to Satheesh's post, If you do use "SET XACT_ABORT ON;" to make sure that the entire transaction is terminated and rolled back (if necessary).

Hey, thanks all. But the error is still the same

Msg 6401, Level 16, State 1, Procedure sp_InsertWorkshopTrainers, Line 36
Cannot roll back tr_insert. No transaction or savepoint of that name was found.

• Edited by salwa.mirza 3 hours 11 minutes ago

Change :

CREATE type dbo.tvpTID AS TABLE ( TrainerID int NOT NULL, PRIMARY KEY (TrainerID) )
To 
CREATE type dbo.tvpTID AS TABLE ( TrainerID int identity NOT NULL, PRIMARY KEY (TrainerID) )

• Marked as answer by salwa.mirza 1 hour 35 minutes ago