User defined table in Sql Server

Question

Can I return integer value after an insert using Stored Procedure with used in user defined type in SQL Server? if successful return 1 if error return 0 if Yes .. How? Please explain with one example. I have created this user type and stored procedure and used in asp.net, it is inserted. But my question is can I return integer value after successful. CREATE TYPE InsertEmployee AS TABLE ( [emp_id] [ int ] NOT NULL , [emp_name][ varchar (100)] ) GO CREATE PROCEDURE sp_InsertEmployee ( @DataTableDetails InsertEmployee READONLY ) AS BEGIN INSERT INTO tbl_employee SELECT emp_id, emp_name FROM @DataTableDetails END

Jayant Tripathy · Answer

In this case use Output parameter and return it according to your choice CREATE PROCEDURE sp_InsertEmployee ( @DataTableDetails InsertEmployee READONLY, @ouptputval int output ) AS BEGIN TRY BEGIN INSERT INTO tbl_employee SELECT emp_id, emp_name FROM @DataTableDetails select ouptputval=1 END CATCH -- IF ANY ERROR THEN RETURN AS 0 BEGIN select ouptputval=0 END END

Varun Setia · Answer

//Using TRY-CATCH For Better handling CREATE PROCEDURE sp_InsertEmployee ( @DataTableDetails InsertEmployee READONLY ) AS BEGIN BEGIN TRY INSERT INTO tbl_employee SELECT emp_id, emp_name FROM @DataTableDetails; SELECT @@ROWCOUNT; END TRY BEGIN CATCH SELECT 0; END CATCH; END

Varun Setia · Answer

Hi, Try this: CREATE TYPE InsertEmployee AS TABLE( [emp_id] [ int ] NOT NULL, [emp_name][varchar(100)] ) GO CREATE PROCEDURE sp_InsertEmployee ( @DataTableDetails InsertEmployee READONLY ) AS BEGIN INSERT INTO tbl_employee SELECT emp_id, emp_name FROM @DataTableDetails; SELECT @@ROWCOUNT; END SELECT @@ROWCOUNT will do the trick. If data is inserted, it will return 1, if no row is inserted it will return 0.