ماهو الخطأ في الكود التالي

لقولك شيئ :

أنا لم أفهم لماذا استخدمت Begin Try

and Begin Catch

إذا كنت تقصد وضع شرط لتنفيذ هذه التعليمات عند تحقق الشرط

لازم تستخدم If

هذا الكود بعد التصحيح لكن لازم تنتبه إنك لازم تكتب جمل الشرط

CREATE  PROCEDURE usp_InsertTimeSheet
(
   @TimeSheetID	  UNIQUEIDENTIFIER,
   @UserID		   UNIQUEIDENTIFIER,
   @WeekEndingDate   DATETIME
)
AS
Begin 
DECLARE 
	@GroupID UNIQUEIDENTIFIER,
	@ProjectID UNIQUEIDENTIFIER,
	@TimeSheetDate DATETIME,
	@i TINYINT

-- Get the GroupID that the user belongs to
SELECT @GroupID = GroupID FROM Users WHERE UserID = @UserID

DECLARE Project_Cursor CURSOR FOR SELECT ProjectID
   FROM GroupProjects WHERE GroupID = @GroupID

BEGIN TRANSACTION

	  -- Insert the time sheet
	  INSERT INTO TimeSheets
		 (TimeSheetID, UserID, WeekEndingDate, Submitted, ApprovalDate,
		 ManagerID, LastUpdateDate)
		 VALUES(@TimeSheetID, @UserID, @WeekEndingDate, 0, NULL,
		 NULL, GETDATE())

-- 	Missing If Condition --------------------------------------------------------

	  ROLLBACK TRANSACTION
	  RAISERROR('Insert into TimeSheets failed.',18,1)
	  RETURN

   -- Set the initial time sheet date to the beginning of the week
   SET @TimeSheetDate = @WeekEndingDate - 4

   -- Set up a loop to insert time sheet items for 5 days
   SET @i = 1
   WHILE (@i < 6)

	  BEGIN
	  -- Open the cursor
	  OPEN Project_Cursor

	  -- Get the first row of data from the cursor into our variable
	  FETCH NEXT FROM Project_Cursor INTO @ProjectID

	  WHILE @@FETCH_STATUS = 0
		 BEGIN
--	Aditional Condition --------------------------------------------------
	If @@Fetch_Status <> -2	
	Begin 
				-- Insert the time sheet item
				INSERT INTO TimeSheetItems
				(TimeSheetItemID, TimeSheetID, ProjectID, Hours, TimeSheetDate)
			 	  VALUES(NEWID(), @TimeSheetID, @ProjectID, 0, @TimeSheetDate)
	 END 
--	Missing If Condition --------------------------------------------------
			ROLLBACK TRANSACTION
			RAISERROR('Insert into TimeSheetItems failed.',18,1)
			RETURN

		 -- Get the next row of data from the cursor into our variable
		 FETCH NEXT FROM Project_Cursor INTO @ProjectID
		 END

	  CLOSE Project_Cursor

	  -- Increment the date by one day
	  SET @TimeSheetDate = @TimeSheetDate + 1
	  -- Increment the loop counter by one
	  SET @i = @i + 1
	  END

-- Deallocate cursor
DEALLOCATE Project_Cursor

-- Commit all inserts
COMMIT TRANSACTION

End

سلام

مشكور اخي design على الرد

صراحة انا مامصمم هذا الكود

انا ادرس في كتاب الكتروني وهو Beginning Visual Basic 2005 Databases (Programmer to Programmer)

وهو كتاب ممتاز ولم اوجه فيه اي مشكلة ماعدا المشكلة المطروح

ولكن حسب دراستي وخبرتي المتواضعة

في الكود التالي

BEGIN TRANSACTION
   BEGIN TRY
	  -- Insert the time sheet
	  INSERT INTO TimeSheets
		 (TimeSheetID, UserID, WeekEndingDate, Submitted, ApprovalDate,
		 ManagerID, LastUpdateDate)
		 VALUES(@TimeSheetID, @UserID, @WeekEndingDate, 0, NULL,
		 NULL, GETDATE())
   END TRY
   BEGIN CATCH
	  ROLLBACK TRANSACTION
	  RAISERROR('Insert into TimeSheets failed.',18,1)
	  RETURN
   END CATCH

