CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
HolyUnicow
HolyUnicow

Reputation: 29

Stored procedures - writing errors out to another table

I have two tables and a simple stored procedure. I want to use the stored procedure to insert into table1 with one of the parms being the primary key.

Here is where I am having trouble:

I want to have it so that if the parm/primary key the user has entered into the stored procedure and run is already on the table then it writes out the fields to a second table as well as the error description.

How do you capture / output this error information?

Stored procedure (sans the error logging):

CREATE PROCEDURE procedure1
   @Primary                        INT, 
   @Info                   NVARCHAR                   
AS 
BEGIN 
SET NOCOUNT ON 

 INSERT INTO Table1
      ( 
        Primary                  ,
        Info                   
      ) 
 VALUES 
      ( 
        @Primary                    ,
        @Info                   
      ) 

END

Thanks

Upvotes: 1

Views: 110

Answers (1)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

CREATE PROCEDURE dbo.InsertSomething
(
    @PrimaryKey INT
    , @Info NVARCHAR(MAX)
)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM dbo.Table1 WHERE ID = @PrimaryKey)
    BEGIN
        INSERT INTO dbo.Table2 (ID, Info)
        VALUES (@PrimaryKey, @Info);

        RAISERROR (15600,-1,-1, 'Your custom message');
    END
    ELSE
    BEGIN
        INSERT INTO dbo.Table1 (ID, Info)
        VALUES (@PrimaryKey, @Info);
    END
END

All you have to do is a simple check if record already exists in first table, and if it does just insert into it and throw error.

Upvotes: 1

Related Questions