How to use SQL Variables inside a query ( SQL Server )?

Question

I have written the following SQL Stored Procedure, and it keeps giving me the error at @pid = SELECT MAX(... The whole procedure is:

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

Can anyone please tell me what I'm doing wrong here?

Solution 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

This has an advantage over SET with SELECT in that you can select expressions in multiple variables in one statement:

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

Solution 2

declare @total int

select @total = count(*) from news;

select * from news where newsid = @total+2

//**news** table name and **newsid** column name