فانه استخدم begin try و begin catch للتاكد

فهو يحاول اضافة record في جدول Timesheets وان فشل فسيرسل رسالة للمستخدم ان العملية فشلت

عبر الكود

RAISERROR('Insert into TimeSheets failed.',18,1)

الموضوع في catch

واعتقد ان لايخفى عليك ان جملة Begin Try and Begin Catch

تستعمل لحالة طلب تنفيذ امر في begin try واذا فشل ينفذ الاوامر في begin catch

على كل حال انا اشكرك شكرا جزيلا على ردك على موضوعي :lol:

بس انا اريد اعرف اش بينو الكود الاصلي يعني من الناحية البرمجية مضبوط بس مااعرف ليش ماكيتنفذ

شكرا

السلام عليكم يا شباب في الحقيقة يوجد

Try

Catch

ولكن في Sql Server 2005

وهذه صفحة من الـ Help تبين ذلك رغم أن استخدامها لم ينجح معي !!! :blink:

---------------------------

Implements error handling for Transact-SQL that is similar to the exception handling in the C# and C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs within the TRY block, control is passed to another group of statements enclosed in a CATCH block.

Syntax

BEGIN TRY
	 { sql_statement | statement_block }
END TRY
BEGIN CATCH
	 { sql_statement | statement_block }
END CATCH
[; ]


Arguments
sql_statement
Is any Transact-SQL statement.

statement_block
Any group of Transact-SQL statements in a batch or enclosed in a BEGIN…END block.

Remarks
A TRY…CATCH construct catches all execution errors with severity greater than 10 that do not terminate the database connection.

A TRY block must be followed immediately by an associated CATCH block. Placing any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.

A TRY…CATCH construct cannot span multiple batches. A TRY…CATCH construct cannot span multiple blocks of Transact-SQL statements. For example, a TRY…CATCH construct cannot span two BEGIN…END blocks of Transact-SQL statements and cannot span an IF…ELSE construct.

If there are no errors in the code enclosed in a TRY block, when the last statement in the TRY block completes, control passes to the statement immediately after the associated END CATCH statement. If there is an error in the code enclosed in a TRY block, control passes to the first statement in the associated CATCH block. If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed back to the statement that invoked the stored procedure or trigger.

When the code in the CATCH block completes, control passes to the statement immediately after the END CATCH statement. Errors trapped by a CATCH block are not returned to the calling application. If any of the error information must be returned to the application, the code in the CATCH block must do so using mechanisms, such as SELECT result sets or the RAISERROR and PRINT statements. For more information about using RAISERROR in conjunction with TRY…CATCH, see Using TRY...CATCH in Transact-SQL.

TRY…CATCH constructs can be nested. Either a TRY block or a CATCH block can contain nested TRY…CATCH constructs. For example, a CATCH block can contain an embedded TRY…CATCH construct to handle errors encountered by the CATCH code.

Errors encountered in a CATCH block are treated like errors generated anywhere else. If the CATCH block contains a nested TRY…CATCH construct, any error in the nested TRY block will pass control to the nested CATCH block. If there is no nested TRY…CATCH construct, the error is passed back to the caller.

TRY…CATCH constructs catch unhandled errors from stored procedures or triggers executed by the code in the TRY block. Alternatively, the stored procedures or triggers can contain their own TRY…CATCH constructs to handle errors generated by their code. For example, when a TRY block executes a stored procedure and an error occurs in the stored procedure, the error can be handled in the following ways:

If the stored procedure does not contain its own TRY…CATCH construct, the error returns control to the CATCH block associated with the TRY block containing the EXECUTE statement.


If the stored procedure contains a TRY…CATCH construct, the error transfers control to the CATCH block in the stored procedure. When the CATCH block code completes, control is passed back to the statement immediately after the EXECUTE statement that called the stored procedure.


