Ubuntu上使用SQL Server编写存储过程指南
一 环境准备与连接
二 基本语法与模板
CREATE OR ALTER PROCEDURE dbo.ProcedureName
@Param1 INT,
@Param2 NVARCHAR(50),
@OutputParam INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
-- 业务逻辑
SELECT @OutputParam = @Param1 * 2;
END
GO
DECLARE @Result INT;
EXEC dbo.ProcedureName @Param1 = 5, @Param2 = N'hello', @OutputParam = @Result OUTPUT;
SELECT @Result AS Result;
ALTER PROCEDURE dbo.ProcedureName ... ; -- 修改
DROP PROCEDURE IF EXISTS dbo.ProcedureName; -- 删除（SQL Server 2016+ 支持 IF EXISTS）
三 常用示例
CREATE OR ALTER PROCEDURE dbo.AddNumbers
@Number1 INT,
@Number2 INT,
@Sum INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @Sum = @Number1 + @Number2;
END
GO
-- 调用
DECLARE @Res INT;
EXEC dbo.AddNumbers @Number1 = 5, @Number2 = 10, @Sum = @Res OUTPUT;
SELECT @Res AS Result;
IF OBJECT_ID(N'dbo.GetEmployeesByDept', N'P') IS NOT NULL
DROP PROCEDURE dbo.GetEmployeesByDept;
GO
CREATE PROCEDURE dbo.GetEmployeesByDept
@DeptID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT EmployeeID, Name, Title
FROM dbo.Employees
WHERE DepartmentID = @DeptID
ORDER BY EmployeeID;
END
GO
-- 调用
EXEC dbo.GetEmployeesByDept @DeptID = 1;
CREATE OR ALTER PROCEDURE dbo.TransferMoney
@FromAcc INT,
@ToAcc INT,
@Amount DECIMAL(18,2)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.Accounts SET Balance = Balance - @Amount WHERE AccountID = @FromAcc;
IF @@ROWCOUNT = 0 THROW 50001, 'From account not found or insufficient funds.', 1;
UPDATE dbo.Accounts SET Balance = Balance + @Amount WHERE AccountID = @ToAcc;
IF @@ROWCOUNT = 0 THROW 50002, 'To account not found.', 1;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrSeverity INT = ERROR_SEVERITY();
DECLARE @ErrState INT = ERROR_STATE();
RAISERROR(@ErrMsg, @ErrSeverity, @ErrState);
END CATCH
END
GO
-- 创建登录与用户
CREATE LOGIN app_user WITH PASSWORD = 'StrongP@ssw0rd!';
CREATE USER app_user FOR LOGIN app_user;
-- 授予执行权限
GRANT EXECUTE ON dbo.GetEmployeesByDept TO app_user;
-- 查看定义
EXEC sp_helptext 'dbo.GetEmployeesByDept';
-- 修改
ALTER PROCEDURE dbo.GetEmployeesByDept
@DeptID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT EmployeeID, Name, Title, HireDate
FROM dbo.Employees
WHERE DepartmentID = @DeptID
ORDER BY HireDate DESC;
END
GO
四 在Ubuntu终端的最佳实践
五 常见问题与排查