Loading

User defined table in Sql Server

User defined table in Sql Server

Dibyajyoti Palata

Dibyajyoti Palata

6y971

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jayant Tripathy6y ago
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
0
Varun Setia6y ago
1. //Using TRY-CATCH For Better handling
2. CREATE PROCEDURE sp_InsertEmployee
3. (
4. @DataTableDetails InsertEmployee READONLY
5. )
6. AS
7. BEGIN
9. BEGIN TRY
11. INSERT INTO tbl_employee
12. SELECT emp_id, emp_name
13. FROM @DataTableDetails;
15. SELECT @@ROWCOUNT;
17. END TRY
18. BEGIN CATCH
20. SELECT 0;
22. END CATCH;
24. END
0

Varun Setia

Varun Setia6y ago

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.

0