GOTO statements cannot be used to enter a TRY or CATCH block. GOTO statements can be used to jump to a label within the same TRY or CATCH block or to leave a TRY or CATCH block.

The TRY…CATCH construct cannot be used within a user-defined function.

Retrieving Error Information
Within the scope of a CATCH block, the following system functions can be used to obtain information about the error that caused the CATCH block to be executed: 

ERROR_NUMBER() returns the number of the error.


ERROR_SEVERITY() returns the severity.


ERROR_STATE() returns the error state number.


ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.


ERROR_LINE() returns the line number inside the routine that caused the error.


ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.


These functions return NULL if called outside the scope of the CATCH block. Error information may be retrieved using these functions from anywhere within the scope of the CATCH block. For example, the script below demonstrates a stored procedure that contains error-handling functions. In the CATCH block of a TRY…CATCH construct, the stored procedure is called and information about the error is returned. 

 Copy Code 
USE AdventureWorks;
GO
-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ( 'usp_GetErrorInfo', 'P' ) IS NOT NULL 
	DROP PROCEDURE usp_GetErrorInfo;
GO

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
	SELECT
		ERROR_NUMBER() AS ErrorNumber,
		ERROR_SEVERITY() AS ErrorSeverity,
		ERROR_STATE() AS ErrorState,
		ERROR_PROCEDURE() AS ErrorProcedure,
		ERROR_LINE() AS ErrorLine,
		ERROR_MESSAGE() AS ErrorMessage;
GO

BEGIN TRY
	-- Generate divide-by-zero error.
	SELECT 1/0;
END TRY
BEGIN CATCH
	-- Execute error retrieval routine.
	EXECUTE usp_GetErrorInfo;
END CATCH;


Errors Unaffected by a TRY…CATCH Construct
TRY…CATCH constructs do not trap the following conditions:

Warnings or informational messages with a severity of 10 or lower.


Errors with severity of 20 or higher that terminate the SQL Server Database Engine task processing for the session. If an error occurs with severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.


Attentions, such as client-interrupt requests or broken client connections.


When the session is terminated by a system administrator using the KILL statement.


The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

Compile errors, such as syntax errors, that prevent a batch from executing.


Errors that occur during statement-level recompilation, such as object name resolution errors that happen after compilation due to deferred name resolution.


These errors are returned to the level that ran the batch, stored procedure, or trigger. 

If an error occurs during compilation or statement-level recompilation at a lower execution level (for example, when executing sp_executesql or a user-defined stored procedure) inside the TRY block, it occurs at a lower level than the TRY…CATCH construct and will be handled by the associated CATCH block. For more information, see Using TRY...CATCH in Transact-SQL.

The following example shows how an object name resolution error generated by a SELECT statement is not caught by the TRY…CATCH construct, but is caught by the CATCH block when the same SELECT statement is executed inside a stored procedure.

 Copy Code 
USE AdventureWorks;
GO

BEGIN TRY
	-- Table does not exist; object name resolution
	-- error not caught.
	SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH
	SELECT 
		ERROR_NUMBER() as ErrorNumber,
		ERROR_MESSAGE() as ErrorMessage;
END CATCH


The error is not caught and control passes out of the TRY…CATCH construct to the next higher level.

Running the SELECT statement inside a stored procedure will cause the error to occur at a level lower than the TRY block. The error will be handled by the TRY…CATCH construct.

 Copy Code 
-- Verify that the stored procedure does not exist.
IF OBJECT_ID ( N'usp_ExampleProc', N'P' ) IS NOT NULL 
	DROP PROCEDURE usp_ExampleProc;
GO

-- Create a stored procedure that will cause an 
-- object resolution error.
CREATE PROCEDURE usp_ExampleProc
AS
	SELECT * FROM NonexistentTable;
GO

BEGIN TRY
	EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
	SELECT 
		ERROR_NUMBER() as ErrorNumber,
		ERROR_MESSAGE() as ErrorMessage;
END CATCH;


For more information about batches, see Batches.


Uncommittable Transactions and XACT_STATE
If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that normally aborts a transaction outside of a TRY block causes a transaction to enter an uncommittable state when it occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction.

For more information about uncommittable transactions and the XACT_STATE function, see Using TRY...CATCH in Transact-SQL and XACT_STATE (Transact-SQL).

Examples
A. Using TRY…CATCH
This example shows a SELECT statement that will generate a divide-by-zero error. The error causes execution to jump to the associated CATCH block.

 Copy Code 
USE AdventureWorks;
GO

BEGIN TRY
	-- Generate a divide-by-zero error.
	SELECT 1/0;
END TRY
BEGIN CATCH
	SELECT
		ERROR_NUMBER() AS ErrorNumber,
		ERROR_SEVERITY() AS ErrorSeverity,
		ERROR_STATE() AS ErrorState,
		ERROR_PROCEDURE() AS ErrorProcedure,
		ERROR_LINE() AS ErrorLine,
		ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO


B. Using TRY…CATCH within a transaction
This example shows how a TRY…CATCH block works inside a transaction. The statement inside the TRY block generates a constraint violation error.

 Copy Code 
USE AdventureWorks;
GO
BEGIN TRANSACTION;

BEGIN TRY
	-- Generate a constraint violation error.
	DELETE FROM Production.Product
		WHERE ProductID = 980;
END TRY
BEGIN CATCH
	SELECT 
		ERROR_NUMBER() AS ErrorNumber,
		ERROR_SEVERITY() AS ErrorSeverity,
		ERROR_STATE() as ErrorState,
		ERROR_PROCEDURE() as ErrorProcedure,
		ERROR_LINE() as ErrorLine,
		ERROR_MESSAGE() as ErrorMessage;

	IF @@TRANCOUNT > 0
		ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
	COMMIT TRANSACTION;
GO


C. Using TRY…CATCH with XACT_STATE
This example shows how to use the TRY…CATCH construct to handle errors that occur within a transaction. The XACT_STATE function determines whether the transaction should be committed or rolled back. In this example, SET XACT_STATE is ON, which makes the transaction uncommittable when the constraint violation error occurs.

 Copy Code 
USE AdventureWorks;
GO

-- Check to see if this stored procedure exists.
IF OBJECT_ID (N'usp_GetErrorInfo', N'P') IS NOT NULL
	DROP PROCEDURE usp_GetErrorInfo;
GO

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
	SELECT 
		ERROR_NUMBER() AS ErrorNumber,
		ERROR_SEVERITY() AS ErrorSeverity,
		ERROR_STATE() as ErrorState,
		ERROR_LINE () as ErrorLine,
		ERROR_PROCEDURE() as ErrorProcedure,
		ERROR_MESSAGE() as ErrorMessage;
GO

-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs. 
SET XACT_ABORT ON;

BEGIN TRY
	BEGIN TRANSACTION;
		-- A foreign key constrain exists on this table. This 
		-- statement will generate a constraint violation error.
		DELETE FROM Production.Product
			WHERE ProductID = 980;

	-- If the DELETE statement succeeds, commit the transaction.
	COMMIT TRANSACTION;
END TRY
BEGIN CATCH
	-- Execute error retrieval routine.
	EXECUTE usp_GetErrorInfo;

	-- Test XACT_STATE:
		-- If 1, the transaction is committable.
		-- If -1, the transaction is uncommittable and should 
		--	 be rolled back.
		-- XACT_STATE = 0 means that there is no transaction and
		--	 a COMMIT or ROLLBACK would generate an error.

	-- Test if the transaction is uncommittable.
	IF (XACT_STATE()) = -1
	BEGIN
		PRINT
			N'The transaction is in an uncommittable state.' +
			'Rolling back transaction.'
		ROLLBACK TRANSACTION;
	END;

	-- Test if the transaction is committable
	IF (XACT_STATE()) = 1
	BEGIN
		PRINT
			N'The transaction is committable.' +
			'Committing transaction.'
		COMMIT TRANSACTION;   
	END;
END CATCH;
GO