回傳資料集
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
SET NOCOUNT ON;
SELECT LastName, FirstName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment;
EXEC HumanResources.uspGetAllEmployees;
c#:
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "HumanResources.uspGetAllEmployees";
//cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1;
SqlDataReader reader = cmd.ExecuteReader();
while ((dr.Read()){}
-----------------------------------------------------------------------------------
回傳 OUTPUT
CREATE PROCEDURE GetImmediateManager
@employeeID INT,
@managerID INT OUTPUT
AS
BEGIN
SELECT @managerID = ManagerID //將抓出的值止訂給 OUTPUT變數
FROM HumanResources.Employee
WHERE EmployeeID = @employeeID
END
DECLARE @managerID INT
EXEC GetImmediateManager 5, @managerID OUTPUT
SELECT @managerID
c#:
SqlParameter lastName = cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 30);
lastName.Direction = ParameterDirection.Output;
lastName.Value
-----------------------------------------------------------------------------------
回傳 RETURN
CREATE PROCEDURE mysp_InsData (
@LastName varchar(30),
@FirstName varchar(10)
)
AS
BEGIN
INSERT INTO Customer (LastName, FirstName) VALUES (@LastName, @FirstName);
RETURN SCOPE_IDENTITY(); //在新增完資料後，使用 RETURN 返回新增資料的識別值
END
c#:
SqlParameter retID = cmd.Parameters.Add("@ReturnValue", SqlDbType.Int);
retID.Direction = ParameterDirection.ReturnValue;
retID.Value
----------------------------------------------------------------------------------------
@@ERROR @@ROWCOUNT CATCH: ERROR_MESSAGE()
IF @@ERROR <> 0 //表示有錯誤
BEGIN
-- Return 99 to the calling program to indicate failure.
PRINT N'An error occurred deleting the candidate information.';
RETURN 99;
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
PRINT N'The job candidate has been deleted.';
RETURN 0;
END;
----------------------------------------
UPDATE HumanResources.Employee
SET JobTitle = N'Executive'
WHERE NationalIDNumber = 123456789
IF @@ROWCOUNT = 0 //表示異動資料失敗
PRINT 'Warning: No rows were updated';
----------------------------------------
BEGIN TRY
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage; //取得錯誤訊息
END CATCH;
-------------------------------------------------------------------------------------------
TRANSACTION 1 @@TRANCOUNT
ALTER Procedure [dbo].[DeleteStudentTransaction]
@Id INT
AS
BEGIN TRY
BEGIN TRANSACTION
DELETE FROM Student WHERE Id=@Id
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 若有異動到資料，則回復異動
ROLLBACK
END CATCH
----------------------------------------------------------------------------------------
TRANSACTION 2
BEGIN TRANSACTION;
BEGIN TRY
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;
----------------------------------------------------------------------------------------
CURSOR: WHILE @@FETCH_STATUS = 0
ALTER PROCEDURE [dbo].[InsertData](@Input_ID int , @result bit output) --注意欄位型態..
AS
DECLARE @C1 int --宣告變數
DECLARE @C2 VARCHAR(50)
DECLARE @C3 VARCHAR(50)
SELECT @result = 1 //表示成功
DECLARE CURSOR1 CURSOR FOR --宣告CURSOR 將值賦予CURSOR
SELECT ID,FileID,Photographer FROM Picture WHERE ID < @Input_ID --注意欄位型態..
OPEN CURSOR1 --開啟CURSOR
FETCH NEXT FROM CURSOR1 INTO @C1 , @C2 , @C3 --將CURSOR中的第一列資料 傳給宣告的變數
WHILE @@FETCH_STATUS = 0 --0 陳述式順利執行。-1 陳述式失敗，或資料列超出結果集。-2 擷取的資料列已遺漏。
BEGIN
INSERT INTO Table1(C1,C2,C3) VALUES(@C1,@C2,@C3)
INSERT INTO Table2(C1,C2,C3) VALUES(@C1,@C2,@C3)
if @@ERROR >0 --若有錯誤產生
BEGIN
SELECT @result = 0 //表示失敗
END
FETCH NEXT FROM CURSOR1 INTO @C1 , @C2 , @C3 --將CURSOR中的下一筆資料 傳給宣告的變數
END
CLOSE CURSOR1 --關閉CURSOR
DEALLOCATE CURSOR1 --刪除CURSOR
執行測試
DECLARE @result bit
EXEC dbo.InsertData 4 , @result OUTPUT
SELECT @result