urgent - question about procedures, try & catch

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

urgent - question about procedures, try & catch

Author

Topic

sasam1400
Starting Member

2 Posts

Posted - 2010-06-02 : 22:03:15

Hi all. I'm having trouble with my task:
Make a procedure to enable making new assignments for professors in school, check everything with TRY&CATCH, and if everything is OK display that table.
On the beginning the code works fine, but it doesn't display the table on the end!
Please help!

CREATE PROCEDURE [dbo].[NewAssignment] 
	@SubjectID as int,
	@ProfessorID as nvarchar(20),
	@Assignment as nvarchar(50)
AS
BEGIN
	BEGIN TRY
		INSERT INTO ProfessorAssignments VALUES (@SubjectID,@ProfessorID,@Assignment)
	END TRY

	BEGIN CATCH
		DECLARE @ErrNumb INT 
		SET @ErrNumb = ERROR_NUMBER()
		IF @ErrNumb >0
		BEGIN
			IF @ErrNumb =2627 SELECT 'That subject is already assigned for that professor.' AS ErrorExpl
			ELSE SELECT 'Other error' AS ErrorExpl
		END
		ELSE
		BEGIN
			SELECT * FROM ProfessorAssignments 
		END
	END CATCH

END

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-03 : 12:14:42

You want to treat the TRY block as a normal execution path. So, move the "SELECT * FROM ProfessorAssignments" into the TRY block. The CATCH block should only execute if there is an error. So the CATCH block is just an execption to normal execution. In this case, if the INSERT succeeds, then the CATCH block never executes. Thus, the SELECT * doesn't run.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources