Breaking News: Grepper is joining You.com. Read the official announcement!

stored procedure that checks for foreign key constraint

Nice Narwhal answered on November 17, 2023 Popularity 3/10 Helpfulness 1/10

answer stored procedure that checks for foreign key constraint

stored procedure that checks for foreign key constraint

Comment

----------------- Microsoft SQL Server -------------------
 --- A stored procedure that tests for a valid foreign key
CREATE PROC spInsertInvoice
							@VendorID int,
							@InvoiceNumber varchar(50),
							@InvoiceDate date,
							@InvoiceTotal money,
							@TermsID int,
							@InvoiceDueDate date
AS
	IF EXISTS(SELECT * FROM Vendors WHERE VendorID = @VendorID)
		INSERT Invoices
				VALUES (@VendorID, @InvoiceNumber,
						@InvoiceDate, @InvoiceTotal, 0, 0,
						@TermsID, @InvoiceDueDate, NULL
                        );
	ELSE
      --THROW [error_number, message, state]
		THROW 50001, 'Not a valid VendorID!', 1;
        
-- Calling Procedure -----------
-- A script that calls the procedure
BEGIN TRY
		EXEC spInsertInvoice
			799,'ZXK-799',’2020-03-01',299.95,1,’2020-04-01';
END TRY
BEGIN CATCH
		PRINT 'An error occurred.';
		PRINT 'Message: ' + CONVERT(varchar, ERROR_MESSAGE());
		IF ERROR_NUMBER() >= 50000
			PRINT 'This is a custom error message.';
END CATCH;

---- The response from the system --------
An error occurred.
Message: Not a valid VendorID!
This is a custom error message.

Popularity 3/10 Helpfulness 1/10 Language sql

Source: Grepper

Tags: key procedure sql

Link to this answer
Share Copy Link

Contributed on Nov 17 2023

Nice Narwhal

0 Answers Avg Quality 2/10

Continue with Google

By continuing, I agree that I have read and agree to Greppers's Terms of Service and Privacy Policy.

stored procedure that checks for foreign key constraint

Contents

More Related Answers

stored procedure that checks for foreign key constraint

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.