SQL Server 存储过程、函数与游标核心语法详解

-- 有输入参数的存储过程
CREATE PROCEDURE GetComment
    @commentid int
AS
SELECT * FROM Comment WHERE CommentID = @commentid

-- 有输入与输出参数的存储过程
CREATE PROCEDURE GetCommentCount
    @newsid int,
    @count int OUTPUT
AS
SELECT @count = COUNT(*) FROM Comment WHERE NewsID = @newsid

DECLARE @resultCount int;
EXEC GetCommentCount @newsid = 2, @count = @resultCount OUTPUT;
PRINT @resultCount;

-- 根据参数有效性选择对应的查询逻辑
IF (@addDate IS NOT NULL) AND (@name <> '')
    SELECT * FROM [table] WHERE addDate = @addDate AND name = @name
ELSE IF (@addDate IS NOT NULL) AND (@name = '')
    SELECT * FROM [table] WHERE addDate = @addDate
ELSE IF (@addDate IS NULL) AND (@name <> '')
    SELECT * FROM [table] WHERE name = @name
ELSE
    SELECT * FROM [table]

SELECT * FROM [table] 
WHERE (addDate = @addDate OR @addDate IS NULL) 
AND (name = @name OR @name = '')

CREATE FUNCTION MyFunction(@newsid int)
RETURNS int
AS
BEGIN
    DECLARE @count int;
    SELECT @count = COUNT(*) FROM Comment WHERE NewsID = @newsid;
    RETURN @count;
END

-- 调用标量函数需要使用 schema.dbo 前缀
SELECT dbo.MyFunction(2);

CREATE FUNCTION GetFunctionTable(@newsid int)
RETURNS TABLE
AS
RETURN (SELECT * FROM Comment WHERE NewsID = @newsid)

-- 调用表值函数可直接作为表操作
SELECT * FROM GetFunctionTable(2);

DECLARE @a INT;
SET @a = 5; -- 推荐用于标量赋值

DECLARE @user NVARCHAR(50);
SELECT @user = Name FROM ST_User WHERE ID = 1; -- 从结果集赋值

CREATE TABLE #tempUser (
    ID INT,
    Name NVARCHAR(50)
);
INSERT INTO #tempUser SELECT ID, Name FROM ST_User;
DROP TABLE #tempUser;

DECLARE cur_cursor CURSOR FOR 
SELECT ID, Name FROM ST_User;
OPEN cur_cursor;
FETCH NEXT FROM cur_cursor INTO @ID, @Name;
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 处理逻辑...
    FETCH NEXT FROM cur_cursor INTO @ID, @Name;
END
CLOSE cur_cursor;
DEALLOCATE cur_cursor;

CREATE TRIGGER trg_User_Update ON ST_User
FOR UPDATE
AS
BEGIN
    INSERT INTO LOG ([MSG])
    SELECT '更新日志：用户 ' + CAST(Deleted.ID AS VARCHAR) + ' 信息已变更' 
    FROM Inserted, Deleted;
END;