sql - 如何在查询中使用 SQL 变量(SQL Server)？

Question

我写了下面的 SQL 存储过程，它一直给我错误@pid = SELECT MAX(... 整个过程是:

Alter PROCEDURE insert_partyco
@pname varchar(200)
AS
BEGIN
    DECLARE @pid varchar(200);

    @pid = SELECT MAX(party_id)+1 FROM PARTY;
    INSERT INTO party(party_id, name) VALUES(@pid, @pname)
    SELECT SCOPE_IDENTITY() as PARTY_ID
END
GO

谁能告诉我我做错了什么？

Answer 1

Alter PROCEDURE insert_partyco
@pname varchar(200)
AS
BEGIN
    DECLARE @pid varchar(200);

    SELECT @pid = MAX(party_id)+1 FROM PARTY;
    INSERT INTO party(party_id, name) VALUES(@pid, @pname)
    SELECT SCOPE_IDENTITY() as PARTY_ID
END

这比使用 SELECT 的 SET 有优势，因为您可以在一个语句中选择多个变量中的表达式:

SELECT @var1 = exp1, @var2 = expr2 ... etc